Public variable question

[MrKremrik]

Hi all,

I have several macros in a personal folder that all need to reference the same variables located in a single file. So I declared these variables as public. But I don't know the syntax for assigning a cell value to these. Something like: Public Price as Double. And then: Price = workbook.xls-sheet1-"E3". I know that's not right, but hopefully it gets the point across. Thanks!

 

[AlphaFrog]

Something like...

Price = Workbooks("Book1.xls").Sheets("Sheet1").Range("E3").Value

 

[Joe4]

I have never tried this before, but it seems like it should work, in theory.
Since the "Personal Macro Workbook" is actually a workbook itself, why not store your variables/values there?
They should then always be open and available, just like the Personal Macros Workbook macros are.

 

[MrKremrik]

Something like...

Price = Workbooks("Book1.xls").Sheets("Sheet1").Range("E3").Value

Okay, that didn't work. It said "Invalid Outside Procedure".

@Joe4. Unless I don't fully understand what you're recommending, I don't know that it would work. The file needs user input in E3 of the aforementioned file (which is not the personal.xls workbook). The value in this cell needs to be a variable that 5 different macros can use, just so I can eliminate a ton of superfluous coding.

Thanks!

 

[Joe4]

Sorry, I thought you might be working with "static" variables, and not a user input ones that change.

 

[MrKremrik]

Sorry, I thought you might be working with "static" variables, and not a user input ones that change.

Oh no problem! Is there any way to do something like what AlphaFrog was saying?

 

[Joe4]

Is the workbook you need to reference open when you are trying to use the formula?

 

[MrKremrik]

Yes it is.

 

[Joe4]

AlphaFrog's syntax seems to work fine for me.

Could you post your code, where you are trying to use it?

 

[MrKremrik]

 Public qnty As Double
Public Price As Double
    Price = Workbooks("Book1.xls").Sheets("Sheet1").Range("E3").Value
Public stdrdP As Double

Sub quote()...

...

I have the piece of code above various "subs" I have in the same module. Is this wrong?