CODEWITHSUNDEEP
vbaexcelmacros
Chuck0185
Chuck0185

Reputation: 531

dim and set a variable across all modules excel vba

I have many projects where I have the same variables across multiple modules. In each module I dim and set the variables and each time they are the same variable type and have the same value. How do I dim and set variables across an entire project/workbook?

Ex: (I have many modules in a workbook where I have had to repeat all of the following along with many other similar variables that do not change across modules)

Sub PullSFAFiles()
Dim Wb                          As Workbook
Dim WsSFAFiles                  As Worksheet
Dim WsAllCourses                As Worksheet
Dim rngAllCourses               As Range
Dim rngCourse                   As Range
Dim LoSFAFiles                  As ListObject
Dim rngPreviousFiles            As Range
Dim rngRemoveLines              As Range

Dim strCourse                   As String
Dim strApp                      As String
Dim strPeCFldrPath              As String
Dim strFileLocation             As String
Dim strFileNm                   As String
Dim objFile                     As Object

Dim intSFARow                   As Integer
Dim intCourseRow                As Integer
Dim intPFilesRow                As Integer
Dim dtLastUpdate                As Date
Dim intNumRemove                As Integer

Set Wb = ThisWorkbook
Set WsSFAFiles = Wb.Sheets("sfafiles")
Set WsAllCourses = Wb.Sheets("allcourses")
Set rngAllCourses = WsAllCourses.Range("tblAllCourses[CourseName]")
Set LoSFAFiles = WsSFAFiles.ListObjects("tblSFAFiles")

strEBTypeFolder = "Exercise Booklet"
strEBfiletype = "EB"
strCISTypeFolder = "Classroom Information Sheet"
strCISfiletype = "CIS"
intCourseRow = rngCourse.Row - 1
strCourse = rngCourse.Value
strApp = WsAllCourses.Range("tblallcourses[application]").Rows(intCourseRow)
strPeCFldrPath = "\\Cx138\training\Live\Credentialed Trainers\"
strEBFileLocation = strApp & "\" & strTypeFolder & "\" & strCourse & "_" & strEBfiletype & "*" & ".pdf"
strEBFileNm = Dir(strPeCFldrPath & "\" & strEBFileLocation)
strCISFileNm = Dir(strPeCFldrPath & "\" & strCISFileLocation)

Upvotes: 1

Views: 17939

Answers (1)

Peter S
Peter S

Reputation: 66

Replace variable declaration Dim with Public. Thus:

Public rngCourse as Range

Public strCourse As String

Declare them at module level.

Upvotes: 5

Related Questions