Passing a private variable to another module

**maacmaac** · 11-25-2008, 05:19 PM

I have some code in which I need to pass the value of "j" from one module to another. "j" is declared in Sheet1 (Data) under the Microsoft Excel Objects.

Please Login or Register  to view this content.

I need to pass the value of j to a class module which is coded as follows:

Please Login or Register  to view this content.

.

I have tried a number of different options such declaring "j" has a Public Variable and trying pass "j" ByVal but can't get any of these options to work. Not sure what I am doing wrong. Thanks.

**jasoncw** · 11-25-2008, 05:23 PM

It needs to be declared as public on a General Module.

HTH

Jason

**maacmaac** · 11-26-2008, 11:51 AM

I declared "j" as a public variable but not sure if my declare statement is correct or if I have declared it in the correct part of the code. I have included all the code and were each is stored and highlighted were I am using my variable. Thanks.

This part of the code is stored under "Microsoft Excel Objects" (This is were I get a compile error).

Please Login or Register  to view this content.

This part of the code is stored under "Class Module"

Please Login or Register  to view this content.

**Kenneth Hobson** · 11-26-2008, 12:03 PM

I don't understand. Put them in a Module.

You may need to delcare the Public in both the Module it is used in and the other.

**Andy Pope** · 11-26-2008, 12:04 PM

Are you sure about where each code sample is?

Are you saying your class exposes click events?

I thought I had posted a reply, maybe I forgot to submit.

You can declare a public variable in the class or a private variable with get/let properties.

In your main code you will need to assign the value to the property before executing the function that uses it other wise the value will be zero

**maacmaac** · 11-26-2008, 01:25 PM

Andy -

Sorry, you lost me in your comments. I'm not sure what the "get/let" properties are nor do I know where to assign the value. The whole assigning variables and letting other procedures use these variables is somewhat new to me. Realistically, I would really like to declare "j" as a private variable and then pass the value to the function that needs it. I have attached a copy of the spreadsheet so you can see where each code is stored. HTH. Thanks again for your comments.

**Kenneth Hobson** · 11-26-2008, 01:43 PM

As I said, if you will copy the Public to the top of both the sheet and the Module, it will work. Or, Dim j in the module where it was not defined as Public.

While you have few rows and J as integer will work, it is generally a good practice to dim variables that reference row numbers as Long.

**Leith Ross** · 11-26-2008, 02:03 PM

Hello maacmaac,

The attached file has the corrections. The row number "j' is now stored as a public variable "LineNumber" in the class module. Property Let assigns the value and Property Get returns the value.

Sincerely,
Leith Ross