Worksheet wide vars

[Guest]

I wish to have fixed content vars for use in my code in order to avoid using
hardcoded content which would require a lot of editing in case it is
necessary to change a value.

PUBLIC var AS STRING in THISWORKBOOK should do the job.

The question is where can I initialize this var to my desired content and
this initialization be done at workbook startup. How can I define a procedure
that is executed as soon as the workbook is loaded, so that inside it I can
perform any initializations are necessary? Is there a default such procedure
(whose name and existence I obviously ignore...)?

 

[Guest]

Depending on how often the values change, you may want to use public
constants instead; e.g.

Public Const MyName = "Ken"
Public Const FavoriteColor = vbRed
Public Const Age = 42
Public Const Weight = "None of your business!"

You can just put all of these in a standard module, maybe name it CONSTANTS,
and edit the values when needed.

 

[Guest]

....and if you need to perform any functions whenever the workbook is open
there is a default function in ThisWorkbook called Workbook_Open.

Select the ThisWorkbook module in VBAProject and click the pull-down that
has "(General)" in it. Select the Workbook function and the Workbook_Open
sub will appear. Whatever functionality you code into this sub will be
executed whenever the workbook is opened.

 

[Roman]

You can use a sub called auto_open that is fired each time workbook is
opened or a workbook_open event in thisworkbook module to start a code
each time workbook is opened. But you don't need doing this to declare
Publics.

 

[Guest]

You both know by now that I am new to Excel programming. Still Charlie's
answer was what I was really asking for since I cannot implement K answer.
Thanks Charlie.

Undoubtedly K's answer was more to the point considering the usage of what I
asked. Unfortunately I cannot do what you suggest K because I don't know how
to create a standard module and place my variables inside. So far I've only
written code to the Microsoft Excel Objects part of the project. I can see
the Modules part but I don't know what it's for. I had discovered the Public
Const statement but its description suggested a standard module and clicking
on it wasn't really helpful to me. If it's not boring for you, pls explain
what a standard module is and how to create one. If I knew that, I wouldn't
need to use the Workbook_Open procedure in this case.

 

[Guest]

I agree. Overkill for a mere declaration.

I believe I may have succeeded in doing what K Dales suggested. I created a
new module under Modules, which got the name Module2 ( I cannot rename it to
Constants ), and in it I declared my variable with PUBLIC CONST without
putting anything else in that module. My project is working fine. Is this
what a standard module is ???? just declarations and no coding (procedures,
subs and so on)??

Please reply

 

[Guest]

Right click on the VBAProject name in the Explorer and do Insert-->Module.
Then double-click the module name (Module1) in the Modules folder. Now you
can create subs and functions for global use in any sheet of the workbook.
At the top of the module is where you can declare public constants, for
example, I prefer to use 1-based arrays and prefer the use of "Yes" and "No"
to "True" and "False", so I always put in these lines at the top of Module1

Option Explicit
Option Base 1

Global Const Yes As Boolean = True
Global Const No As Boolean = False

(etc.)

Then you can start inserting your own functions:

Public Function Whatever(iRow As Long, iCol As Long) As String

....

Later you can right-click on the module name and export it to a folder and
later import it into another workbook. (Wish that part was automatic, like
VB projects.)



:

(snip)

 

[Norman Jones]

Hi Doctor G.

You might find it helpful to visit David McRitchie's site an see his
'Getting Started with Macros and User Defined Functions' notes at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

As a new programmer, you will find a wealth of other matrial on David's
site.

 

[Tom Ogilvy]

In the VBE, do Insert=>Module in the menu.

 

[Guest]

Thanks to ALL OF YOU for being so prompt and helpful. I really covered some
holes today. I needed to sort out certain "meanings" and link them to what I
already know from my DOS/Clipper programming days.
My project is working and I believe I now understand how to use Modules
effectively. McRitchie's site was also very helpful.
Thanks again - I wish I could do the same for you.

 

[Tom Ogilvy]

Make the properties window visible in the VBE while the module is selected.
The only property for the module is the change. Change it to Constants by
editing the existing name (module2)