Format percent function

[dworst]

I've got the following code and I want the result to be formatted as
follows:

0.0%

I've looked for samples of how to do this but I'm a bit confused. Can
someone help me out with how to do it? Thanks in advance!

Public Sub Eval()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim intWait As Integer

StatusBar = "Please wait..."

For intWait = 1 To 1000
System.Cursor = wdCursorWait
Next intWait

Dim a, b, c, d, e, f, g, h, i, j, k As Double
Dim pScore As Double
Dim vdenominator As Integer

vdenominator = 11
With ThisDocument
Select Case True 'Adaptability
Case .OptionButton1.Value
a = 1
Case .OptionButton11.Value
a = 2
Case .OptionButton12.Value
a = 3
Case .OptionButton13.Value
vdenominator = vdenominator - 1
Case Else
MsgBox "No selection in Adaptability"
Exit Sub
End Select

Select Case True 'Acumen
Case .OptionButton2.Value
b = 1
Case .OptionButton21.Value
b = 2
Case .OptionButton22.Value
b = 3
Case .OptionButton23.Value
vdenominator = vdenominator - 1
Case Else
MsgBox "No selection in Business Acumen"
Exit Sub
End Select

Select Case True 'BusinessCollaboration
Case .OptionButton3.Value
c = 1
Case .OptionButton31.Value
c = 2
Case .OptionButton32.Value
c = 3
Case .OptionButton33.Value
vdenominator = vdenominator - 1
Case Else
MsgBox "No selection in Business Collaboration"
Exit Sub
End Select

Select Case True 'TeamCollaboration
Case .OptionButton7.Value
k = 1
Case .OptionButton8.Value
k = 2
Case .OptionButton9.Value
k = 3
Case .OptionButton10.Value
vdenominator = vdenominator - 1
Case Else
MsgBox "No selection in Team Collaboration"
Exit Sub
End Select

Select Case True 'Communication
Case .OptionButton4.Value
d = 1
Case .OptionButton41.Value
d = 2
Case .OptionButton42.Value
d = 3
Case .OptionButton43.Value
vdenominator = vdenominator - 1
Case Else
MsgBox "No selection in Communication"
Exit Sub
End Select

Select Case True 'Customer Focus"
Case .OptionButton5.Value
e = 1
Case .OptionButton51.Value
e = 2
Case .OptionButton52.Value
e = 3
Case .OptionButton53.Value
vdenominator = vdenominator - 1
Case Else
MsgBox "No selection in Customer Focus"
Exit Sub
End Select

Select Case True 'Knowledge Application
Case .OptionButton6.Value
f = 1
Case .OptionButton61.Value
f = 2
Case .OptionButton62.Value
f = 3
Case .OptionButton63.Value
vdenominator = vdenominator - 1
Case Else
MsgBox "No selection in Knowledge Application"
Exit Sub
End Select

Select Case True 'Leadership
Case .OptionButton64.Value
g = 1
Case .OptionButton641.Value
g = 2
Case .OptionButton642.Value
g = 3
Case .OptionButton643.Value
vdenominator = vdenominator - 1
Case Else
MsgBox "No selection in Leadership"
Exit Sub
End Select

Select Case True 'People Development
Case .OptionButton644.Value
h = 1
Case .OptionButton6441.Value
h = 2
Case .OptionButton6442.Value
h = 3
Case .OptionButton6443.Value
vdenominator = vdenominator - 1
Case Else
MsgBox "No selection in People Development"
Exit Sub
End Select

Select Case True 'Project Participation
Case .OptionButton6444.Value
i = 1
Case .OptionButton64441.Value
i = 2
Case .OptionButton64442.Value
i = 3
Case .OptionButton64443.Value
vdenominator = vdenominator - 1
Case Else
MsgBox "No selection in Technical Expertise"
Exit Sub
End Select

Select Case True 'Technical Expertise
Case .OptionButton64444.Value
j = 1
Case .OptionButton644441.Value
j = 2
Case .OptionButton644442.Value
j = 3
Case .OptionButton644443.Value
vdenominator = vdenominator - 1
Case Else
MsgBox "No selection in Technical Expertise"
Exit Sub
End Select

pScore = (a + b + c + d + e + f + g + h + i + j + k) / vdenominator

MsgBox "Average score = " & pScore
End With

With ActiveDocument
.Unprotect
.FormFields("Total_Score").Result = pScore
.Protect wdAllowOnlyFormFields, True
End With
End Sub

 

[Jonathan West]

I've got the following code and I want the result to be formatted as
follows:

0.0%

Dim a, b, c, d, e, f, g, h, i, j, k As Double

By the way, do you realise that you have declared a, b, c, d, e, f, g, h, i
and j as Variants?

pScore = (a + b + c + d + e + f + g + h + i + j + k) / vdenominator

MsgBox "Average score = " & pScore

MsgBox "Average score = " & Format(pScore, "#0.0") & "%")


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[dworst]

Is there a problem with declaring them as variants in this function?

Also, I see that you are formating the result in the message box as a
percent but I have a form field "Total Score" that needs this result
also. So how do I format the Total Score form field to show only
"0.0%" format?

I'm also getting an syntax error with the code you gave me for the Msg
Box but I'll take another look at it.

Thanks for helping me out. I'm confused as to how to format as a
percent.

 

[Jonathan West]

Is there a problem with declaring them as variants in this function?

In this context probably not. Given what the variables are being used for,
they would probably better be declared as Long - the code would be
marginally quicker.

Also, I see that you are formating the result in the message box as a
percent but I have a form field "Total Score" that needs this result
also. So how do I format the Total Score form field to show only
"0.0%" format?

..FormFields("Total_Score").Result = Format(pScore, "#0.0") & "%"

I'm also getting an syntax error with the code you gave me for the Msg
Box but I'll take another look at it.

Ah - there's a stray closing parenthesis at the end.

Thanks for helping me out. I'm confused as to how to format as a
percent.

Look up the Format function in VBA Help


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[dworst]

Jonathan:

Thanks for the explanation on the variant -- I changed it. Also, I
figured out the error also in the Msg Box code and just replicated that
for my Total Score field which works great.

Thanks so much for your help!