Error 'Constant Expression Required' For Constant (1 Viewer)

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:28
Joined
Oct 17, 2012
Messages
3,276
Okay, this is a weird one.

I have a procedure that is somewhat long. In a nutshell, it takes 2 required and 7 optional parameters, validates an excel file and worksheet name, does a number of different things based on whether or not the file or sheet already exists and what the calling code wants done in those cases, then finally generates an Excel file.

It works fine, no issue, compiles and runs perfectly well.

Part of that procedure is a constant. I define it at the beginning with
Code:
 Const PH = "E2EPLACEHOLDER"
It is referenced in a couple of places during worksheet creation.

Again, no issues, everything compiles and works fine.

Now, I'm looking to add three more optional parameters. What I'm running into is that as soon as I make the slightest change to the parameter list, the code no longer compiles. All references to PH now generate the error 'Constant expression required', and right-clicking PH and selecting 'Definition' results in the error 'Identifier under cursor not recognized'. Note that none of the new parameters are called PH, and changing the name of the constant and the references to it doesn't change anything.

Does anyone have any suggestions for this one? It makes absolutely no sense.

I can provide the full code if necessary - nothing in it is proprietary, and it's based off something I posted here a year or two back.

I've already replaced the code with code from a backup, replaced the entire module, compacted and repaired the pre-modification file, decompiled and recompiled before making the change, decompiled and recompiled AFTER making the change, and done a full object pull into a blank database.
 

Cronk

Registered User.
Local time
Tomorrow, 02:28
Joined
Jul 4, 2013
Messages
2,770
Bit of corruption somewhere? Tried importing everything into a blank accdb?


Post your code. I'm sure any number of us would try to emulate your problem.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:28
Joined
Feb 28, 2001
Messages
26,999
I don't see anything obvious in that statement, but I would wonder about the other constant declarations.

I'm with Cronk - post the thing you tried that didn't work and gave you that error.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:28
Joined
Oct 17, 2012
Messages
3,276
Bit of corruption somewhere? Tried importing everything into a blank accdb?


Post your code. I'm sure any number of us would try to emulate your problem.

Yep, I've already listed the steps I've taken at the end of my post, and I did try to import everything into a blank DB. :(
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:28
Joined
Oct 17, 2012
Messages
3,276
I don't see anything obvious in that statement, but I would wonder about the other constant declarations.

I'm with Cronk - post the thing you tried that didn't work and gave you that error.

That's actually the only constant declaration in the procedure.

I'll post the code next, but please be aware it's going to be an entire module, and if you download and run it, you'll need to do some cutting - at the very least, the error handling won't compile, since it uses a custom class.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:28
Joined
Oct 17, 2012
Messages
3,276
Okay, here's the full code. While you guys chew on that, I'm going back to my old troubleshooter days and rebooting, then trying my steps all over again.

OH One note: this version includes the additional parameter. The code compiled just fine until I added the ShowFile parameter. Same with LeaveVisible instead of ShowFile.


Code:
Option Compare Database
Option Explicit
'Custom Type used in ExportToExcel.
Private Type NameCheckResults
    ResultMsg As String
    IsDupe As Boolean
End Type
Public Function ExportToExcel(ByRef rs As DAO.Recordset, _
                              ByVal OutputPath As String, _
                              Optional ByVal TopLeft As String = "A1", _
                              Optional ByRef SheetName As String = "Sheet1", _
                              Optional ByVal ReplaceExisting As Boolean = False, _
                              Optional ByVal AddSheet As Boolean = False, _
                              Optional ByVal IncludeColumnNames As Boolean = False, _
                              Optional ByVal AutoFitData As Boolean = True, _
                              Optional ByVal AppendData As Boolean = False, _
                              Optional ByVal ShowFile As Boolean = False) As Integer
'*************************************************
'Version:               1.1.5
'Created By:            Scott L Prince
'Date Created:          6/8/2016
'Revised By:            Scott L Prince
'Date Revised:          11/30/2016
'Purpose:               Exports a recordset to Excel.
'Parameters:            An open recordset containing the data to be exported
'                       A string containing the full path for the file to be appended or created
'                       Optional - The location of the top-left cell for the exported dataset.  Defaults to "A1".
'                       Optional - The name of the created worksheet.  Defaults to 'Sheet 1'.
'                       Optional - Whether or not to replace any existing workbook of the same name and location.  Defaults to FALSE.
'                       Optional - Whether or not to add the recordset as a new worksheet if the destination workbook already exists.  Defaults to FALSE.
'                       Optional - Whether or not to append the data to an existing sheet.  !!!THIS CAN OVERWRITE EXISTING DATA!!!  Defaults to FALSE.
'                                  AppendData overrides ReplaceExisting and AddSheet, and should only be used when adding multiple recordsets to one worksheet.
'Returns:               Success/Failure Code:
'                       0   - Uncaught/unhandled exception
'                       1   - Output file already exists, user chose to cancel rather than replace the file
'                       2   - Output sheet already exists in output file and user chose to cancel
'                       3   - Output file already exists and is locked
'                       999 - Successful save
'Dependencies:          Requires CheckFileLock procedure from modFileUtilities
'                       Requires FileExists procedure from modFileUtilities
'Comments:              If additional option types or option locations are added, ensure that this module is updated to reflect this.
'                       This procedure saves the spreadsheet as the default for whichever version of Excel is being used
'                           Excel 97 to 2003:       .xls
'                           Excel 2007 or later:    .xlsx
'*************************************************
'Edited By:             Scott L Prince
'Edit Date:             10/19/2016
'Description:           Revised to add checks for valid worksheet names.
'*************************************************
'Edited By:             Scott L Prince
'Edit Date:             10/27/2016
'Description:           Revised to clean up logic errors and remove default sheets from new workbooks.
'*************************************************
'Edited By:             Scott L Prince
'Edit Date:             11/01/2016
'Description:           Revised to allow data to be appended to existing worksheets.
'*************************************************
'Edited By:             Scott L Prince
'Edit Date:             11/30/2016
'Description:           Added explicit sheet selection to avoid an assortment of Excel errors arising from modifying non-selected sheet.
'*************************************************
'Edited By:             Scott L Prince
'Edit Date:             08/16/2017
'Description:           Changed SheetName parameter to ByRef to account for the possibiity of changing the worksheet name mid-procedure.
'*************************************************
On Error GoTo E2E_Err
'Dim wb As Excel.Workbook
'Dim ws As Excel.Worksheet
'Dim xl As Excel.Application
Dim wb As Object
Dim ws As Object
Dim xl As Object
Dim NameCheck As NameCheckResults
Dim PH As String
    PH = "E2EPlaceholder"
    
'Const PH = "E2EPlaceholder"             'Used as the name of a placeholder worksheet name.  If changed, ensure it is one that will never actually be used.
    'Defaults
    If Nz(SheetName, "") = "" Then SheetName = "Sheet1"
    
    'Determine whether or not target workbook already exists.
    If FileExists(OutputPath) Then
        'File exists.  Check to see if ReplaceExisting is set to TRUE.
        If Not ReplaceExisting And Not AppendData Then
            'ReplaceExisting is not set to true - check AddSheet (if RE is FALSE and AS is TRUE, there is no reason to query the user.)
            If Not AddSheet Then
                'Ask the user if they want to replace the existing file.
                Select Case MsgBox("File already exists.  Do you want to replace it?", vbYesNo + vbCritical + vbDefaultButton2, PROJECT_NAME)
                    Case vbYes
                        'User okayed replacing the file, so change ReplaceExisting to TRUE.
                        ReplaceExisting = True
                    Case vbNo
                        'User does not want to replace the file.  Determine if they want to add the data to the existing file in a new worksheet.
                        If MsgBox("Do you wish to add a worksheet with this data to the existing file?", vbYesNoCancel + vbInformation + vbDefaultButton3, PROJECT_NAME) = vbYes Then
                            'Change the AddSheet flag to TRUE
                            AddSheet = True
                        Else
                            'User chose to neither kill the existing file nor add a sheet to it.  Return result code and terminate processing.
                            ExportToExcel = 1
                            Err.Raise 500
                        End If
                    Case Else
                        'User either closed the message box or selected 'Cancel'.  Return result code and terminate processing.
                        ExportToExcel = 1
                        Err.Raise 500
                End Select
            End If
        End If
        
        'Determine if the destination file is locked.
        If CheckFileLock(OutputPath) Then
            'File is in use.  Return error code and terminate processing.
            ExportToExcel = 3
            Err.Raise 500
        End If
        
        'Open Excel
        Set xl = CreateObject("Excel.Application")
        
        'File is not locked.  Check value of ReplaceExisting.
        If ReplaceExisting Then
            'File is to be replaced.  Delete the existing copy.
            Kill OutputPath
            'Create the output workbook.
            Set wb = xl.Workbooks.Add
            'Create a placeholder sheet.
            Set ws = wb.Sheets.Add
            ws.Name = PH
            Set ws = Nothing
            'Delete all other sheets.
            For Each ws In wb.Sheets
                If ws.Name <> PH Then ws.Delete
            Next ws
            'Save the workbook.
            wb.SaveAs OutputPath
        Else
            'File is not to be replaced, and AddSheet is TRUE.  (FALSE results would be aborted above.)  Open the existing file.
            Set wb = xl.Workbooks.Open(OutputPath)
        End If
        
        'Verify that the provided sheet name, if any, is valid.
        Do
            NameCheck = ValidateWSName(wb, SheetName)
            If NameCheck.ResultMsg <> "" Then
                SheetName = InputBox(NameCheck.ResultMsg & vbCrLf & vbCrLf & "Please enter a valid worksheet name.  Leave blank to cancel.", PROJECT_NAME, "Sheet1")
                If SheetName = "" Then
                    ExportToExcel = 2
                    Err.Raise 500
                End If
            End If
        Loop Until NameCheck.ResultMsg = ""
        
        If NameCheck.IsDupe And Not AppendData Then
            Select Case MsgBox("The worksheet '" & SheetName & "' already exists.  Do you wish to replace it?", vbCritical + vbYesNoCancel + vbDefaultButton3, PROJECT_NAME)
                Case vbYes
                    'Delete the existing sheet and set IsDupe to false.
                    xl.DisplayAlerts = False
                    wb.Sheets(SheetName).Delete
                    xl.DisplayAlerts = True
                    NameCheck.IsDupe = False
                Case vbNo
                    'Verify the worksheet name.
                    Do Until NameCheck.ResultMsg = "" And Not NameCheck.IsDupe
                        'Ask the user for a new worksheet name.
                        If NameCheck.IsDupe Then
                            SheetName = InputBox("The worksheet name you provided already exists.  Please provide a different worksheet name.  Leave blank to cancel.", _
                                                 PROJECT_NAME, "Sheet1")
                        Else
                            SheetName = InputBox(IIf(Nz(NameCheck.ResultMsg, "") = "", "", NameCheck.ResultMsg & vbCrLf & vbCrLf) & _
                                                 "Please enter a valid worksheet name.  Leave blank to cancel.", PROJECT_NAME, "Sheet1")
                        End If
                        
                        If SheetName = "" Then
                            ExportToExcel = 2
                            Err.Raise 500
                        End If
                        
                        NameCheck = ValidateWSName(wb, SheetName)
                    Loop
                Case vbCancel
                    ExportToExcel = 2
                    Err.Raise 500
            End Select
        End If
                
        'If NameCheck.IsDupe is false, create a worksheet after all existing ones.  (Will only be true if AppendData is true.)
        If Not NameCheck.IsDupe Then
            Set ws = wb.Worksheets.Add(, wb.Worksheets(wb.Worksheets.Count))
            ws.Name = SheetName
        End If
        
        'Delete the sheet E2EPlaceholder if it exists.
        If Not ws Is Nothing Then Set ws = Nothing
        For Each ws In wb.Sheets
            If ws.Name = PH Then ws.Delete
        Next ws
        'Re-select the new sheet.
        Set ws = wb.Worksheets(SheetName)
        'Save the workbook.
        wb.Save
    Else
        'Output file doesn't already exist, so create it.
        'Open Excel.
        Set xl = CreateObject("Excel.Application")
        'Create the output workbook.
        Set wb = xl.Workbooks.Add
        'Delete all sheets save the first one.
        For Each ws In wb.Sheets
            If ws.Index <> 1 Then ws.Delete
        Next ws
        'Select the only remaining worksheet in the workbook.
        Set ws = wb.Sheets(1)
        'Verify that the provided sheetname is valid.
        If ws.Name <> SheetName Then
            Do
                NameCheck = ValidateWSName(wb, SheetName)
                If NameCheck.ResultMsg <> "" Then
                    'Note - there is no Dupe check because there is only one worksheet remaining, and we're setting its name, so only name validity matters.
                    SheetName = InputBox(IIf(Nz(NameCheck.ResultMsg, "") = "", "", NameCheck.ResultMsg & vbCrLf & vbCrLf) & _
                                         "Please enter a valid worksheet name.  Leave blank to cancel.", PROJECT_NAME, "Sheet 1")
                    If SheetName = "" Then
                        ExportToExcel = 2
                        Err.Raise 500
                    End If
                End If
            Loop Until NameCheck.ResultMsg = ""
            'Set the name of the worksheet to SheetName.
            ws.Name = SheetName
        End If
        'Save the workbook.
        wb.SaveAs OutputPath
    End If
    Dim CurrentRow As Long
    Dim CurrentColumn As Long
    
    'Ensure that the worksheet ws is selected.
    ws.Select
    
    'Assign row and column numbers to counters.
    CurrentRow = ws.Range(TopLeft).Row              'Used to track the row of the 'pointer'.
    CurrentColumn = ws.Range(TopLeft).Column        'Used primarily to cut down the number of function calls.
    
    'Determine if column names are to be transferred.
    If IncludeColumnNames Then
        Dim ColCount As Long
        For ColCount = 0 To rs.Fields.Count - 1
            ws.Cells(CurrentRow, ColCount + 1).Value = rs.Fields(ColCount).Name
        Next ColCount
        ws.Range(xl.Cells(CurrentRow, CurrentColumn), xl.Cells(CurrentRow, CurrentColumn + rs.Fields.Count - 1)).Font.Bold = True
        CurrentRow = CurrentRow + 1
    End If
    
    'Copy the submitted recordset to the output spreadsheet.
    ws.Cells(CurrentRow, CurrentColumn).CopyFromRecordset rs
    
    'Check to see if the exported data columns should be resized.
    If AutoFitData Then
        'Resize the rows to fit.
        ws.UsedRange.Columns.AutoFit
    End If
    
    'Save the workbook.
    wb.Save
    
    'Return a 'Success' result
    ExportToExcel = 999
    
E2E_Exit:
    On Error Resume Next
    If Not ws Is Nothing Then Set ws = Nothing
    If Not wb Is Nothing Then
        wb.Save
        wb.Close
        Set wb = Nothing
    End If
    If Not xl Is Nothing Then
        xl.Quit
        Set xl = Nothing
    End If
    Exit Function
    
E2E_Err:
    Trap.Handle "basExcelUtilities", "ExportToExcel"
    Resume E2E_Exit
End Function
Public Function ValidateWSName(ByRef wb As Object, _
                               ByVal WSName As String) As NameCheckResults
On Error GoTo VWN_Err
Dim ws As Variant
Dim x As Long
Dim Msg As String
    Msg = "Please correct the following errors with the provided worksheet name:" & vbCrLf
    
    'Length check
    If Len(WSName) > 31 Then Msg = Msg & vbCrLf & "*Worksheet names may not exceed 31 characters in length."
    
    ValidateWSName.IsDupe = False
    
    'Duplicate check
    For Each ws In wb.Sheets
        If WSName = ws.Name Then
            ValidateWSName.IsDupe = True
            Exit For
        End If
    Next ws
    
    'Special character check
    If CheckSpecChars(WSName) Then Msg = Msg & vbCrLf & "The following characters may not be used in worksheet names: \/[]*:?"
    
    'Results
    If Msg <> "Please correct the following errors with the provided worksheet name:" & vbCrLf Then
        ValidateWSName.ResultMsg = Msg
    Else
        ValidateWSName.ResultMsg = ""
    End If
    
VWN_Exit:
    Exit Function
    
VWN_Err:
    Trap.Handle "basExcelUtilities", "ValidateWSName"
    Resume VWN_Exit
End Function
Private Function CheckSpecChars(ByVal SheetName As String) As Boolean
Dim SpecChars() As String
Dim x As Long
    CheckSpecChars = False
    
    SpecChars() = Split("\,/,*,[,],:,?", ",")
    
    'Special character check
    For x = LBound(SpecChars) To UBound(SpecChars)
        If InStr(1, SheetName, SpecChars(x), vbTextCompare) > 0 Then
            CheckSpecChars = True
            Exit For
        End If
    Next x
End Function
Public Function IsExcelRunning() As Boolean
Dim xlApp As Object
    
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
IsExcelRunning = (Err.Number = 0)
Set xlApp = Nothing
Err.Clear
End Function
Function GetColumnLetter(ColumnNumber As Long) As String
    Dim n As Long
    Dim c As Byte
    Dim s As String
    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    GetColumnLetter = s
End Function
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:28
Joined
Oct 17, 2012
Messages
3,276
My apologies for the spacing. AWF loves to remove blank lines from submitted code.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:28
Joined
Feb 28, 2001
Messages
26,999
OK, looking at the syntax and context, you were declaring a private constant and allowing VBA to decide the type for you. In the past, I've had issues by declaring constants after declaring anything that occupies memory. I.e. declare constants, THEN declare Dim statement items. It also helps when declaring to include an AS to show the intended data type.

I cannot tell you why, but there are certain things that have to be early in your list of declarations. ENUM and TYPE declarations, because they imply constant definitions, also have to precede anything declared to take up storage. I.e. DIM, PUBLIC, PRIVATE, etc. I think it is because the CONST items have to be stored differently than DIM items and once you start allocating storage in the VBA module, you lose the window of opportunity to perform that storage. That is an IMPRESSION and I can't find documentation for that - but that's how I have successfully declared multiple constants in a module.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:28
Joined
Oct 17, 2012
Messages
3,276
The type is declared at the very start of the module - there is literally nothing before it other than the Option statements.. The constant is local scope, and is declared almost at the very start of the procedure in which it is used. I'll try moving it to the top of the declaration list.

I actually explicitly declared the constant as a string at one point, and it did not solve the problem.

Don't forget that this worked perfectly until I added a parameter.

At this point, I've actually decided to leave it be and changed it to a string variable.

There's always a work-around. Thanks for looking into it, though!
 

JHB

Have been here a while
Local time
Today, 16:28
Joined
Jun 17, 2012
Messages
7,732
I know you've solved it in a way, so ...
What happen if you create a new database and put the code you provide here (+ what is missing) and then change the parameters, do you get a compiler error when you've PH set as a constant?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:28
Joined
Feb 28, 2001
Messages
26,999
The constant is local scope, and is declared almost at the very start of the procedure

Almost but not quite. Try this. Just humor me.

Code:
[COLOR="Red"]Private Const PH As String = "E2EPlaceholder"[/COLOR]
             'Used as the name of a placeholder worksheet name.  If changed, ensure it is one that will never actually be used.

On Error GoTo E2E_Err
'Dim wb As Excel.Workbook
'Dim ws As Excel.Worksheet
'Dim xl As Excel.Application
Dim wb As Object
Dim ws As Object
Dim xl As Object
Dim NameCheck As NameCheckResults
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:28
Joined
Oct 17, 2012
Messages
3,276
I did say I'd give it a try in the very next sentence. :p I'll let you know the results after I get a chance to check.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:28
Joined
Oct 17, 2012
Messages
3,276
As I mentioned before, adding 'As String' actually did not solve the issue.

Strangely, moving the constant up before the variable declarations did.

Freaking Access. I would love to know why in THIS case, of all the times I've ever used constants, it broke, but I doubt I ever will.

Thanks for your help!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:28
Joined
Feb 28, 2001
Messages
26,999
Yep, thought so. I cannot tell you WHY, but for some reason you MUST put all constant declarations before any variable declaration and/or any executable code lines. I've seen that before and have yet to find the explanation - but it is so for me and now you confirm it for you.

Don'tcha just LOVE MS documentation?
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:28
Joined
Oct 17, 2012
Messages
3,276
Honestly, I've been putting constants after variables for years without running into this issue, so it's less 'You MUST' than 'You should because on rare occasions Access goes insane'. Hell, it worked here just fine right up until I added that tenth parameter.

Still freaking aggravating. :p
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:28
Joined
Feb 28, 2001
Messages
26,999
I wonder if the problem is that the extra parameter "broke" something. I can't find ShowFile in the object browser but then I might not have the references you do. See if Object Browser finds "ShowFile" somewhere other than in your project.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:28
Joined
Oct 17, 2012
Messages
3,276
It didn't, that was one of the things I checked. It didn't actually matter WHAT the parameter was called - I was going to add two optional ByVal string parameters 'ShowFile' and 'LeaveOpen', and move the ws reference up into the parameters as an optional ByRef. Any of them resulted in the same error, as did the parameter 'BillyJoeBob'. Somehow, it was the act of going from 9 to 10 parameters.

One odd thing was that if I imported JUST that module and the classes it refers to and nothing else, it compiled just fine. Moving the rest of the application into the new file, however, brought the error back.

Unfortunately, I don't really have the time to really dig down into what caused it. Since we fixed the issue by moving the constant declaration up, I had to mark it fixed and move on.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:28
Joined
Feb 28, 2001
Messages
26,999
Understood. Sometimes you have to shrug and walk away. And file the event under "yet another day programming in the Twilight Zone."
 

The Boojum

New member
Local time
Today, 15:28
Joined
Jan 22, 2019
Messages
1
Thanks to you both. I've had the same problem with Excel VBA was was tearing my hair out.

As you say, weird!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:28
Joined
Feb 28, 2001
Messages
26,999
Not surprised you had the same problem, The Boojum.

In another thread we discovered that VBA is actually some sort of shared module library and that ANY Office element that uses VBA uses the SAME copy of VBA. We found this out when a formatting change was made for Access VBA code and the formatting change showed up in an Excel VBA module. Can't recall the exact thread because it has been at least several months, 10 or more I think, but apparently there IS only one VBA. So that means that bugs in ONE utility's VBA will likely show up in other utilities' VBAs.

What's worse is that they frequently show the same error across multiple utilities but that just makes it harder to figure out what the error means. When you get "Application-defined or object-defined error" you are pretty much screwed because there is almost no way to correctly trace that down. It's the ultimate "catch-all" error.

It's the moral equivalent of that moment in the movie Galaxy Quest where Tech Sergeant Chen and Lilana slide off screen in a passionate embrace, tentacles briefly flash on screen, and Guy Fleegman says "Aw, that ain't right."
 

Users who are viewing this thread

Top Bottom