Public Variables

[Richard]

Hi

Instead of restating the following in each module or procedure I would like
to set a public variable to do the following:

Dim strYear As String

strYear = WorksheetFunction.WorkDay(Now(), -1)
strYear = Format(strYear, "yyyy")

I understand that in a seperate module I can simply state:

public strYear As String

but do not understand how to state that strYear is equal to
WorksheetFunction.WorkDay(Now(), -1) and to format it correctly.

Can you please advise.

Thanks in advance

Richard

 

[Dave Peterson]

First, if you want to share that variable between modules, you'll want to make
it Public (not just use Dim).

Public strYear as Variable 'sometimes it's a date
Public VarsAreInitialized as Boolean 'more on this later

This would be at the top of the module.

Then you need something that does the actual work.

I'd suggest a Subroutine that initializes all your variables (and you'd use that
public variable to check).

In any old procedure that may use any of the public variables.

if varsareinitialized = false then
call InitializeTheVariables
end if
msgbox strYear

========
Then in a new procedure, initialize all the variables you need.

Sub InitializeTheVariables()
VarsAreInitialized = true 'the flag that keeps track

'your code for setting strYear
strYear = WorksheetFunction.WorkDay(Now(), -1)
strYear = Format(strYear, "yyyy")

'or dim strYear as a String and do it all at once:
strYear = format(WorksheetFunction.WorkDay(Now(), -1), "yyyy")

End Sub

 

[JLGWhiz]

You make your public declaration at the top of the module, outside any
procedure. Then you only have to assign the value and formatting once
inside any single procedure and it will apply wherever it is used in any
other procedure in that module.

 

[Dave Peterson]

Ps. You don't need the parens near the now() in your code. That's required in
a formula in a cell in excel.

This will work fine:
strYear = format(WorksheetFunction.WorkDay(Now, -1), "yyyy")
as will:
strYear = format(WorksheetFunction.WorkDay(Date, -1), "yyyy")

<<snipped>>

 

[tompl]

A brief summary, I hope it is not too cryptic:

• Variable scope:
o Procedural level variable (declared within the procedure and available
only within the procedure).
o Module level variable (declared at the top of the module)
Dim VariableName as Type (Available only within the module).
Private VariableName as Type (Available only within the module).
Public VariableName as Type (Available throughout the project).
• Variable lifetime:
o By default variables retain their assigned value only until the
subroutine in which they are declared executes the exit or end statement.
o If the subroutine or the variable is declared as static the variable
retains its value until the workbook is closed.
o Module level variables are static.

Tom

 

[Richard]

Dave

thanks for the help that sorted it nicely. I am also grateful for the
simplified code to format the string I am creating.

Richard