Passing variable from sheet code to module code

Question

I have a problem passing variables from sheet code to module code. Here is the sheet code:

Public Sub Update()

Dim plan As String

plan = "PlanB"

'Code stuff here

Call LookForManualData(plan)

'Code stuff here

End Sub

And here is the module:

Public Sub LookForManualData(plan As String)

'Code stuff here

MsgBox plan

'Code stuff here

End Sub

Please note, that the code is simplified, and irrelevant parts are excluded. Also, that I have tried pretty much possibility for defining plan as a variable, although there has to be be something that hasn't came in my mind. The basic idea is to let the module know what plan is to be able to use it, because now it's empty.

Because it doesn't pass the plan variable to the module, it is empty in LookForManualData. — e700k
– e700k, Commented May 23, 2014 at 9:36
There must be something happening in the code you omitted because what you posted does work. — Rory
– Rory, Commented May 23, 2014 at 9:42
As others have mentioned, there must be something happening within the rest of your code which is changing plan. I'd suggest searching the procedure for something like plan = " to see if it's being changed. — Gareth
– Gareth, Commented May 23, 2014 at 10:12

L42 · Accepted Answer · 2014-05-23 12:14:06Z

1

To pass variable values and do what you want you need to declare the variable in any module:

Option Explicit
Public plan As String '~~> at the very top of the module outside any sub

Public Sub Anysub()
'~~> any code
End Sub

Then you can then set it in any sheet code as you want and then call it in any sub.

edited May 23, 2014 at 12:14

answered May 23, 2014 at 9:36

L42

19.8k11 gold badges47 silver badges70 bronze badges

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

7 Comments

Rory Over a year ago

That is not correct-you can pass arguments as in the sample posted.

RubberDuck Over a year ago

@Rory, Technically this works, it's just not good practice to use globals.

Rory Over a year ago

I wasn't suggesting this wouldn't work. I was objecting to the statement that you have to use a public variable rather than passing it as an argument.

Dan Wagner Over a year ago

Whoa... my add-ins typically have an entire module dedicated JUST to global variables, like MaxNumberOfFiles = 1000, Excel2007ForWin = 51, Excel2007ForWinBinary = 50, Blank = vbNullString etc... is there a "best practices" discussion on using global variables somewhere that I missed?

RubberDuck Over a year ago

@L42 You're being down voted because this only solves the superficial problem in what many consider to be poor practice. Yes it works, but that doesn't make it correct. If OP clarifies and you update, I'll be happy to remove my down vote.

|

Collectives™ on Stack Overflow

Passing variable from sheet code to module code

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related