VBA Format Function not Formatting Numbers on Worksheet

[ScottyBee]

I am training a class and was showing students how to use the "Format" function in VBA. Everyone's code worked by returning one decimal place for the MPG except for one student. His function is returning multiple decimal places to the worksheet. I checked character for character to see how his code differed from mine and cannot see any differences.

We have created a function called MPG that calculates gas mileage and used the Format function so that the values returned in the worksheet are formatted with one decimal place. Below is the code and how the returned value is displayed in the worksheet.


Public Function MPG(BegMiles, EndMiles, FuelConsumed) As Single
Dim Mileage As Single
MPG = Format(((EndMiles - BegMiles) / FuelConsumed), "#,##0.0")
End Function

29025

Any ideas on why the format function isn't returning one decimal place? Thanks

[Paul_Hossler]

WS functions cannot do more than return a value to the WS



Format function

Returns a Variant (String) containing an expression (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#expression) formatted according to instructions contained in a format expression.


So I'm assuming that some students had applied a number format to col E and some did not

However,

1. Format() returns a string
2. Which is coerced into a Single when it is returned by the function
3. Which coerces it into a Double when it is returned to the worksheet

That's a lot of messing about and wasted processing cycles.

I think it'd be better to use a simpler function like below, type all variables and parameters correctly, and apply a number format to the worksheet manually or with a sub



Option Explicit


Function MPG(BegMiles As Double, EndMiles As Double, FuelConsumed As Double) As Double
If FuelConsumed <= 0 Then
MPG = 0
Else
MPG = (EndMiles - BegMiles) / FuelConsumed
End If
End Function




Sub FormatMPG()
ActiveSheet.Columns(7).NumberFormat = "#,##0.0"
End Sub

[ScottyBee]

Thanks Paul for the solution. It worked out great and your explanation makes sense. Enjoy your weekend :)