CODEWITHSUNDEEP
vbaexceldoubleregional-settings
Kaushik
Kaushik

Reputation: 23

Run Time and Compatibility Errors using Variable Type "Double"

I have a strange issue using Double type variables in my VBA Macros.

The Macro works fine when I test it on some systems, but causes a runtime error '1004' when tested on another system with different language settings. Both systems use Office 2013.

I have a ScrollBar in a form, with values ranging from -50 to + 50. Basically an indicator to increase/decrease the value of a range by -/+ 50% The Macro is something simple like follows:

Dim multiplier As Double

multiplier = CLng(Me.DPScroll.Value) / 100 + 1
ThisWorkbook.ActiveSheet.Range("AB11:AB" & LR).Formula = "=H11*" & multiplier

When the DPScroll.Value = 0 (Multiplier = 1), the form runs file without any errors, but if it has a positive or negative value, it returns

Run-time Error 1004 : Application Defined/Object Defined Error

(On my system, both work fine)

So, I'm guessing the Multiplier is unable to take values in Decimal Points (Eg. 1.07 to increase by 7%)

(Less important issue) It also causes a strange formatting error on another sheet. I'm calculating the average of a few rows in a list box and pasting the value to a range. On some systems, the format is retained, but on others it changes and gets multiplied by millions and a Large number is displayed (like 10^E6 times)

Since this is restricted to some systems - are there any regional/language compatablity issues with suing "Double" variable type.

Upvotes: 2

Views: 147

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

It is indeed a localization issue. As long as you use .Formula the regional settings are always standard US that means the multiplier has to be 1.07 (e.g. not 1,07).

But if the localization is German for example then if the multiplier is cast to a string it becomes 1,07 but .Formula needs the US standard 1.07.

Solution 1
Replace any comma , in multiplier to . using replace()

Solution 2
use .FormulaLocal to use the localized formula:

ThisWorkbook.ActiveSheet.Range("AB11:AB" & LR).FormulaLocal= "=H11*" & multiplier

But then you probably have to deal with other issues like e.g. =IF(A1=0,TRUE,FALSE) becomes localized too (e.g. for german): =WENN(A1=0;WAHR;FALSCH).

Solution 3
Another approach would be to write the multiplier into a named range, so you can use that name in your formula directly as following:

ThisWorkbook.ActiveSheet.Range("AB11:AB" & LR).Formula = "=H11*multiplier"

Therefore you can use ActiveWorkbook.Names.Add "multiplier", multiplier so you even don't need a helper cell for that named range.

But then if you change the multiplier any time, it also changes for every old formula in your sheets where the named range multiplier was used.

Upvotes: 2

Related Questions