Static Variable

[Todd Huttenstine]

hey

I have a variable(number) and want it to not die until the
workbook is closed. I need to be able to see the value of
the variable from a worksheet module. How would I do this
and what module would I put the variable in?

Thank you
Todd Huttenstine

 

[Chip Pearson]

Todd,

Declare the variable as Public in any standard code module, above
and outside of any procedure. E.g.,

Public MyVar As Long


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Todd Huttenstine]

See I did that. Below code is in workbiook module.

Public Sub Workbook_BeforePrint(Cancel As Boolean)
Static PrintCounter
PrintCounter = PrintCounter + 1
MsgBox "print Job: " & PrintCounter
End Sub

Now the value for variable PrintCounter can be 5 but here
is the problem.

I click a print button on worksheets(5) and the code in it
is along the lines of:

If PrintCounter > 0 then
else
End If


In this case, the code is seeing the value of PrintCounter
as "EMPTY" but the real value is 5. PrintCounter value is
still 5, but for some reason this code cant see that. How
do I correct this?

 

[Chip Pearson]

Todd,

Since you have declared the variable PrintCounter within the
BeforePrint procedure, it is visible only within that procedure.
It cannot be accessed by any other procedure. The declaration
'Static' means only that the value of the variable is retained
between calls to that procedure. It does not change the scope
(accessiblity) of the variable. If you want to be able to access
the contents of a variable from more than one procedure, you must
declare it as a Public variable in a standard code module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Todd Huttenstine]

Ahhh! Thank you.

-----Original Message-----
Todd,

Since you have declared the variable PrintCounter within the
BeforePrint procedure, it is visible only within that procedure.
It cannot be accessed by any other procedure. The declaration
'Static' means only that the value of the variable is retained
between calls to that procedure. It does not change the scope
(accessiblity) of the variable. If you want to be able to access
the contents of a variable from more than one procedure, you must
declare it as a Public variable in a standard code module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message news:[email protected]...


.

 

[Bob Phillips]

I may be missing something, but what you have done is not what Chip
suggested. Your variable is not a Public variable and is not in a standard
module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Todd Huttenstine]

hey Bob

I created a standard module called PrintCount with a
procedure by the name of PrintCount as well. In the
procedure I put the following code:

Static PrintCounter

Dim RngCounter As Long
If PrintCounter > 0 Then
RngCounter = Application.WorksheetFunction.CountA
(Worksheets("Wkly Renewals").Range("D"))
With CreateObject("Wscript.Shell")
.Popup "The name of the active printer is " &
Application.ActivePrinter _
, 1, "Print Confirmation", 64
End With
Worksheets("Wkly Renewals").Range("D7:F" & RngCounter +
2).PrintOut
Else

If MsgBox("The name of the active printer is " &
Application.ActivePrinter & vbCrLf & _
"Do you want to use this printer?" & vbCrLf & _
"" & vbCrLf & _
"Click no to select a different printer.", vbYesNo) = vbNo
Then

RngCounter = Application.WorksheetFunction.CountA
(Worksheets("Wkly Renewals").Range("D"))
ActiveSheet.PageSetup.PrintArea = "$D$7:$F$" & RngCounter
+ 2
PrintCounter = PrintCounter + 1
Application.Dialogs(xlDialogPrint).Show
ActiveSheet.PageSetup.PrintArea = ""
Else
PrintCounter = PrintCounter + 1
RngCounter = Application.WorksheetFunction.CountA
(Worksheets("Wkly Renewals").Range("D"))
Worksheets("Wkly Renewals").Range("D7:F" & RngCounter +
2).PrintOut
End If
End If

PrintCounter = PrintCounter + 1
With CreateObject("Wscript.Shell")
.Popup "Print Job: " & PrintCounter _
, 1, "Print Number", 64
End With


Then in the Print button (commandbutton) code I put:
PrintCount.PrintCount
This way it calls the procedure with the static variable.


This works. My problem was I was just stuck on using the
beforeprint event which I had put in the workbook module.
This throwing me for a loop.

Thanx for everyones help!


Todd

 

[onedaywhen]

Note that Static is not common usage; I've never seen it in product
code. You will more often see a Property Let/Get pair with a private
module-level variable to persist the property's value e.g.

Private m_strName As String

Public Property Get Name() As String
Name = m_strName
End Property

Public Property Let Name(ByVal NewValue As String)
m_strName = NewValue
End Property

This gives you more control over the underlying value because you can
add code to the Get/Let e.g. to make write-once:

Public Property Let Name(ByVal NewValue As String)
If m_strName <> Empty Then
Exit Property
End If
m_strName = NewValue
End Property

--