Format(sum,"00.000")

[Reytheo]

Hi, I am mapping crossfunctional flowcharts. In the shape data of each step of the process I have the number of FTE. Now I'd like to sum in another shape the total FTE for the process. I've found a piece of VBA code in a previous post here, and it's working fine without error. The problem is that it is returning the total in a number format that has no decimal. I need to have 4 numbers after the decimal: "00.000". I'm pretty much a newbie in VBA. I've tried to use the format function. Anyone can help me out? Here is the code:
Sub FTESUM()

Dim shp As Visio.Shape, sumshp As Visio.Shape
Dim Sum As Integer

Sum = 0

For Each shp In ActivePage.Shapes

  If shp.CellExists("Prop.FullTimeEquivalent", False) Then
    Sum = Sum + shp.Cells("Prop.FullTimeEquivalent").Result("")

  End If

  If shp.CellExists("Prop.TotalFTE", False) Then
    Set sumshp = shp
   
    End If
   
Next
sumshp.Cells("Prop.TotalFTE").Formula = Sum

End Sub

[wapperdude]

You ought to be able to use the format function.  Something like:  format(sum, "0.0000 U")

Of course, in VBA you'll need something more like: 
  sumshp.Cells("Prop.TotalFTE").Formula = "format(" & sum & ", " & ""0.0000 U"" & ")" 

The "0.0000 U" is a problem, and I don't recall the exact VBA format to handle that.  May need use of chr(34).

HTH
Wapperdude

[Reytheo]

Hi Wapperdude,

Thanks for the quick answer, but a programmer at work was able to supply a reviewed VBA code. I'm posting the modified code for future reference for anyone who would have the same need (i.e Business process analysts). I've also uploaded the file to share the love.

Cheers!

Sub FTESUM()

Dim shp As Visio.Shape, sumshp As Visio.Shape
Dim Sum As Double ' Integer only stored whole numbers, Double will stored decimal points

Sum = 0

For Each shp In ActivePage.Shapes

  If shp.CellExists("Prop.FullTimeEquivalent", False) Then
    Sum = Round(Sum + shp.Cells("Prop.FullTimeEquivalent").Result(""), 4) ' Rounding to 4 decimal points

  End If

  If shp.CellExists("Prop.TotalFTE", False) Then
    Set sumshp = shp
   
    End If
   
Next
sumshp.Cells("Prop.TotalFTE").Formula = Sum

MsgBox Sum

End Sub