Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-10-2018, 10:02 AM   #1
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Error 'Constant Expression Required' For Constant

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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 10-10-2018, 10:52 AM   #2
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,137
Thanks: 3
Thanked 466 Times in 459 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Error 'Constant Expression Required' For Constant

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.
Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
Frothingslosh (10-11-2018)
Old 10-10-2018, 11:05 AM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,382
Thanks: 87
Thanked 1,648 Times in 1,530 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Error 'Constant Expression Required' For Constant

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-10-2018, 11:16 AM   #4
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Error 'Constant Expression Required' For Constant

Quote:
Originally Posted by Cronk View Post
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 10-10-2018, 11:18 AM   #5
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Error 'Constant Expression Required' For Constant

Quote:
Originally Posted by The_Doc_Man View Post
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 10-10-2018, 11:20 AM   #6
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Error 'Constant Expression Required' For Constant

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
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 10-10-2018, 11:22 AM   #7
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Error 'Constant Expression Required' For Constant

My apologies for the spacing. AWF loves to remove blank lines from submitted code.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 10-10-2018, 01:33 PM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,382
Thanks: 87
Thanked 1,648 Times in 1,530 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Error 'Constant Expression Required' For Constant

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Frothingslosh (10-10-2018)
Old 10-10-2018, 02:21 PM   #9
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Error 'Constant Expression Required' For Constant

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!
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 10-10-2018, 04:06 PM   #10
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,081 Times in 2,036 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Error 'Constant Expression Required' For Constant

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?
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
Frothingslosh (10-11-2018)
Old 10-10-2018, 06:37 PM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,382
Thanks: 87
Thanked 1,648 Times in 1,530 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Error 'Constant Expression Required' For Constant

Quote:
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:
Private Const PH As String = "E2EPlaceholder"
             '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
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-11-2018, 03:46 AM   #12
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Error 'Constant Expression Required' For Constant

I did say I'd give it a try in the very next sentence. I'll let you know the results after I get a chance to check.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 10-11-2018, 05:15 AM   #13
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Error 'Constant Expression Required' For Constant

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!
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
The Following User Says Thank You to Frothingslosh For This Useful Post:
The Boojum (01-22-2019)
Old 10-11-2018, 05:18 AM   #14
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,382
Thanks: 87
Thanked 1,648 Times in 1,530 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Error 'Constant Expression Required' For Constant

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?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
The Boojum (01-22-2019)
Old 10-11-2018, 06:19 AM   #15
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Error 'Constant Expression Required' For Constant

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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Constant Connection To B.e Shaunk23 Modules & VBA 4 05-30-2012 07:37 AM
Combo plus constant CEH Forms 6 10-12-2007 12:49 PM
Constant Variables Moonshine Modules & VBA 3 10-04-2005 04:24 AM
Can't set Constant Meltdown Modules & VBA 5 02-09-2005 04:20 PM
Declare a constant (Field / Expression?) to be used in a query? Cosmos75 Queries 7 09-17-2002 09:31 AM




All times are GMT -8. The time now is 12:09 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World