Access Equivalent to this Excel Significant Figure VBA Function

[mccrimmon]

Is anyone able to help with the below:

I need the equivalent Access Function for the below Excel Function

Public Function SignificantFigureCalculation(dblValue, intSigFigure)
    If dblValue <> 0 Then
        SignificantFigureCalculation = Application.Evaluate("ROUND(" & dblValue & ",-(INT(LOG(ABS(" & dblValue & "))+1-" & intSigFigure & ")))")
    End If
End Function

I had this function in Access, however, it is not watching the results of the Excel Function for the following prices:

0.70495 is becoming 0.7049 rather than 0.705 and 0.40545 is becoming 0.4054 rather than 0.4055

 Public Function FormatSigFig(Value As Double, SigFigs As Long) As String
    Dim Digits As Long
    
    If Value <> 0 Then
        Digits = SigFigs - Int(Log(Abs(Value)) / Log(10)) - 1
        FormatSigFig = Int(0.5 + Value * 10 ^ Digits) / 10 ^ Digits
    Else
        FormatSigFig = 0
    End If
End Function

Any help would be greatly appreciated.

Thanks

[June7]

Round function in Access/VBA follows even/odd rule. http://hypernews.slac.stanford.edu:5....html?inline=1

However, Format function does not. I use Format function in my calcs. Problem is the result is text and can sometimes require conversion to a number with Val function in subsequent calcs.

[mccrimmon]

Hi

Is anyone able to help provide access VBA function that produces same results as my excel function?

Thanks

McC

[June7]

Building a custom rounding function will involve converting the number to a string, breaking it up and testing if the 'last' digit is >= 5 and increasing or not increasing the previous digit and then recombining the string pieces back together and converting to a number. Complicated.

Why not use the intrinsic Format function as suggested?

[recyan]

Hi June,

Does the FormatNumber() function, not work, the way it is desired ?

Thanks

[June7]

FormatNumber behaves the same as Format and the output is the same, a text string. Seems I tried FormatNumber and ran into some problems with it in Access queries or in ControlSource calcs.

[recyan]

May be wrong, but, I think, FormatNumber() should return a number.
Do not know about ControlSource calcs, but I think it should work in queries.

Thanks

[June7]

Test the function in Immediate window. If you try to add two results of Format or FormatNumber function, the result is concatenation, not addition.

[recyan]

Yes, agree with you, works funny with addition (did not expect it - expected FormatNumber() to return a number) . I might be mis-interpreting the subtraction, multiplication & division results.

Function testNumber()
    abc = FormatNumber(0.40545, 4, -1)
    def = FormatNumber(0.40545, 4, -1)
      
    ghi = abc + def
    jkl = abc - def
    mno = abc * def
    pqr = abc / def
    
    Debug.Print "The first number : " & abc
    Debug.Print "The second number : " & def
    Debug.Print "The addition : " & ghi
    Debug.Print "The subtraction : " & jkl
    Debug.Print "The multiplication : " & mno
    Debug.Print "The division : " & pqr
End Function 

The result :

The first number : 0.4055
The second number : 0.4055
The addition : 0.40550.4055
The subtraction : 0
The multiplication : 0.16443025
The division : 1 

Function testNumber_1()
    Dim abc As Double
    Dim def As Double
    Dim ghi As Double
    Dim jkl As Double
    Dim mno As Double
    Dim pqr As Double
    
    abc = FormatNumber(0.40545, 4, -1)
    def = FormatNumber(0.40545, 4, -1)
      
    ghi = abc + def
    jkl = abc - def
    mno = abc * def
    pqr = abc / def
    
    Debug.Print "Test_1_The first number : " & abc
    Debug.Print "Test_1_The second number : " & def
    Debug.Print "Test_1_The addition : " & ghi
    Debug.Print "Test_1_The subtraction : " & jkl
    Debug.Print "Test_1_The multiplication : " & mno
    Debug.Print "Test_1_The division : " & pqr
End Function 

The result :

Test_1_The first number : 0.4055
Test_1_The second number : 0.4055
Test_1_The addition : 0.811
Test_1_The subtraction : 0
Test_1_The multiplication : 0.16443025
Test_1_The division : 1 

Wish we could find some explanation.

Thanks

[June7]

Format and FormatNumber return string values. The plus (+) sign is concatenation operator left over from ancient BASIC that VBA is evolved from. The ampersand (&) is the preferred concatenation operator in VBA but the + concatenator was kept. Use it with two strings, even if they are only digit characters, it will concatenate. Use it with a number string and true number and it will add. VBA and Access will presume that since a true number is in the expression that addition is desired, not concatenation. Weird but true!

Your second test function declares the variables as double type so VBA will interpret the string result of the Format and FormatNumber functions as numbers and the calcs are correct.

Test the Format, FormatNumber, and Round functions in the Immediate window by doing calcs directly with the functions and not setting to variables and can see the results I describe. This is why must be careful when using Format and FormatNumber in queries or ControlSource expressions. This is only an issue with addition. Results of Format and FormatNumber will calc just fine with other math operators.

I just realized that FormatNumber does behave differently - it inserts commas into the result. Which reveals the issue with using FormatNumber in addition calcs. If need to add two results of Format, must use Val function on at least one of them to convert into number value. Val won't work properly with FormatNumber because of the commas. Oh wow, another discovery, CDbl() function will work with result of FormatNumber.

[recyan]

June,

Am Still confused (My day actually started in a confused manner today).
Could you test the result of a FormatNumber() with IsNumeric() ?

Thanks

[June7]

Yes, it works. VBA/Access will interpret the string of digits (commas generated by FormatNumber appear to be ignored) as a number value. Addition is the issue because + is still valid as string concatenation operator.