CODEWITHSUNDEEP
excelvbafunctionbooleanparameter-passing
Someone Somewhere
Someone Somewhere

Reputation: 1

How to return a Boolean value from a Function?

Trying to write some error checking code. Until now all my code has been one giant sub procedure.

I select cell A1 in my worksheet, which has Test1 inside of the cell. Then I run the MainProgramCode macro.

The results I get:
False Before Function call
You selected data from column 1
False After Function call

I have OkToProceed = True right after the MsgBox "You selected data from column 1". But my OkToProceed variable in MainProgramCode is still False after the function?

Function Check_User_Select_Range_Column(OkToProceed As Boolean)
If Selection.Column = 1 Then
    MsgBox "You selected data from column 1"
    OkToProceed = True
Else
    MsgBox "You did not select data from column 1, please try again."
    OkToProceed = False
End If
End Function


Sub MainProgramCode()
Dim OkToProceed As Boolean

'Do some error checking
MsgBox OkToProceed & " Before Function call "
OkToProceed = Check_User_Select_Range_Column(OkToProceed)
MsgBox OkToProceed & " After Function call "

'Proceed with MainProgramCode if user data selection is correct
If OkToProceed = True Then
    MsgBox "MainProgramCode"
Else
    Exit Sub
End If

End Sub

Upvotes: 0

Views: 1296

Answers (2)

Porcupine911
Porcupine911

Reputation: 928

The root cause of your issue is that you are not understanding how to return a value from a VBA function. To return a value from a function, you assign that value to the name of the function.

From the Microsoft Dev Center VBA reference docs,

"To return a value from a function, assign the value to the function name. Any number of such assignments can appear anywhere within the procedure. If no value is assigned to name, the procedure returns a default value: a numeric function returns 0, a string function returns a zero-length string (""), and a Variant function returns Empty. "

yamashiro.akihito's answer demonstrates the correct usage.

Upvotes: 0

yamashiro.akihito
yamashiro.akihito

Reputation: 109

since Check_User_Select_Range_Column does not return anything, you should change

OkToProceed = Check_User_Select_Range_Column(OkToProceed)

to

Check_User_Select_Range_Column OkToProceed

However the following is better.

Function Check_User_Select_Range_Column()
If Selection.Column = 1 Then
MsgBox "You selected data from column 1"
Check_User_Select_Range_Column = True
Else
MsgBox "You did not select data from column 1, please try again."
Check_User_Select_Range_Column = False
End If
End Function


Sub MainProgramCode()
Dim OkToProceed As Boolean

'Do some error checking
MsgBox OkToProceed & " Before Function call "
OkToProceed = Check_User_Select_Range_Column()
MsgBox OkToProceed & " After Function call "

'Proceed with MainProgramCode if user data selection is correct
If OkToProceed = True Then
MsgBox "MainProgramCode"
Else
Exit Sub
End If

End Sub

Upvotes: 1

Related Questions