4

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?

Haris
  • 778
  • 2
  • 9
  • 20

3 Answers3

4

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
L42
  • 19,427
  • 11
  • 44
  • 68
2

From help: You can't use variables, user-defined functions, or intrinsic Visual Basic functions (such as Chr) in expressions assigned to constants.

In your case you have to use a variable.

Dmitry
  • 421
  • 4
  • 14
0

I know this is old but I was looking to do this myself and came up with this. I use a function:

Function insertPNA(strSource As Long) As String

Dim strResult As String

strResult = Replace(strSource, strSource, ThisWorkbook.Sheets("Audits & Actuals").Range("pnacode").Value)

insertPNA = strResult

End Function

Whenever I want the pna code I just type insertpna(1). Hope someone finds this useful. (It doesn't have to be "1" obvs, I just used it to minimize typing.)

Joe Patrick
  • 51
  • 1
  • 11