none
Logical Variable passed byref to subroutine not updated 'properly' RRS feed

  • Question

  • The following code example is extracted from a VBA program.  A logical variable is transferred byref to a subroutine & updated. On return to the main program, its value is not the updated value (True), but the original one (False).

    Where am I wrong?

    Any help would be appreciated.

    Peter123

    Public iRow As Integer
    
    Sub Main()
         
         iRow = 0
         Dim RF As Boolean
         RF = False
         LocRec (RF)
         If RF Then MsgBox RF
             
     End Sub
     Sub LocRec(ByRef R As Boolean)
         
         If iRow = 0 Then
             iRow = 10
             R = True
         End If
         
     End Sub
    


    Peter 123

    Thursday, April 24, 2014 7:22 PM

Answers

  • LocRec (RF)

    You've tripped on what can be a useful technique to pass a variable ByVal to a routine that expects ByRef. Putting the variable RF in brackets evaluates it and passes, in this case, False but not the pointer to the variable RF. IOW the reason RF remains unchanged is because it was never sent. To send byRef send without the brackets

    LocRec RF

    You could use Call but this use of the brackets wraps the arguments not evaluate them

    Call LocRec(RF)

    though Call LocRec((RF)) is the same as your version and passes the value not the variable.

    In passing, unless you need to maintain a global variable it's almost always possible and generally better to pass variables even through long chains and let them fall out of scope when done.

    • Marked as answer by Peter 123 Friday, April 25, 2014 1:54 PM
    Friday, April 25, 2014 7:38 AM
    Moderator

All replies

  • Hi,

    I get the same thing in Excel 2010 SP2, but the following did work as expected:

    Sub Main()
    Dim irow As Integer
         irow = 0
         Dim RF As Boolean
         RF = False
         LocRec RF, irow
         If RF Then MsgBox RF
    End Sub
    Sub LocRec(ByRef RF As Boolean, irow As Integer)
        If irow = 0 Then
            irow = 10
            RF = True
        End If
    End Sub
    

    The other way of coding this is with a function:

    Public iRow As Integer
    
    Sub Main()
         iRow = 0
         Dim RF As Boolean
         RF = False
         If LocRec Then
            MsgBox True
        End If
    End Sub
    
    Function LocRec() As Boolean
        If iRow = 0 Then
            iRow = 10
            LocRec = True
        Else
            LocRec = False
        End If
    End Function


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Thursday, April 24, 2014 9:10 PM
  • Thanks for your reply.

    You  show two alternate ways of coding it & they work.  My work-around was to declare the logical variable as Public at the top of Module 1 rather than passing it as a subroutine argument.  That works too.

    My remaining question: What is wrong with my code? Why does it not work? Isn't this a bug in the VBA code?

    Peter123


    Peter 123

    Friday, April 25, 2014 3:10 AM
  • Friday, April 25, 2014 7:15 AM
  • LocRec (RF)

    You've tripped on what can be a useful technique to pass a variable ByVal to a routine that expects ByRef. Putting the variable RF in brackets evaluates it and passes, in this case, False but not the pointer to the variable RF. IOW the reason RF remains unchanged is because it was never sent. To send byRef send without the brackets

    LocRec RF

    You could use Call but this use of the brackets wraps the arguments not evaluate them

    Call LocRec(RF)

    though Call LocRec((RF)) is the same as your version and passes the value not the variable.

    In passing, unless you need to maintain a global variable it's almost always possible and generally better to pass variables even through long chains and let them fall out of scope when done.

    • Marked as answer by Peter 123 Friday, April 25, 2014 1:54 PM
    Friday, April 25, 2014 7:38 AM
    Moderator
  • Thanks, that resolves the issue.  Was not aware of the fact that sub calls with & w/o parenths are handled differently.

    Peter123


    Peter 123

    Friday, April 25, 2014 2:06 PM