Asked  7 Months ago    Answers:  5   Viewed   73 times

do I understand correctly, that if I use a command like

Set myRange.formula = “=ROW(mySheet!R12)” 

my macro will cause #NAME? error appear in cells if it is run on, say, Russian Excel. I mean that in this case the above formula should be hard-coded like

Set myRange.formula = “=??????(mySheet!R12)”

where ?????? is the Russian analogue of the SUM function. I wouldn't anticipate Excel to be smart enough to translate the formulas in run-time. So is there any way around this and, most importantly, what is the most generic code to make the macro work correctly irrespective of languange ?

 Answers

75

VBA is very EN-US-centric. VBA's .Formula and .FormulaR1C1 expect the ROW function. To use regional language function 'flavors' like ?????? then the Range.FormulaLocal property or Range.FormulaR1C1Local property should be employed instead.

The same holds true for list separator characters. Use a comma (e.g. ,) to separate the arguments in a function when using .Formula or .FormulaR1C1 regardless of system regional settings. If your system uses a semi-colon (e.g. ;) as the list separator character, this should only be used with .FormulaLocal or .FormulaR1C1Local.

The result on the worksheet will properly reflect the language settings of the Office installation.

myRange.Formula = "=ROW(mySheet!$12:$12)"
myRange.FormulaR1C1 = "=ROW(mySheet!R12)"
myRange.FormulaLocal  = "=??????(mySheet!$12:$12)"
myRange.FormulaR1C1Local= "=??????(mySheet!R12)"
Tuesday, June 1, 2021
 
Pradip
answered 7 Months ago
59

I have found what seems to be the solution:

Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        '.PrintArea = Worksheets(ReportWsName).UsedRange
        .FitToPagesWide = 1
        '.FitToPagesTall = 1
    End With
Application.PrintCommunication = True

I needed to add the Application.PrintCommunication part to the equation. For whatever reason Excel would overwrite the settings I was putting if I ran the code without it.

Friday, July 30, 2021
 
hillz
answered 4 Months ago
44

Replace the semicolons with commas:

Formula = "=IFERROR(VLOOKUP(Q" & j & ",Table1[#All],2,FALSE),"""")"

OpenOffice uses semicolons to separate function parameters, Excel normally uses commas, and always uses commas when setting formulas in the above fashion.

Friday, July 30, 2021
 
MasterJoe
answered 4 Months ago
61

You are using a user defined function (UDF) ConcatRelated, so the UDF runs for each record, otherwise, usually Access SQL works in the normal way.

Wednesday, August 18, 2021
 
Bere
answered 4 Months ago
42

As you already noted in your comments, this is a regional setting - as your system is using , as the decimal separator, the string gets converted to 1200. The solution in this case is to convert it with Val:

Sub Sample()
    Dim myDouble As Double
    Dim stringDouble As String
    stringDouble = "12.00"
    myDouble = Val(stringDouble)
End Sub
Wednesday, October 20, 2021
 
Kyle
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share