CODEWITHSUNDEEP
vbaexcel
Kay
Kay

Reputation: 21

Naming a sheet a global string variable taken from a different workbook

I seem to have successfully created this global string variable as I can print it in the MsgBox in the sub I am trying to use it in.

Current Code:

First Sub: 

Option Explicit
Public storeName As String
Sub cleanupProdReq()

'takes storename as public variable
storeName = Cells(5, "C").Value

Next Sub:

Sub SelectActualUsedRange()

Dim w As Workbook
Set w = ActiveWorkbook

'opens requests workbook, names sheet today unles there is already one

Workbooks.Open ("C:\Users\***\Documents\***\***\Supply Chain\Requests")

Dim wsTest As Worksheet
Const strSheetName As String = storeName

When I run this it highlights the last occurrence of 'storeName' and gives the error: "Constant Expression Required". Can anyone suggest a way around this?

Upvotes: 0

Views: 46

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

Const strSheetName As String = storeName

The value of a Const must be determined at compile-time.

You're assigning it to a variable, whose value is only determined at run-time.

Use Dim instead of Const to declare strSheetName, and the compiler will be happy.

Upvotes: 0

Related Questions