How to Call Subscripts or Functions and send data between them in VBA?


This section will go through how to pass data between 2 scripts. Whilst I touched upon the subject in the VBA basics section I wanted to add this page for clarification and to underline its strength.

pass data vba

Passing data between scripts is a great knowledge to have, and it is highly recommended rather than creating 1 superlarge script that takes you ages to scroll. It also reduces your code from repetitive parts, and therefore makes it much easier to troubleshoot which you will have to. Common subscripts could be doing searching for something, where that something changes a lot of time, email scripts, printing scripts. Well.. anything really that suits your project.

Code


Let's start with passing one string to illustrate how it could look like. One thing to note is that the second script that receives the variable is unaware of its type. We therefore have to declare the variable, which also means that we can give it any name we want. The script only knows that it will receive some data.

Sub PassVariable_Simple()

Dim Str As String
Str = "Pls Fix Thx"

'Using the 'Call' syntax. 
'Whilst not necessary, I think the code looks cleaner using this syntax.
Call RecieveVariable_Simple(Str) 'Sending a variable of type string.

End Sub

'********************************************************
'Receiving a variable of type string. 
Sub RecieveVariable_Simple(MyVar As String)

Debug.Print MyVar

End Sub
send variable to a sub in vba

Now that we have learned how to send 1 variable, a string.

Let's send more variables.

Sub PassVariables()

Dim Nr As Double
Nr = 7

Dim Rng As Range
Set Rng = Range("A1:A7")

Dim MyArr(1 To 5) As String

MyArr(1) = "Please"
MyArr(2) = "Analyze"
MyArr(3) = "The"
MyArr(4) = "Attached"
MyArr(5) = "Thx."

'Send all variables.
Call RecieveVariables(Nr, Rng, MyArr)

End Sub

'**************************************************************
'Receive a lot more variables.
Sub RecieveVariables(MyDouble As Double, MyRange As Range, MyArray As Variant)

'Print out my variables to know that it worked as planned.
Debug.Print MyDouble
Debug.Print MyRange.Address
Debug.Print MyArray(1)

End Sub

send data to a function in vba

Now this exemplifies how to send data between 2 scripts in VBA. Granted, these are both of type subs, but it works exactly the same for Functions.


1 view0 comments