CODEWITHSUNDEEP
excelvba
VBasic2008
VBasic2008

Reputation: 54807

Sub With Arguments Produces Error (Compile error: Constant expression required)

I wrote this script which loops through a column and if it finds a value in it and a value in another column, changes the value of the other column. 

Sub RepChange()

    Const Company As String = "Kellog's"
    Const Representer As String = "Elite"
    Const NewRepresenter As String = "Core"

    Const cSheet As Variant = "Sheet1"
    Const cColRep As Variant = "H"
    Const cColComp As Variant = "I"
    Const cFR As Long = 2

    Dim LR As Long
    Dim i As Long

    With ThisWorkbook.Worksheets(cSheet)
        LR = .Columns(cColComp).Cells(.Rows.Count).End(xlUp).Row
        For i = cFR To LR
            If StrComp(.Cells(i, cColComp), Company, vbTextCompare) = 0 And _
                    StrComp(.Cells(i, cColRep), Representer, vbTextCompare) _
                    = 0 Then .Cells(i, cColRep) = NewRepresenter
        Next
    End With

    MsgBox "Operation finished successfully.", vbInformation, "Success"

End Sub

It works fine. Now I wanted to use the first three constants as arguments of the Sub:

Sub RepChange2(Company As String, Representer As String, _
        NewRepresenter As String)

    Const cSheet As Variant = "Sheet1"
    Const cColRep As Variant = "H"
    Const cColComp As Variant = "I"
    Const cFR As Long = 2

    Dim LR As Long
    Dim i As Long

    With ThisWorkbook.Worksheets(cSheet)
        LR = .Columns(cColComp).Cells(.Rows.Count).End(xlUp).Row
        For i = cFR To LR
            If StrComp(.Cells(i, cColComp), Company, vbTextCompare) = 0 And _
                    StrComp(.Cells(i, cColRep), Representer, vbTextCompare) _
                    = 0 Then .Cells(i, cColRep) = NewRepresenter
        Next
    End With

End Sub

so I could use the following:

Sub Kellogs()
    RepChange2 "Kellog's", "Elite", "Core"
End Sub

but it produces the following error:

Compile error: Constant expression required

in the line

With ThisWorkbook.Worksheets(cSheet)

where cSheet is grayed out. If I replace it with "Sheet1", another error occurs in the next line:

LR = .Columns(cColComp).Cells(.Rows.Count).End(xlUp).Row

where cColComp is grayed out etc. Changing the Variants to Strings doesn't help.

Would appreciate any help.

Upvotes: -1

Views: 115

Answers (2)

Christian
Christian

Reputation: 11

I just had the same issue: I defined constants at the beginning of a function and used these constants inside said function. I was getting the same error message like the OP.

To resolve the issue, I simply cut the function from the module and pasted it into another module. I immediately was able to run the function as intended without any "constant req'd" errors. Finally, I moved the function back into the original module (cut and paste). It still works.

Luckily, I didn't have to create a new workbook. My workbook is full with worksheet designs, various named cells and so on, I would have been a nightmare to reproduce everything from scratch in a new workbook.

Upvotes: 1

BigBen
BigBen

Reputation: 50008

I was unable to reproduce this error. The error description does not match the code you've provided; you clearly have Constants.

Perhaps create a new workbook and copy this code into it, that's the only thing I can think of.

Upvotes: 1

Related Questions