Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
scope of public variables
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
scope of public variables
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) "Janis" wrote in message ... 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
scope of public variables
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... -- HTH... Jim Thomlinson "Janis" wrote: 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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
scope of public variables
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 -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Janis" wrote: 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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
scope of public variables
well this is helpful to keep in mind with the end.
"Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Janis" wrote: 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, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
scope of public variables
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. "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Janis" wrote: 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, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
scope of public variables
You need to declare it in all modules, better still get the IDE to do it by
ToolsOptionsEditor and check the Require Variable Declaration box -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Janis" wrote in message ... 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. "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Janis" wrote: 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scope of Variables | Excel Discussion (Misc queries) | |||
Public variables and scope | Excel Programming | |||
Scope of variables | Excel Programming | |||
Scope of a public variable | Excel Programming | |||
Scope of Public Variable | Excel Programming |