Prev: Run make table query from Excel with Access closed
Next: Point to My Docs in Win7/XP, Excel 03/07
From: Richard on 8 Apr 2010 10:56
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

From: Dave Peterson on 8 Apr 2010 11:13
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






Richard wrote:
>
> 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
From: JLGWhiz on 8 Apr 2010 11:16
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.



"Richard" <Richard(a)discussions.microsoft.com> wrote in message
news:B5CBB361-96E0-4970-9919-95F4C587BEA2(a)microsoft.com...
> 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
>


From: Dave Peterson on 8 Apr 2010 11:20
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>>
From: tompl on 8 Apr 2010 12:28
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
 |  Next  |  Last
Pages: 1 2
Prev: Run make table query from Excel with Access closed
Next: Point to My Docs in Win7/XP, Excel 03/07