vba - Assigning the value of a worksheet cell to a constant

Question

I am trying to assign the value of a worksheet cell to a constant variable in a VBA macro. The logic behind that action is that the end user is supposed to enter the current week in a specified cell before running the Macro. Since this value is going to be reused throughout the macro and I wanted to play it safe, I tried to declare it as a public constant:

private const thisWeek as Integer = Range("B1")

However I get an error message about a constant value being needed. So, is it even possible to declare a constant like this in VBA?

Answer 1

No it is not possible. As the word suggest it should be Constant.

Workaround:

Public Const weekRange As String = "$B$1"

Then in your code:

Sub Something()
    Dim thisWeek As Integer: thisWeek = Range(weekRange).Value
'~~> some codes here
End Sub