I am creating public variables in my program to be used across multiple subs in the module.
Public c As Range, wsSrc As Worksheet, wsDest As Worksheet, cDest As Range
However I want these variables to have the following value across the entire function as well
Set wsSrc = Workbooks("SourceBook").Worksheets("SourceSheet")
Set wsDest = Workbooks("DestBook").Worksheets("DestSheet")
Is there a way for me to set these and their values carry across the entire function? Also for c and cDest I am changing those in the functions so I Do Not want those defined globally. Only wsSRC and wsDest. Are variables in VBA immutable?
This is a question of scope. For using variables across a module, you don't need to set them public - Public is used for making variables available to other modules.
In this case just need to declare variables outside function
'Module begining
Dim wsSrc As Worksheet, wsDest As Worksheet
Function f1
Dim c As Range, cDest As Range
'... wsSrc and wsDest are acessible, eg:
Set c = wsSrc.Columns(2).Find(What:="Commercial Income", LookIn:=xlValues, _ LookAt:=xlPart, MatchCase:=False)
End Function
Function f2
'... wsSrc and wsDest are also acessible here
End Function
However, to avoid initialization problems, you may use them as properties, so each time they are called, value is properly returned:
'Module begining
Property Get wsSrc As Worksheet
Set wsSrc = Workbooks("SourceBook").Worksheets("SourceSheet")
End Property
Property Get wsSrc As Worksheet
Set wsDest = Workbooks("DestBook").Worksheets("DestSheet")
End Property
Function f1
Dim c As Range, cDest As Range
'... wsSrc and wsDest are acessible
End Function
Function f2
'... wsSrc and wsDest are also acessible here
End Function