VBA Decimal Places in String Variable

In this post I hope to show you a work around for reducing a string to two decimal places.

I was working on a project just recently for a reporting system for staff sickness.  Part of this had to work out an average sickness for a team , and then output it as part of a sentence.  The average figure would be outputted for example as 1.25372190.  Because it’s part of the sentence I couldn’t  just format the cell to twovba-decimal-places decimal places.   A work around for this was to modify the variable when declaring it.  I was working with data in excess of 1000 lines, which would change every month.  So to keep it simple, let’s assume that in Sheet 1 in Column A there are four names, in cells A1 to cell A4.  Then cells B1 to B4 detail their sickness days.

Let’s look at the code.

Sub SicknessAverage()

Dim sicknessaverage as String * 4 'This reduces the string to four characters
Dim TotalSickness as String 'Total sickness for the whole team
Dim TeamTotal as String

For A = 3 to 6 Step 1
If Cells(A,1) > 0 Then
TotalSickness = TotalSickness + Cells(A,1)
End If
TeamTotal=TeamTotal + 1
Next A

sicknessaverage = TotalSickness / TeamTotal 'This is where the variable is shortened to four places

Cells(1,1) = "Average " & sicknessaverage & " Days per Person"

End Sub

The line highlighted in green is the line that outputs the sentence with the average sickness figure.  It uses the sicknessaverage variable and assigns the answer to TotalSickness divided by TeamTotal, which in this case is 15/4 = 3.75.  So the sentence would read Average 3.75 Days per Person.

By declaring sicknessaverage as Dim sicknessaverage As String * 4 you are actually saying take the first four characters. So if the the answer was 10.75, the variable would be displayed as 10.7 as the decimal point is counted as a character.

Leave a Reply

Your email address will not be published. Required fields are marked *