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
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
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