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!

 

[Joe4]

I have to admit that I have not worked a lot with Public Variables, especially ones that are not constant.
See if these two links provide any help/guidance:
VBA Variables and Constants
excel vba - How do I declare a global variable in VBA? - Stack Overflow

 

[ZVI]

 Public qnty As Double

Public Price As Double
    [COLOR=#ff0000][B]Price = Workbooks("Book1.xls").Sheets("Sheet1").Range("E3").Valu[/B]e
[/COLOR]Public stdrdP As Double

Sub quote()...

...

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

The red line of code above is in wrong place.
You can't set the module variables between their declarations.

Use code like this:

Public qnty As Double
Public Price As Double
Public stdrdP As Double
 
 
Sub Init()
 
  ' Trap error if references are not valid
  On Error GoTo exit_
 
  ' Change to suit
  qnty = Workbooks("Book2.xls").Sheets("Sheet2").Range("A1").Value
  Price = Workbooks("Book1.xls").Sheets("Sheet1").Range("E3").Value
  stdrdP = Workbooks("Book3.xls").Sheets("Sheet3").Range("B2").Value
 
exit_:
 
  If Err Then MsgBox Err.Description, vbExclamation, "Init error"
 
End Sub

 

[MrKremrik]

I have to admit that I have not worked a lot with Public Variables, especially ones that are not constant.
See if these two links provide any help/guidance:
VBA Variables and Constants
excel vba - How do I declare a global variable in VBA? - Stack Overflow

Thanks for all the help! I think from what I've seen I'm just gonna have to do a little extra coding to make everything work in each macro. Learned a bit more about variables though...

@ZVI Thanks for the tip!