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

November 18, 2021

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?

>Solution :

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