Excel VBA function works in US but not in France-CodePudding

The following custom function works for employees in the US, but when employees in France use it, they get a #VALUE! error that says a value in the formula is of the wrong data type. This shouldn't even be possible because the function accepts variants.

FYI the purpose of the function is to round a value to an appropriate number of significant figures as per our business rules - the built-in ROUND function does not do this correctly for us.

Function ROUNDSF(num As Variant, sigs As Variant) As String
    Dim exponent As Integer
    Dim decplace As Integer
    Dim fmt_left As String
    Dim fmt_right As String
    Dim numround As Double
    If IsNumeric(num) And IsNumeric(sigs) Then
        If sigs < 1 Then
            ' Return the   "  #NUM  "   error
            ROUNDSF = CVErr(xlErrNum)
        Else
            numround = WorksheetFunction.Text(num, "." & _
                        String(sigs, "0") & "E 000")
            If num = 0 Then
                exponent = 0
            Else
                'Round is needed to fix a ?truncation?
                'problem when num = 10, 100, 1000, etc.
                exponent = Round(Int(Log(Abs(numround)) / Log(10)), 1)
            End If
            decplace = (sigs - (1   exponent))
            If decplace > 0 Then
                fmt_right = String(decplace, "0")
                fmt_left = "0."
            Else
                fmt_right = ""
                fmt_left = "0"
            End If
            ROUNDSF = WorksheetFunction.Text(numround, _
                      fmt_left & fmt_right)
        End If
    Else
        ' Return the   "  #N/A  "   error
        ROUNDSF = CVErr(xlErrNA)
    End If
End Function

I figured it would be a problem related to local settings, where in the US we separate decimals with a period (1.5) and in France they separate with a comma (1,5). However, in France they have switched their Excel settings to go with US rules for that. I also see that while in the US we separate arguments with a comma ROUNDSF(arg1, arg2) - in France they are separated with a semicolon ROUNDSF(arg1; arg2), however, Excel automatically translates those commas to semicolons when the French open the very same workbook as the Americans. I suspect there's SOMETHING like this sort of local setting that is causing this issue, but without flying to France and working on a French local machine, it would be very difficult for me to debug this.

CodePudding user response：

In Excel Options>Advanced, you can change your separators while testing.

Here, I've switched my settings (I'm in the US) to be those used in France:

Your function seems to work for me, so I wonder if the #VALUE! error they're getting is when they are trying to do some calculations using the result. Because there's a period in there instead of a comma, it will be a problem. Also, because the result is text, functions like SUM, AVERAGE, MIN and so on, won't produce correct results.

The formula for rounding to significant figures is:

=ROUND(value,figures-(1 INT(LOG10(ABS(value)))))

So you might find it more convenient to save that as a LAMBDA function in the Name Manager as I've done here and train the users to use cell formats to add any leading or trailing zeroes.

CodePudding user response：

The problem is that their system settings still use commas for decimals rather than periods. So, even though Excel is displaying periods because they changed their settings, the calculations in the background are done with commas. So, the problem is in the VBA where there are hard-coded periods such as:

numround = WorksheetFunction.Text(num, "." & _

and

fmt_left = "0."

Either changing these to commas, or changing system settings to American English vs European English solves the problem.

Thanks to Rory in the comment above for giving me the idea.