Constants equal to Defined Name VBA

[dhosi439]

Is it possible to do this:


Originally I would set a constant, this is a pain because I would need to change all of the ranges manually if a row was added to my table:

Private Sub Worksheet_Change(ByVal Target As Range)
Const MONTH As String = "F5:F28,I5:I28,L5:L28,O5:O28,R5:R28,U5:U28,X5:X28,AA5:AA28,AD5:AD28,AG5:AG28,AJ5:AJ28,AM5:AM28"

Instead I would like to used a named range as this updates itself.

With the code below I get an error for

MONTH As String =

Compile error: Constant expression required

I am assuming I get this error because I cannot set a constant to this reference or variables.

Const MONTH As String = ThisWorkbook.Names("BillMonth").RefersToRange

OR

When MsgBox is used this prompts the correct data, the data is several columns from a table titled Bill Checklist. So the ranges are

='Bill Checklist'!$F$5:$F$25,'Bill Checklist'!$I$5:$I$25, and every third column until the end of the table.

The code below removes the equal sign from the range to be used as a string variable.

Dim MONTH As String
MONTH = ThisWorkbook.Names("BillMonth").RefersTo
MONTH = Mid(MONTH, 2, Len(MONTH) - 2)

The second option does output the correct ranges its just not reading into the code below not sure why...

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(MONTH)) Is Nothing Then
For Each cell In Target
    If (Target = "Paid") Then
        cell.NumberFormat = "@"
        cell.Font.Color = RGB(0, 0, 0)
        Call OpenMainForm
        frmMain.txtWith.Value = Format(ActiveCell.Offset(-1, 1) * -1, "Standard")
    ElseIf (Target = "Due") Then
        cell.NumberFormat = "@"
        cell.Font.Color = RGB(0, 0, 0)
    ElseIf (Target = "DueX") Then
        cell.NumberFormat = "@"
        cell.Font.Color = RGB(0, 0, 0)
    ElseIf (Target = "DueN") Then
        cell.NumberFormat = "@"
        cell.Font.Color = RGB(0, 0, 0)
    Else
        cell.NumberFormat = "@"
        cell.Font.Color = RGB(255, 0, 0)
    End If
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Sub

As a constant with the ranges inputted manually as show at the top this does work, not sure what needs to be changed for it to accept either a constant set to the named range or using the MONTH variable. It might be possible that the code doesn't recognize the table references, not too familiar with using table references and named ranges in this manner.

Any advice would help and be appreciated.

 

[Norie]

Why are you using a constant in the first place?

It just seems to be causing problems.

By the way I would recommend you don't use MONTH as a name for a constant or variable.

 

[tusharm]

1) Just use range("billmonth"). No need for an intermediate variable.

2) In your code, if Target refers to multiple cells, then the test for Target = "whatever" will yield a runtime error. If Target can never refer to multiple cells, then the For each loop is redundant.

Is it possible to do this:


Originally I would set a constant, this is a pain because I would need to change all of the ranges manually if a row was added to my table:

Private Sub Worksheet_Change(ByVal Target As Range)
Const MONTH As String = "F5:F28,I5:I28,L5:L28,O5:O28,R5:R28,U5:U28,X5:X28,AA5:AA28,AD5:AD28,AG5:AG28,AJ5:AJ28,AM5:AM28"

Instead I would like to used a named range as this updates itself.

With the code below I get an error for

MONTH As String =

Compile error: Constant expression required

I am assuming I get this error because I cannot set a constant to this reference or variables.

Const MONTH As String = ThisWorkbook.Names("BillMonth").RefersToRange

OR

When MsgBox is used this prompts the correct data, the data is several columns from a table titled Bill Checklist. So the ranges are

='Bill Checklist'!$F$5:$F$25,'Bill Checklist'!$I$5:$I$25, and every third column until the end of the table.

The code below removes the equal sign from the range to be used as a string variable.

Dim MONTH As String
MONTH = ThisWorkbook.Names("BillMonth").RefersTo
MONTH = Mid(MONTH, 2, Len(MONTH) - 2)

The second option does output the correct ranges its just not reading into the code below not sure why...

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(MONTH)) Is Nothing Then
For Each cell In Target
    If (Target = "Paid") Then
        cell.NumberFormat = "@"
        cell.Font.Color = RGB(0, 0, 0)
        Call OpenMainForm
        frmMain.txtWith.Value = Format(ActiveCell.Offset(-1, 1) * -1, "Standard")
    ElseIf (Target = "Due") Then
        cell.NumberFormat = "@"
        cell.Font.Color = RGB(0, 0, 0)
    ElseIf (Target = "DueX") Then
        cell.NumberFormat = "@"
        cell.Font.Color = RGB(0, 0, 0)
    ElseIf (Target = "DueN") Then
        cell.NumberFormat = "@"
        cell.Font.Color = RGB(0, 0, 0)
    Else
        cell.NumberFormat = "@"
        cell.Font.Color = RGB(255, 0, 0)
    End If
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Sub

As a constant with the ranges inputted manually as show at the top this does work, not sure what needs to be changed for it to accept either a constant set to the named range or using the MONTH variable. It might be possible that the code doesn't recognize the table references, not too familiar with using table references and named ranges in this manner.

Any advice would help and be appreciated.

 

[dhosi439]

Norie-

I was using a constant as a means to set the ranges, originally I didn't have so many. Yes it is causing problems. I also was only using MONTH for testing purposes not thinking that this is a predetermined function in VBA.

Thanks for the recommending that, just wasn't thinking.

tusharm-

You are exactly correct, I don't know what I was thinking by not using Me.Range("BillMonth"), this is much more efficient.

Thanks for the help guys.