VBA write function and return value

Question

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.

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. — , Nov 17 '15 at 09:57
Possible duplicate of [How to Return a result from a VBA Function](http://stackoverflow.com/questions/2781689/how-to-return-a-result-from-a-vba-function) — SiHa, Nov 17 '15 at 09:57

R3uK · Accepted Answer · 2015-11-17T16:15:58.113

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

If I use this it gives me the error ByRef argument type mismatch — Nant, Nov 17 '15 at 16:05
It is because you didn't declare `RSTA_ISIN, RSTA_Currency` as String. See and test the edit! ;) — R3uK, Nov 17 '15 at 16:16

score 2 · Answer 2 · answered Nov 17 '15 at 09:57

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.

If I use this it gives me the error ByRef argument type mismatch — Nant, Nov 17 '15 at 16:05

VBA write function and return value

2 Answers2