VBA: Cannot assign a function's returned value to a variable

Question

I have the following VBA code in one of my sheets (i.e. not in a module):

Option Explicit

Public timing As String

Sub ButtonLoan1_Click()

    timing = check_timing()
    Application.Run ("loan_" & timing & "_req01")

End Sub

The function check_timing is defined in a module and works correctly:

Function check_timing()
    
    If ActiveSheet.Range("B5") = "Q1 and Q3" Then
        timing = "q1q3"
    ElseIf ActiveSheet.Range("B5") = "Q2 and Q4" Then
        timing = "q2q4"
    End If
    
    Exit Function

End Function

However, running the ButtonLoan1_Click() Sub returns an error because the variable timing is empty, i.e. it is not getting its value from the function check_timing, as I would like it to. What am I doing wrong?

Within a function, you assign the value to the function name in order to return the value so `check_timing = "q1q3"` and `check_timing = "q2q4"` — Raymond Wu, Nov 18 '21 at 14:18
You need to make `timing` public outside of procedures or change your return to `check_timing` — Nathan_Sav, Nov 18 '21 at 14:18

score 1 · Accepted Answer · answered Nov 18 '21 at 14:21

1

My guess is you should probably use check_timing instead of timing so VBA knows this is what the function is returning to whomever called it before.

Function check_timing()
    
    If ActiveSheet.Range("B5") = "Q1 and Q3" Then
        check_timing = "q1q3"
    ElseIf ActiveSheet.Range("B5") = "Q2 and Q4" Then
        check_timing = "q2q4"
    End If
    
    Exit Function

End Function

answered Nov 18 '21 at 14:21

deverson

38
5

1

And the `Exit Function` is redundant and can be dropped. Also `check_timing` should probably accept a `Range` parameter instead of using `ActiveSheet.Range("B5")`. – BigBen Nov 18 '21 at 14:22
2

Also, add the return type of string too – Nathan_Sav Nov 18 '21 at 14:24

VBA: Cannot assign a function's returned value to a variable

1 Answers1