Public variable in VBA Excel

  Desert Andy 17:09 11 Mar 2007

Is it possible to set the value of a variable on the opening sheet of a excel workbook, and then use this same variable on all the worksheets.

  VoG II 17:32 11 Mar 2007

Right click the Excel logo to the left of File > View Code. Paste in:

Private Sub Workbook_Open()
Call init
End Sub

Then in a regular module use code like

Public Myconst As Variant

Sub init()
Myconst = 123
End Sub

Sub test()
MsgBox Myconst
End Sub

  Desert Andy 18:20 11 Mar 2007

This is perfrect if you know what the variable is, however, on my workbook, it opens on the start up page. The user has to make a choice between 2 options, and this sets the variable I want to use throughout the workbook. I put in your code, and before the line Myconst = 123 I tried to select a cell in the start up sheet which had the variable to use. When i did this I get the error message Compile error: Invalid outside procedure.

Any way to get around this. The constant variable to be used in the remainder of the workbook is in cell "AB3"

  VoG II 18:36 11 Mar 2007

This works for me. Adjust sheet name and range to suit.

Sub init()
Myconst = Sheets("Sheet1").Range("A1").Value
End Sub

  Desert Andy 18:45 11 Mar 2007

OK, i don't get an error now, however, I must be putting this code in the wrong place since I get nothing in my message box. When you say put the code in a regular module, what do you mean please.

  VoG II 18:54 11 Mar 2007

In the VB editor, Insert > Module. Make sure that

Public Myconst As Variant

is at the top of the module (but after any declaration like Option Explicit).

Note that you will need to save, close and re-open the workbook for the Private Sub Workbook_Open() to fire.

  Desert Andy 19:08 11 Mar 2007

OK, getting somewhere now. If I run the macro test routine, then the msgbox gives the right answer, however, if i use the msgbox myconst in code in any worksheet, it appears that myconst is empty. Can I use the variable in day to day code in worksheets

  VoG II 19:16 11 Mar 2007

I've tested it and it works on every sheet. Please note that certain actions, like opening the VB Editor may reset it to null.

  Desert Andy 19:31 11 Mar 2007

Thanks for your help, unfortunately I can't get this to work at all. I can run the macros and that works fine, but as soon as i go to a worksheet the variable is empty. I have put the sub init() in a new module 2. The public myconst as variant is at the top of the module. There is the usual horizontal line produced and then the sub init() code is seen. Is there anything simple I could have missed?

  Desert Andy 19:41 11 Mar 2007

I have solved the problem, I left the public declaration in a worksheet code before i put it in a module. Sorry to have caused any problems, it now works fine, thanks very much for all your help.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Is this the future of VR and AR?

Best iPad buying guide 2017

Comment regarder le Bureau des L├ęgendes en ligne ?