Const mRange as String error...

[Noob101]

Hello,

I'm still at the very basics of VBA and I would really love it if someone could help me out because this stuff is so tricky!

Basically, I am learning how to add more rules using VBA (and I have a few more to go) but I'm kind of stuck because I keep getting the "Const mRange as String error" appearing in my Excel 2003 macro. Below is the code - any help would be fantastic!!

Private Sub Worksheet_Change(ByVal Target As Range)

Const mRange As String = "O21:BH450"
Dim myPlage As Range, c As Range
Set myPlage = Me.Range(mRange)
For Each c In myPlage
With c
Select Case UCase(.Value)
Case "N"
.Interior.ColorIndex = 38
Case "N/A"
.Interior.ColorIndex = 48
Case "EXEMPT"
.Interior.ColorIndex = 48
Case "Y"
.Interior.ColorIndex = 4
Case "NOMINATED"
.Interior.ColorIndex = 41
Case "ENROLLED"
.Interior.ColorIndex = 6
Case ""
.Interior.ColorIndex = 38
Case Else
.Interior.ColorIndex = xlNone
End Select
End With
Next c
Const mRange As String = "BJ21:BJ450"
Dim myPlage As Range, c As Range

Set myPlage = Me.Range(mRange)

For Each c In myPlage
With c
Select Case UCase(.Value)
Case "N/A"
.Interior.ColorIndex = 48
Case ""
.Interior.ColorIndex = 38
Case Else
.Interior.ColorIndex = xlNone
End Select
End With
Next c
End Sub

Any thoughts??

 

[Rick Rothstein]

Hello,

I'm still at the very basics of VBA and I would really love it if someone could help me out because this stuff is so tricky!

Basically, I am learning how to add more rules using VBA (and I have a few more to go) but I'm kind of stuck because I keep getting the "Const mRange as String error" appearing in my Excel 2003 macro. Below is the code - any help would be fantastic!!

Private Sub Worksheet_Change(ByVal Target As Range)

Const mRange As String = "O21:BH450"
Dim myPlage As Range, c As Range
Set myPlage = Me.Range(mRange)
For Each c In myPlage
With c
Select Case UCase(.Value)
Case "N"
.Interior.ColorIndex = 38
Case "N/A"
.Interior.ColorIndex = 48
Case "EXEMPT"
.Interior.ColorIndex = 48
Case "Y"
.Interior.ColorIndex = 4
Case "NOMINATED"
.Interior.ColorIndex = 41
Case "ENROLLED"
.Interior.ColorIndex = 6
Case ""
.Interior.ColorIndex = 38
Case Else
.Interior.ColorIndex = xlNone
End Select
End With
Next c
Const mRange As String = "BJ21:BJ450"
Dim myPlage As Range, c As Range

Set myPlage = Me.Range(mRange)

For Each c In myPlage
With c
Select Case UCase(.Value)
Case "N/A"
.Interior.ColorIndex = 48
Case ""
.Interior.ColorIndex = 38
Case Else
.Interior.ColorIndex = xlNone
End Select
End With
Next c
End Sub

Any thoughts??

The keyword Const is short for "constant"... and a constant is just that... constant. You are not allowed to change a constant once you have defined it. The line the error is occurring on is your attempt to assign a new value to the constant mRange that you defined at the top of your code. If you need to change the value assigned to it during your code, then do not make it a constant... just Dim it a normal variable of type String.

 

[Noob101]

Hi Rick!

Sorry for the hassle and thankyou for helping me out. I'm still new at this - could you provide an example code answer (because my boss wants this done by next week).

You guys/gals are the best!

 

[Rick Rothstein]

Hi Rick!

Sorry for the hassle and thankyou for helping me out. I'm still new at this - could you provide an example code answer (because my boss wants this done by next week).

It sounds like you might need to step back and learn how to program VBA before you go on, or you will end up in trouble with your boss as he is going to assume you know how program at a level you are not ready for. The change I mentioned you should do is quite basic (no pun intended) and should not have been difficult for you to implement. I'll change your code, but you really need to understand what it is that I did in case you are ever asked to modify this code in the future. Verbally, what I have done is remove both of your Const statements and replaced them with simple assignment statements (shown in red) and I also declared mRange as a variable of type String in your existing Dim statement (shown in blue). I also remove this second Dim statement...

Dim myPlage As Range, c As Range

because you already had this same line at the top of your code (you can only Dim a variable once within any one code procedure). I have also indented your code because that makes it much more readable (you may have actually done that with your own code, but you did not use code tags around your code, so this forum's comment processor left justified all of the text in your code).

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim myPlage As Range, c As Range, mRange As String

  mRange = "O21:BH450"
  Set myPlage = Me.Range(mRange)
  For Each c In myPlage
    With c
      Select Case UCase(.Value)
        Case "N"
          .Interior.ColorIndex = 38
        Case "N/A"
          .Interior.ColorIndex = 48
        Case "EXEMPT"
          .Interior.ColorIndex = 48
        Case "Y"
          .Interior.ColorIndex = 4
        Case "NOMINATED"
          .Interior.ColorIndex = 41
        Case "ENROLLED"
          .Interior.ColorIndex = 6
        Case ""
          .Interior.ColorIndex = 38
        Case Else
          .Interior.ColorIndex = xlNone
        End Select
    End With
  Next c
  
  mRange = "BJ21:BJ450"
  Set myPlage = Me.Range(mRange)
  For Each c In myPlage
    With c
    Select Case UCase(.Value)
      Case "N/A"
        .Interior.ColorIndex = 48
      Case ""
        .Interior.ColorIndex = 38
      Case Else
        .Interior.ColorIndex = xlNone
    End Select
    End With
  Next c
End Sub