Initialize a Private variable

[jerry chapman]

I have a private variable:

Private outlookDate As Date

How can I set the value of this variable before I run any macros?

 

[Tom Ogilvy]

You can do it in the workbook_Open event

Private Sub Workbook_Open()
OutLookDate = DateSerial(Year(Date),Month(Date)+6,1)
End Sub

Place this in the ThisWorkbook Module

See Chip Pearson's page on events
http://www.cpearson.com/excel/events.htm


Or just set it in your first macro that is to run.

 

[Guest]

Tom,

If the variable in declared as Private, I was under the impression that it's
value diappears at the end of the procedure in which you set the value. If
this is the case, the OP would have to reset the value in any other sub where
he wanted to use the variable.

I have always thought that if the OP wanted to set the value as you did in
the Workbook_Open event for use somewhere else, then the variable would have
to be declared Public

Am I confused?

 

[Tom Ogilvy]

Thanks for pointing that out.

Actually, I didn't actually correctly see how he declared it.

Sometimes you look and see what you expect, not what is actually there.

I was talking about a public/global variable.

If it is private, but declared at the top of the module, it is only visible
to the procedures in the module. If it is declared in a procedure, it is
visible only to that procedure and as you say would go out of scope when the
procedure ended.

So to the OP,
you would use the workbook_Open event to call a procedure in that module
that initializes the variable. Otherwise, you might reexamine what you are
trying to achieve.

 

[jerry chapman]

I tried the following code, but it didn't seem to be called:

Public Sub Workbook_Open()
maxOutlookDate = "02/02/2005"
MsgBox "maxoutlookdate " & maxOutlookDate
End Sub
What did I do wrong?

 

[Tom Ogilvy]

did you put it in the thisworkbook module?

It always works for me when I do that.

Assumes you are not opening your workbook with code triggered by a shortcut
key combination that includes Shift and you haven't disabled events.