Pass Variable back to Sub that called Function

[blackshh]

Hello all,
I have a sub that is calling a funciton. The function needs to set a flag and send this flag value back to the sub. The sub will then perform based on the flag value. I can't seem to get the flag to be sent back to the sub. Can someone please help me figure out what I'm doing wrong or if this can even be done.
Thanking you in advance


Here's is part of my code:

Sub RefreshSheet()

Dim blnFlag As Boolean

blnFlag = True

Product (blnFlag)
If Not blnFlag Then
MsgBox "No Products"
Exit Sub
End If

(Code if sub continues)

End Sub


Function Product(blnFlag As Boolean)
( code to set iCopyEndRow and iStartRow values)

If iCopyEndRow < iStartRow Then
blnFlag = False
Exit Function
End If
(code if function continues)
End Function

 

[al_b_cnu]

Hi, Try

Sub RefreshSheet()

Dim blnFlag As Boolean

blnFlag = Product(blnFlag)
If Not blnFlag Then
    MsgBox "No Products"
    Exit Sub
End If

'(Code if sub continues)

End Sub


Function Product() As Boolean
'( code to set iCopyEndRow and iStartRow values)

If iCopyEndRow < iStartRow Then
    Product = False
    Exit Function
End If
'(code if function continues)

Product = True
End Function

 

[Colin Legg]

Because you are passing blnFlag ByRef (a pointer), your Product function should change the value of blnFlag in the calling sub. Here's a proof of concept example:

Sub Test()
    Dim blnFlag As Boolean
    
    'boolean variables are initialised as False:
    Debug.Print blnFlag
    
    fProduct blnFlag
    
    Debug.Print blnFlag
End Sub

Function fProduct(ByRef blnFlag As Boolean)
    
    blnFlag = True
    
End Function

So I expect the problem lies in the code you excluded?

But, perhaps another approach would be?

Sub Test()
    Dim blnFlag As Boolean
    
    'boolean variables are initialised as False:
    Debug.Print blnFlag
    
    blnFlag = fProduct
    
    Debug.Print blnFlag
End Sub

Function fProduct() As Boolean
    
    fProduct = True
    
End Function

 

[blackshh]

Thank you both,
I tried what al_b_cnu sent and it worked.

Thanks

 

[Kenneth Hobson]

Both methods work. If you are dealing with more than one variable that you want updated or returned, use Colin's method or this 3rd method. Colin used the same variable name but it need not be so.

The 3rd method is to declare your variables at the top of the Module before any Sub or Function. You can use Dim, Private or Public. I would only use this method if I wanted to share between Modules, UserForms and such.