This question seems to be bang on what I am looking for in terms of declaring a variable that can be called and and retains information from anywhere.
I have a userform that provides a project number and a filepath both as string and I have another as a boolean to determine if OK or CANCEL button was selected and how to proceed when the process returns to the sub. From the research I was doing, it seems to indicate that the easiest way to do this is through something called a global variable. While this may be the easiest way, is it the proper way?
I have tried placing my variable declaration at the top of the code sheet before the first sub/fun in Sheet3 where most of my code is and where the button click code is for launching the the user form. I have also tried placing it at the top of the code in ThisWorkBook. And I have also tried to place it at the top of the Code page for the associated userform.
I only ever declare them in one place. I do not declare them in multiple places at the same time.
When I declare on Sheet3 I get an error in a sub placed in the code for the userform
Associated Code:
Private Sub OKBtn_Click()
strPPTFilepath = PPTPath.text
strProjectNumber = ProjectNoTextBox.text
bCancelled = False
End Sub
When I declare it in ThisWorkBook I get:
And the code causing the error is located in Sheet3 and is as follows:
Private Sub LoadPPT_Click()
Dim frm As PPT_Picker_Form
Dim wbPPT As Workbook
Dim wsPPT As Worksheet
Dim rngTopLeft As Range
Dim lngLastUsedRow As Long
Dim lngLastCo As Long
'Public strPPTFilepath As String
'Public strProjectNumber As String
Set frm = UserForms.Add(PPT_Picker_Form.Name)
frm.ListData = ThisWorkbook.Worksheets("Project Numbers").ListObjects("Project_Number_List").DataBodyRange
frm.show
Unload frm
If bCancelled Then '<----this is the undeclared variable
Exit Sub
End If
'Define PPT Table
Set wbPPT = Workbooks.Add(strPPTFilepath)
Set wsPPT = wbPPT.Worksheets("Fee Estimate")
'Find top left corner
With wsPPT.Cells
Set rngTopLeft = .Find("WBS Code", LookIn:=xlValues)
If rngTopLeft Is Nothing Then
'nothing found
'add a manual cell picker routine
End If
End With
'Find last row
'find Right edge
' strProjectNumber
End Sub
When I declare it in the userform code area I get the same error as above in the same location.
What is the proper way to pass information from a form to a sub on sheet3?
Holy crap I can't believe I forgot to show the code for declaring my variables, so here it is:
'set it so that all subs and functions require variables to be dimensioned
Option Explicit
'variable available across all modules?
Public strPPTFilepath As String
Public strProjectNumber As String
Public bCancelled As Boolean
You could use a public variable so that the script can access it from both the UserForm
, ThisWorkbook
and worksheet modules. The public variables should be declared in a standard module rather than in other modules.
In VBA editor (VBE), Insert -> Module
, add the below line in the Code
window.
Option Explicit
'variable available across all modules?
Public strPPTFilepath As String
Public strProjectNumber As String
Public bCancelled As Boolean
Microsoft documentation:
Public variables can be used in any procedures in the project. If a public variable is declared in a standard module or a class module, it can also be used in any projects that reference the project where the public variable is declared. [From: Declaring variables]