2

I would like to declare kind of global variables. What I want to do is initialize these variables, then use them in macros, and change their values in other macros.

I started to write it as public variables:

Option Explicit
'definition of global variables
Public r_start As Integer
Public r_end As Integer
Public c_little As Integer
Public c_big As Integer
Public c_sel_start As Integer
Public c_sel_end As Integer
Public c_data_start As Integer
Public c_data_end As Integer

Public Sub Init_Globals()
' Access global variable initialization
    r_start = 20
    r_end = 833
    c_little = 6
    c_big = 5
    c_sel_start = 1
    c_sel_end = 4
    c_data_start = 11
    c_data_end = 101
End Sub

The problem here is that I have to call Sub_Init_Globals() in each of my SubProcedure, and so if I want to change the initial values of my global variables inside other SubProcedures, those changes won't be made.

Do you know a way to create such variables ?

Community
  • 1
  • 1
M.Tailleur
  • 101
  • 7
  • One approach is to use a class module which contains the variables, and instantiate an instance of that class module. but this is still vulnerable to a "project reset". – Bathsheba Jul 20 '17 at 08:33
  • Calling Init_Globals() in the Workbook_Open() event is one approach. – Alex K. Jul 20 '17 at 10:02

2 Answers2

1

As far as I understood these are just starting values what leaves you with next options:
1.) You can declare these variables and assign values in Workbook_Open sub.
More here Is it possible to declare a public variable in vba and assign a default value?
2.) Create separate sheet, that will be hidden, with support table consisting of these values, in this case all changes to these values will be saved even after you close Workbook.
3.) Declare constants and assign it's value to a different variable inside Procedures.

AntiDrondert
  • 1,128
  • 8
  • 21
  • 1
    "I would simply comment, but sadly I can't :/" - Normally I would say "The fact that you can't comment is not a reason to misuse the system by placing a comment as an 'answer' ", but your post is nearly an answer already. So it might be better to remove that line from the answer (if I hadn't seen it, I wouldn't have known this wasn't a true answer) and possibly just "tidy up" the rest of it a bit. – YowE3K Jul 20 '17 at 08:55
  • Thank you for your answer, I want to do something extremely clean, it is meant to be use in my company. I will try the initialisation by cells in alternative sheet. – M.Tailleur Jul 20 '17 at 09:00
  • 1
    Thanks for reply. I understand the policy about forbidding new users from commenting, but it's geting redicolous to the point where I can't even ask for code sample or screenshot so I have to improvise and provide "common" solutions. – AntiDrondert Jul 20 '17 at 09:01
  • It doesn't take long to get the 50 rep needed to be able to leave comments - just hang in there for a bit longer and you will have it. – YowE3K Jul 20 '17 at 19:38
0
Public Const YourVariableName as Integer = 1

(or any other type or value of course) at the top of any user module seems to do the trick.

Paul Roub
  • 36,322
  • 27
  • 84
  • 93
Derek
  • 1