scope of public variables

[Guest]

I wonder why the frRptDate and the toRptDate variables never show the values
in the message box? This macro is an option button on a form. I have
defined these variables as public variables in a previous module that calls
the form and I did a debug. print statement to see that the variables are
getting set with the value. Just when the user clicks the form I would like
a msgbox with the date of the report so the scope of the variables should be
for the entire project right? Correct me if I am wrong.
-----Form macro that has msg box with dates--------

Sub optCkforDupes_Click()
Dim nResult As Long
Debug.Print frRptDate
Debug.Print toRptDate
Fprocess1 = False

If optCkforDupes.Value = True Then
nResult = MsgBox(prompt:="From: " & frRptDate & vbNewLine & "To: " &
toRptDate, Buttons:=bvOKCancel, Title:="report Date")

End If
If nResult = vbOK Then
Call createXLdb.CkforDupes
Fprocess1 = True
End If

End Sub
Its always cell A1 & B1 that has the dates. They have to be removed since
it is a database spreadsheet.
----------the module that calls the form------

Option Explicit
Public FrReptDate As Date
Public ToReptDate As Date
--

Public Sub LoadProcessChoices()
On Error GoTo ERR_Message
openFile
deleteDateRow1
ProcessChoices.Show
End Sub
------
Private Sub openFile()

Const fName As String = "Book1.xls"
Const fPath As String = "Mac OS X:SSP Process:"

Dim wkb As Workbook
On Error Resume Next

Set wkb = Workbooks(fName)
On Error GoTo 0
If wkb Is Nothing Then _
Set wkb = Workbooks.Open(Filename:=fPath & fName)

End Sub
-----

Sub deleteDateRow1()
Dim Rng As Range
Set Rng = Range("A1")
With Rng
If IsDate(.Value) Then
FrReptDate = .Value
ToReptDate = .Offset(0, 1).Value

..EntireRow.Delete Shift:=xlUp
Debug.Print FrReptDate
Debug.Print ToReptDate

Else
Exit Sub
End If
End With
End Sub


THANKS,

 

[Bob Phillips]

Public FrReptDate As Date
Public ToReptDate As Date

;;;;;--------------------------------

Debug.Print frRptDate
Debug.Print toRptDate


Check your spelling.

If you Option Explicit at the head of your modules, that would have trapped
that.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

There are two main reasons why you are not getting the values. One is that
the values were cleared at some point (easy to do inadvertantly) or you have
not completely referenced the variables (more likely in this case).

1. If at any point in your code you have the standalone line End then that
line will clear all globally declared variables and objects. Try to avoid
code like this at all cost

If x = 0 then
end 'You just cleared all globals
end if

If your globals are declared anywhere other than a standard code module
(where recorded macro code lives) then you need to fully reference the
variable. For example lets assume that you have declared a global varaible in
ThisWorkbook. If you intend to reference that variable outside of the
thisworkbook module then you must use
msgbox Thisworkbook.MyGlobalVariable
if you just do
msgbox MyGlobalVariable
Then you will get nothing back in the message box. So now the big question
is where did you declare your variable. If they are in a sheet or
ThisWorkbook then you need to explicitly reference the variable...

 

[Guest]

Oops ignore my reply and go with Bob's. I missed the typo... Here is a link
to Option Explicit and what it is all about...

http://www.cpearson.com/excel/DeclaringVariables.htm

 

[Guest]

well this is helpful to keep in mind with the end.

 

[Guest]

I did have it option explicit. I must have declared it twice once globally
and the other in the module. Thanks, it should be working now.

 

[Bob Phillips]

You need to declare it in all modules, better still get the IDE to do it by
Tools>Options>Editor and check the Require Variable Declaration box

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)