I’m learning macros as I go, so I hope there is an obvious solution here. Google and Youtube doesn’t seem to help much!
I refer to a specific cell to be changed in a variety of functions, based on the users needs. Inside of each individual function, I have been using ‘Dim’ and ‘Set’ i.e.:
Dim foodtype As Range
Set foodtype = Worksheets("Sheet1").Range("A1")
Is there a way that I can just establish the variable once and use it in individual functions?
Create a new module and apply the following code at the top:
Option Explicit
Public foodtype as Range
Public Sub sInitialise
Set foodtype = Worksheets("Sheet1").Range("A1")
End Function
You can now call Sub sInitialise from any appropriate point in your code and foodtype will retain that value and make it available everywhere.
Alternatively, you can use Set foodtype = Worksheets("YourSheetName").Range("YourRange") to any required value anywhere in your code without having to declare it in a Dim statement.