2

I am trying to call a sub routine in another sub routine and want to return some values. However it doesn't seem to return any value. How would I write this as a function ? The code is the following: 

Sub tickersymbolchange()

 Dim RSTA_ISIN, RSTA_Currency, RSTA_Ticker As String

For Each rng In r

         ticker_wo_equity = Replace(rng.Value, " Equity", "")
         Exchangecode = Right(ticker_wo_equity, 2)

         Select Case Exchangecode
            Case "PO", "L3", "B3", "S2", "TQ", "SS"

                'MsgBox "I am in PO"

                Call getRSTA_POL3B3S2TQSSIXEB(ticker_wo_equity, RSTA_ISIN, RSTA_Currency, RSTA_Ticker)

                'Get ISIN from Rsta
                If IsEmpty(RSTA_ISIN) Then
                     rng.Offset(0, 11) = "N/A"
                Else
                     rng.Offset(0, 11) = RSTA_ISIN
                End If


                'Get Currency from Rsta
                If IsEmpty(RSTA_Currency) Then
                    rng.Offset(0, 12) = "N/A"
                Else
                    rng.Offset(0, 12) = RSTA_Currency
                End If
End select

End Sub

Sub getRSTA_POL3B3S2TQSSIXEB(ticker, Optional ByVal getISIN As String, Optional ByVal getCurrency As String, Optional ByVal getTicker As String, Optional ByVal getUS As String, Optional ByVal getTickerTicker_IBEX As String, Optional ByVal getPriceCode_GR As String)

    BBs.Send ticker & "<Equity> RSTA"
    BBs.Go
    'Application.Wait (Now + TimeValue("0:00:01"))
    'Sleep 1000

    BBs.CopyScreen

    getISIN = BBs.GetTextField(7, 2, 13)
    getCurrency = BBs.GetTextField(7, 15, 4)
    getTicker = BBs.GetTextField(7, 20, 6)
    getUS = BBs.GetTextField(7, 11, 9)
    getTickerTicker_IBEX = BBs.GetTextField(7, 2, 7)
    getPriceCode_GR = BBs.GetTextField(7, 2, 7)

End Sub

So here I am trying to give RSTA_ISIN to the function getRSTA_POL... and then the function should assign RSTA_ISIN the value of getISIN, however RSTA_ISIN and all the other parameters are always empty.

Improve this question
2
  • If you want to change the values of the parameters passed into the sub, use byRef, not byVal. After you've changed the values and fallen out of the sub the original vars passed in will hold the new values. Commented Nov 17, 2015 at 9:57
  • 1
    Possible duplicate of How to Return a result from a VBA Function Commented Nov 17, 2015 at 9:57

2 Answers 2

Reset to default
3

First of all, your code use a Call for your subroutine, you can use this but it actually avoid having any output from the function!

Secondly, you need to use ByRef instead of ByVal if you want to affect the value of your arguments inside of the procedure or function. ByVal will create a copy of the inputed argument and though won't affect the initial variable!

Finally, you need to transform the second Sub into a Function and define the type of output (here I set it as a String) and then assign the function output to a variable, like this :

Public Function test_Function() As Integer
    test = 1
End Function
Sub Test()
    Dim Result as Integer
    Result = test_Function
    MsgBox Result 
End Sub

Here is your revised code :

Sub tickersymbolchange()
Dim RSTA_ISIN As String, RSTA_Currency As String, RSTA_Ticker As String
For Each Rng In r
    ticker_wo_equity = Replace(Rng.Value, " Equity", "")
    Exchangecode = Right(ticker_wo_equity, 2)

    Select Case Exchangecode
       Case "PO", "L3", "B3", "S2", "TQ", "SS"
           RSTA_ISIN = getRSTA_POL3B3S2TQSSIXEB(ticker_wo_equity, RSTA_ISIN, RSTA_Currency, RSTA_Ticker)
           'Get ISIN from Rsta
           If IsEmpty(RSTA_ISIN) Then
                Rng.Offset(0, 11) = "N/A"
           Else
                Rng.Offset(0, 11) = RSTA_ISIN
           End If
           'Get Currency from Rsta
           If IsEmpty(RSTA_Currency) Then
               Rng.Offset(0, 12) = "N/A"
           Else
               Rng.Offset(0, 12) = RSTA_Currency
           End If
    End Select
Next Rng
End Sub

Function getRSTA_POL3B3S2TQSSIXEB(ticker, Optional ByRef getISIN As String, _
                            Optional ByRef getCurrency As String, _
                            Optional ByRef getTicker As String, _
                            Optional ByRef getUS As String, _
                            Optional ByRef getTickerTicker_IBEX As String, _
                            Optional ByRef getPriceCode_GR As String) As String
    BBs.Send ticker & "<Equity> RSTA"
    BBs.Go
    'Sleep 1000
    BBs.CopyScreen

    getISIN = BBs.GetTextField(7, 2, 13)
    getCurrency = BBs.GetTextField(7, 15, 4)
    getTicker = BBs.GetTextField(7, 20, 6)
    getUS = BBs.GetTextField(7, 11, 9)
    getTickerTicker_IBEX = BBs.GetTextField(7, 2, 7)
    getPriceCode_GR = BBs.GetTextField(7, 2, 7)

getRSTA_POL3B3S2TQSSIXEB = getISIN
End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

If I use this it gives me the error ByRef argument type mismatch
It is because you didn't declare RSTA_ISIN, RSTA_Currency as String. See and test the edit! ;)
2

First of all Functions are declared differently:

Function GetLong()
   GetLong = 10
End Function

Sub GetLong()
  GetLong = 10 'WRONG!!!
End Sub

Secondly, you can only define a variable provided to a procedure if you send it by it's reference (pointer) not by value:

Sub SetLong(ByRef myLong as Long)
   myLong = 10
End Sub

So it all comes to replacing : ByVal getISIN As String with ByRef getISIN As String

Here you can find a tutorial on Procedures vs. Functions.

Improve this answer

1 Comment

If I use this it gives me the error ByRef argument type mismatch

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.