Macro Debugging - I can't figure out what's wrong!

[Ryan Jamison]

Hi All,
I am having great difficulty in trouble shooting a macro (below is a
shortened version, but with same functionality):

Sub Macro1()
Dim myArray(0 To 1, 0 To 1) As String
Dim i As Long, prange As String, srange As Range
Dim strNum As String

myArray(0, 0) = "Alum"
myArray(1, 0) = "Steel"

myArray(0, 1) = "1"
myArray(1, 1) = "2"

With ActiveDocument
For i = 1 To .Paragraphs.Count
prange = ActiveDocument.Paragraphs(i).Range.Text
If Left(prange, 10) = "MATERIAL " Then
strNum = Mid(prange, 11)
End If
Next i
End With

MsgBox strNum & myArray(1, 1)
If strNum = myArray(1, 1) Then
MsgBox "TRUE"
End If

End Sub

The active document looks like:

MATERIAL 1
// from 'MAT1 1 '
Isotropic
E=1e+07
NU=0.3
density=0.00025382
END
MATERIAL 2
// from 'MAT1 2 '
Isotropic
E=6e+07
NU=0.3
density=0.0.003
END

I'm having difficulty with the "IF" statement at the very end. The results
of the MsgBox look the same, but evaluate differently. Any ideas?

Ryan

 

[Jay Freedman]

Hi Ryan,

The problem you're having is that the expression Mid(prange, 11) returns
everything from the 11th position in prange to the end, including extra
spaces and the paragraph mark. That can never be equal to the one-character
string stored in the array.

To fix it, include the optional third parameter of the Mid function to
specify the length: Mid(prange, 11, 1).

Here are two debugging suggestions that would let you diagnose this kind of
error:

1. In the VBA editor, click View > Locals and then use the F8 key to step
through the code one statement at a time. Watch the values of the variables
in the Locals window. This is easier than writing MsgBox or Debug.Print
statements, and doesn't have to be removed when you're finalizing the code.

2. When you do write MsgBox or Debug.Print statements that will display
strings, include delimiters before and after the string variable to help you
detect spaces, tabs, carriage returns, and other "invisible" characters. For
example,

MsgBox "|" & strNum & "|" & myArray(1, 1)

would display the second "|" on the second line of the message instead of
the first line, proving that there's at least a carriage return in strNum.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.