"constant expression required" error on function declaration line

Question

I have a function written in Visual Basic, which is supposed to read in two Excel workbooks and to some textual analysis on them.

The issue that I am getting is that whenever I try to run the function in a cell in my sheet, I get the error "constant expression required" in VBA and the "#VALUE!" error in the cell.

My function line looks like

Function CriticalPercentage() As Double

and I call the function by typing "=CriticalPercentage()" in the cell.

When I click enter after typing my function call in the cell it goes to VBA, shows the error message, and Function CriticalPercentage() As Double is highlighted in yellow. It also highlights in blue the .Count in the very last line of the following code that I have provided from my function.

Function CriticalPercentage() As Double

    Debug.Print "the first line works fine"

   ByVal WorkbookName1 As String, ByVal WorkbookName2 As String

' this section accesses the excel files and sheets the script will be working with

    WorkbookName1 = "name of excel file"
    WorkbookName2 = "name of excel file"

    Workbooks.Open WorkbookName1
    Workbooks.Open WorkbookName2

    ' define variables for the the two workbooks
    Dim Workbook1, Workbook2 As Excel.Workbook
    Dim Worksheet1, Worksheet2 As Excel.Worksheet

    ' create workbook objects to work with
    Set Workbook1 = Excel.Workbooks(WorkbookName1)
    Set Workbook2 = Excel.Workbooks(WorkbookName2)

    ' sets worksheet variables to the sheets contained in the two workbooks, respectively
    Set Worksheet1 = Workbook1.Sheets(1).Select
    Set Worksheet2 = Workbook2.Sheets(1).Select

'---------------------------------------------------------------------------------------

' this section of code works on reading in all the neccessary info from the ARCHER excel file

    ' search for [Application Name & Finding] 
    Dim i, j, k As Integer
    Dim numRows As Integer
    Const numRows = Worksheet1.UsedRange.Rows.Count

I have tried putting a Debug.Print statement right after Function CriticalPercentage() As Double but it does not show up in the immediate window, so I am led to believe that I am doing something wrong in the declaheration of the function.

I would appreciate any help on this and any insight as to why I am getting this error. if further code needs to be provided I will gladly do so.

Thanks so much!

You can't declare a constant like that. You already have Dim numRows As Integer (btw that really should be As Long). Get rid of the Const. — BigBen

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2019-06-21T20:53:53

The problem isn't with the function's signature, the problem is with this instruction:

Const numRows = Worksheet1.UsedRange.Rows.Count

Dim and Const statements aren't executable, they're declarative statements - you can't place a breakpoint on them: when the compiler sees a Const declaration when it processes the VBA code, it evaluates the value expression in static context, at compile-time and "burns" the value into all call sites.

Const foo = 42
Debug.Print foo

Once compiled is essentially:

Debug.Print 42

At compile time, Worksheet1.UsedRange.Rows.Count isn't a constant expression: it requires objects that won't exist until run-time, like the UsedRange reference.

So long story short, use Dim for variables, and keep Const for things that are constant - like rgbRed = &HFF0000, or Pi = 3.14159, i.e. things that are always going to have the same value, regardless of how or when you look at it. If it's not constant, it shouldn't be a Const.

"constant expression required" error on function declaration line

1 Answers