vendredi 10 mars 2017

If ElseIf outputs after responding to askMsg

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