I had a great answer to my previous question last week, and I have come back for more guidance.
We have a vba macro that runs through the number of revisions made in a Word document and then gives us a difficulty score based on the word count. Recently, we have started to use two different processes that require two different grades, but they are based on the same algorithm (number of revisions/word count). Essentially, I'm trying to make an If ElseIf that will return the score based on the process used. The front end calculations are all the same, so it's simply a matter of implementing the correct definitions.
The original process is still intact when I add the askMsg question; however, when I try to run the second process, the return value is 0 (should be a value of 1-10). It doesn't matter whether I place the new process as the If or the ElseIf; the original code works the same and the new code doesn't despite them being virtually identical. I have included the full code and indicated the lines of concern with 'XX.
Sub Grade()
Dim oDoc As Document
Dim nWords As Long
Dim commentCount As Long
Dim revisionCount As Long
Dim totalRev As Long
'Dim hasChanges As Boolean
'Dim oRevision As Revision
'Dim oComment As comment
Dim rDensity As Variant
Dim mGrade As Long
Set oDoc = ActiveDocument
' update and count number of words
nWords = oDoc.Range.ComputeStatistics(wdStatisticWords)
' The following line was added to handle an accidental macro-button press on an empty
' document. It executes only if nWords is 0 to avoid the divide-by-zero error; it does not handle an
' error in getting the word count.
If nWords = 0 Then mGrade = 10: GoTo ExitManuscriptGrade
'check to see if the document has changes,
revisionCount = oDoc.Revisions.count ' get the revision count
commentCount = oDoc.Comments.count ' get the number of comments
' all the following scoring code was left exactly the same
totalRev = commentCount + revisionCount
'calculate density of revisions
rDensity = totalRev / nWords
'XX Here is where the issues start XX
askMsg = "Is this a Top Tier Sub?"
msgResult = MsgBox(askMsg, vbYesNo)
Select Case msgResult
Case vbYes
TopTier = True
Case vbNo
TopTier = False
End Select
If TopTier = True Then
'Assign manuscript grade
If rDensity > 0.4 Then
mGrade = 1
ElseIf rDensity <= 0.4 And rDensity > 0.37 Then
mGrade = 2
ElseIf rDensity <= 0.37 And rDensity > 0.34 Then
mGrade = 3
ElseIf rDensity <= 0.34 And rDensity > 0.3 Then
mGrade = 4
ElseIf rDensity <= 0.3 And rDensity > 0.26 Then
mGrade = 5
ElseIf rDensity <= 0.22 And rDensity > 0.26 Then
mGrade = 6
ElseIf rDensity <= 0.18 And rDensity > 0.22 Then
mGrade = 7
ElseIf rDensity <= 0.16 And rDensity > 0.18 Then
mGrade = 8
ElseIf rDensity <= 0.13 And rDensity > 0.16 Then
mGrade = 9
ElseIf rDensity <= 0.13 Then
mGrade = 10
End If
'Message box output for testing
MsgBox (mGrade) ' XX mGrade is always set to zero XX
Exit Sub
ElseIf TopTier = False Then
'XX This is where the code originally went prior to adding in askMsg XX
'Assign manuscript grade
If rDensity > 0.31 Then
mGrade = 1
ElseIf rDensity <= 0.31 And rDensity > 0.27 Then
mGrade = 2
ElseIf rDensity <= 0.27 And rDensity > 0.24 Then
mGrade = 3
ElseIf rDensity <= 0.24 And rDensity > 0.2 Then
mGrade = 4
ElseIf rDensity <= 0.2 And rDensity > 0.18 Then
mGrade = 5
ElseIf rDensity <= 0.18 And rDensity > 0.16 Then
mGrade = 6
ElseIf rDensity <= 0.16 And rDensity > 0.13 Then
mGrade = 7
ElseIf rDensity <= 0.13 And rDensity > 0.11 Then
mGrade = 8
ElseIf rDensity <= 0.11 And rDensity > 0.09 Then
mGrade = 9
ElseIf rDensity <= 0.09 Then
mGrade = 10
End If
'Message box output for testing
MsgBox (mGrade) ' XX This outputs the mGrade correctly XX
End If
Exit Sub
' Execution jumps to this label if there are no words in the document
ExitManuscriptGrade:
End Sub
I'm not sure if I'm missing a small command or need some other Else statement, but any help would be greatly appreciated!
Aucun commentaire:
Enregistrer un commentaire