Have a calling sub macro call a sub macro ByRef passing variables back and forth

[Tommee]

Hello. I have been trying to pass variables from a calling sub to a called sub and return the changed variables back from the called sub to the calling sub.

I believe the calling sub should not use parenthesis when calling a sub else the variables will not be updated.

If I create a called sub macro with the following and exit it, it disappears.

It happens only when I use "ByRef".

How can I code the called sub to show and stay in my macro list?

I can't use (I believe) ByVal as I need to pass variables both ways.

I coded the called subroutine 2 ways.

How I should code the calling and called subroutines?

Thank you for your help:

Sub AccountType()

‘ Comment: This is the main calling subroutine

Dim Accountnumber As Integer

Dim Accountinfo As String

Accountinfo = “ “

For Accountnumber = 1 to 2

        Call AccountDescr1 Accountnumber, Accountinfo

        MsgBox “Account Type = “ & Accountinfo

        Call AccountDescr2 Accountnumber, Accountinfo

        MsgBox “Account Type = “ & Accountinfo

Next Accountnumber

End Sub

Sub AccountDesc1(ByRef Accountnumber As Integer, ByRef Accountinfo As String)

‘ Comment: This is called subroutine #1

                If Accountnumber = 1 Then

                        Accountinfo = “old”

               ElseIf Accountnumber = 2 Then

                        Accountinfo = “new”

               End If

End Sub

Sub AccountDesc2(ByRef Accountnbr As Integer, ByRef Accountdata As String)

‘ Comment: This is called subroutine #2

                If Accountnbr = 1 Then

                        Accountdata = “old”

               ElseIf Accountnbr = 2 Then

                        Accountdata = “new”

                End If

End Sub

[#2]

@Tommee 

There is an error is in the calling routine. If you use the Call keyword, you must surround all arguments with a single set of parentheses:

Call AccountDescr1(Accountnumber, Accountinfo)

Those parentheses do not affect the passing mechanism (i.e., passing variables ByVal vs. ByRef). Alternatively, you could omit the Call keyword and code it this way:

AccountDescr1 Accountnumber, Accountinfo

A calling procedure can force an argument to be passed ByVal (effectively) by using parentheses, as noted here. But that is by using parentheses around individual arguments, such as any of these:

Call AccountDescr1((Accountnumber), Accountinfo)
Call AccountDescr1(Accountnumber, (Accountinfo))
Call AccountDescr1((Accountnumber), (Accountinfo))
AccountDescr1 (Accountnumber), Accountinfo
AccountDescr1 Accountnumber, (Accountinfo)
AccountDescr1 (Accountnumber), (Accountinfo)

I will point out that if you are not changing the value of a parameter variable within the called procedure, it is usually preferred to use the ByVal passing mechanism (the exceptions have to do with value-type arguments that use much memory, such as very large Strings or Struct[ure]s, and this is rarely important), and you can mix ByVal and ByRef among your parameters. So in your posted examples, Accountnumber and Accountnbr could be passed ByVal.

Edit: Strings are reference types, not value types; I keep forgetting that

[#3]

@SnowMan55 

Hi Snowman55. I have printed out your corrections & suggestions. Thank you.

If I create a new sub macro called AccountDesc with nothing in it, & edit it in my macro list it looks like this:

Sub AccountDesc()

‘ Comment: This is called subroutine 

End Sub

I can exit out of it and the name stays in my macro list & I can edit it again adding code and execute it by running it from my macro list.

If I edit the called macro and insert ByRef or ByVal variables and exit out of it the macro disappears from my macro list and I can't find it or re-edit it so it's lost.

Sub AccountDesc(ByVal Accountnumber As Integer, ByRef Accountinfo As String)

‘ Comment: This is called subroutine #1               

End Sub

After inserting a ByVal and/or a ByRef variable, the macro disappears once I get out of it. Does anyone know how I can have my macro stay in my list and display it's name so I can re-edit it and execute run it? Thank you for any help.

[#4]

@Tommee 

See Called sub macro disappears after adding ByRef or ByVal variables and exiting out 

[#5]

Hi Hans,
I found it within Developer under Object Browser.
Thank you so much!

[#6]

Note that you can also get to the procedure code quickly by right-clicking its name in calling code, and selecting "Definition" from the popup (context) menu. This also works for variable names, and for the property names and method names of most objects (if they are built-in objects, it shows them in the Object Browser).

[#7]

@SnowMan55 

Hi Snowman,

I tried this and it worked! So much easier!

Tommee