Solved Same code does not work on another machine (1 Viewer)

[frederik]

Hi,

I’m having an issue with my application since I use it on another laptop.
It’s an application in Access that creates Excel-files (export data and do some formatting).
The program works well with Access 2019 (desktop) and Office/Access 365 (all 64-bit) (old laptop).

My new laptop has also Office/Access 365 (64 bit), but it generates an error specifically on this part of code:

objExcel.Worksheets(n).Range(bereik_data).FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(LEN($A5)>0;MOD($M5;2)=0)"

The error it generates is a run-time error ‘5’: Invalid procedure call or argument.

These are the references that are enabled:

• Visual Basic For Applications
• Microsoft Access 16.0 Object Library
• Microsoft ActiveX Data Objects 2.1 Library
• OLE Automation
• Microsoft Excel 16.0 Library
• Microsoft DAO 3.6 Object Library
• Microsoft Outlook 16.0 Object Library
• Microsoft Office 16.0 Object Library

To give more context, this is the block of code where this line is situated:

'-----  formatting alternating color -----
                        objExcel.Worksheets(n).Cells(3, 13) = 0
                        objExcel.Worksheets(n).Cells(5, 13).FormulaR1C1 = "=IF(RC[-12]=R[-1]C[-12],R[-1]C,R[-1]C+1)"
                        
                        bereik_parameter = "M5:M" & aantalRecords + 4
                        objExcel.Worksheets(n).Cells(5, 13).AutoFill Destination:=objExcel.Worksheets(n).Range(bereik_parameter)
                        
                        bereik_data = "A5:L" & aantalRecords + 4
                        objExcel.Worksheets(n).Range(bereik_data).FormatConditions.Add Type:=xlExpression, Formula1:= _
                            "=AND(LEN($A5)>0;MOD($M5;2)=0)"
                        objExcel.Worksheets(n).Range(bereik_data).FormatConditions(objExcel.Worksheets(n).Range(bereik_data).FormatConditions.Count).SetFirstPriority
                        
                        With objExcel.Worksheets(n).Range(bereik_data).FormatConditions(1).Interior
                            .PatternColorIndex = xlAutomatic
                            .ThemeColor = xlThemeColorLight2
                            .TintAndShade = 0.799981688894314
                        End With
                        objExcel.Worksheets(n).Range(bereik_data).FormatConditions(1).StopIfTrue = True
                        objExcel.Worksheets(n).Columns("M:M").EntireColumn.Hidden = True
                      
                        
'--------------------------------------

I don’t understand why this exact same code does work on one machine and not on another.
I thought it had something to do with early/late binding, but all references are the same ....

At this point, I don’t know what else I can check.
Anybody any advice?

 

[arnelgp]

"=AND(LEN($A5)>0;MOD($M5;2)=0)"

you have Semicolon ([;] on your formula (unless your separator is really a ";")

 

[frederik]

Thank you Arnelgp.

I've changed ; to , and indeed, it's working now.


Where I live (Belgium), ";" is the default separator when Belgian regional settings are applied in Windows. That's why it worked on my previous desktop / laptop.


On my new laptop, other regional settings (British) were applied (where , is the default separator) and I didn't notice that.

 

[arnelgp]

good to know that it worked for you, goodluck