VBA - pass variable between Excel projects

Question

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:

Public sapEEID As String
Private Sub UpdateLetterTemplate

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?

support.microsoft.com/kb/153307

Siddharth Rout
– Siddharth Rout

2014-07-17 12:55:43 +00:00
Commented Jul 17, 2014 at 12:55 — Siddharth Rout
– Siddharth Rout, Commented Jul 17, 2014 at 12:55

hnk · Accepted Answer · 2014-07-17 12:51:20Z

2

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

answered Jul 17, 2014 at 12:51

hnk

2,2221 gold badge15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Maciek Over a year ago

Thanks! I didn't know you can make a reference to Excel add-in :)

Collectives™ on Stack Overflow

VBA - pass variable between Excel projects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related