CODEWITHSUNDEEP
vbaexcelglobal-variablesadd-in
Maciek
Maciek

Reputation: 13

VBA - pass variable between Excel projects

My question: How can I set value of a variable declared in other Excel Project?

Background: I am working on calling a private sub from a diffrent Excel Project (I don't know if it matters, but the sub I am interested in is a part of Excel Add-In).

In the Add-In I have:

I am able to run the sub using the: Application.Run ("'Solutions Add-In.xlam'!UpdateLetterTemplate")

HOWEVER, variable sapEEID = ""

Is there a way to pass "17" as sapEEID when running UpdateLetterTemplate private sub?

Upvotes: 1

Views: 1608

Answers (1)

hnk
hnk

Reputation: 2214

You may need to modify your Subroutine somewhat but the following steps will work

  • Add Reference: You need to add a reference to your add-in (VBE -> Tools -> References)
  • ByRef Parameters: Also, make sure that your Sub can take a ByRef parameter. See sample code below.
  • Call the Subroutine: You're done, now in your code, once the reference is set, call the sub and pass your variable.

Sample Code for the Subroutine:

Public Sub ChangeToTen(ByRef a as double)
    a = 10
End Sub

Calling Code in your main file:

Dim a as double
a = 1023.23

Call ChangeToTen(a)
MsgBox(a)          ' It will show 10

Upvotes: 2

Related Questions