Solved: Problem with Find/Replace Formatting in Table

[duluter]

Hi, everyone.

I have a macro that pulls a few thousand records from a database, adds the same number of rows to an existing Word table, and places each record from the database into the table. After everything is in, I select one of the columns and do a find and replace to get rid of italics on chunks of text that meet certain criteria. This all basically works.

The column in question holds scientific names of plant species, like:

Scientific Name
Senecio vulgaris
Silybum sp.
Taraxacum officinale
Hordeum marinum ssp. gussoneanum
etc.

Notice that the "sp." and "ssp." parts are not italicized. I search for these words and get rid of the italics on them, as described above.

The weird thing is that my program successfully removes the italics from all 5000+ rows, except for the first 14 rows. It's kind of driving me nuts. Does anyone have any ideas why the italics are not being removed in the first few rows of my table? Code below:


'Undo italics on "var.", "sp.", and "spp."
objTable.Columns(2).Select

Call ItalicsToRoman("ssp.", True)
Call ItalicsToRoman("sp.", True)
Call ItalicsToRoman("var.", True)

Selection.Collapse

And here is my "ItalicsToRoman" routine that is called in the above code:

Public Sub ItalicsToRoman(strText As String, WholeWord As Boolean)

With Selection.Find

.ClearFormatting
.Font.Italic = True
.Text = strText
.MatchCase = False
.MatchWholeWord = WholeWord

With .Replacement

.ClearFormatting
.Font.Italic = False
.Text = strText

End With

.Execute Format:=True, Replace:=wdReplaceAll

End With

End Sub


Thanks,

Duluter

[duluter]

OK. I worked around the issue by using a Range.Find instead of a Selection.Find.

Still not sure why this would make any difference though.


I'll leave the thread "unresolved" for a little while to let people weigh in on why I was having the problem when using the Selection object.


Duluter

[fumei]

Actually, I got it to work using exactly your code, using Selection. I could not duplicate this. I used:

Sub TableStuff_1()
Dim objTable As Table
Set objTable = ActiveDocument.Tables(1)
objTable.Columns(2).Select

Call ItalicsToRoman("ssp.", True)
Call ItalicsToRoman("sp.", True)
Call ItalicsToRoman("var.", True)

Selection.Collapse
End Sub


Public Sub ItalicsToRoman(strText As String, _
WholeWord As Boolean)
With Selection.Find
.ClearFormatting
.Font.Italic = True
.Text = strText
.MatchCase = False
.MatchWholeWord = WholeWord
With .Replacement
.ClearFormatting
.Font.Italic = False
.Text = strText
End With
.Execute Format:=True, Replace:=wdReplaceAll
End With
End Sub
which is basically your code, with indenting a bit tidier. It works for me.

I did do a re-write of your code. Here is an alternative.
Sub MyWay()
Dim ToChange()
Dim oTable As Table
Dim oCell As Cell
Dim r As Range
Dim var
ToChange = Array("ssp.", "sp.", "var.")

Set oTable = ActiveDocument.Tables(1)
For Each oCell In oTable.Range.Columns(2).Cells
Set r = oCell.Range
With r.Find
For var = 0 To UBound(ToChange())
Do While .Execute(Findtext:=ToChange(var), _
Forward:=True) = True
r.Font.Italic = False
Loop
Set r = oCell.Range
Next
End With
Next

End Sub

It does exactly the same thing, and it works as well.

That being said, it IS better to use Range, rather than Selection.

How did you set and use the range of the column?

[duluter]

fumei:

Excellent post--thank you very much.

It makes sense to me that you got it to work using my code--that's the way it should work! I can't understand why it isn't working for me. I have screenupdating to false, and I have thousands of rows. Maybe Word just gets confused using the selection object under those conditions?

"How did you set and use the range of the column?"

I didn't. I just used oTable.Range instead and searched through every cell, regardless of which column they were in. This was a reasonable work around (I didn't think of doing it your way).

Regarding your way--I wish I had thought of it! It's very elegant. My way always ends up looking like Frankenstein because I'm always jumping around from this project to that and I'm never familiar enough with the various object models to do anything efficiently. It's a source of frustration for me.


Thanks again.

Duluter

[duluter]

fumei:

Now that I've had a chance to run your code, I do have a question about efficiency.

I pretty much copied your code verbatim, and it seemed to run extremely slowly in my massively huge table (1000's of rows). I wonder if that is because your code resets object assignments with each pass as it progresses down the table (as well as calling Find.Execute on each cell). With my original code, Word can do a complete find/replace throughout the whole table in less than a second.

I realize that my range method stinks if you have to limit your search to one column only, so that's a limitation. But grabbing the whole table range all at once and letting Word deal with the nitty-gritty seems to work much, much faster.

Any thoughts on this?


Duluter

[fumei]

Using native Find/Replace is always faster. Try this:
Sub MyWay()
Dim ToChange()
Dim r As Range
Dim var

ToChange = Array("ssp.", "sp.", "var.")

For var = 0 To UBound(ToChange())
Set r = ActiveDocument.Tables(1).Range
With r.Find
.ClearFormatting
.Text = ToChange(var)
.Font.Italic = True
With .Replacement
.ClearFormatting
.Text = ToChange(var)
.Font.Italic = False
End With
.Execute Format:=True, Replace:=wdReplaceAll
End With
Next
End Sub

It still runs through an array of your searching text. there is still no need to do those multiple Calls.

[duluter]

Thanks, fumei. That tidies up my code quite nicely. I haven't ever really used the Array keyword before, so this is a good introduction.

One last question:

I want to pass the array to the routine, rather than declaring the array within the routine. Like this:

Sub Main()

dim oTable as Table
dim myArray() as Variant

'Set oTable, not shown.

myArray = Array("sp.","spp.","var.")

Call ItalicsToRoman(oTable, myArray, True)

End Sub

Sub ItalicsToRoman (myTable as Table, ToChange() as Variant, WholeWord as Boolean)

...
...

End Sub

That works ok, but when I try this, no dice:

Call ItalicsToRoman(oTable, Array("sp.","spp.","var."), True)


It says that there is a type mismatch.

Any ideas why that would be?


Thanks again, I really appreciate it.

Duluter

[fumei]

Because it is a type mis-match.

Sub ItalicsToRoman (myTable As Table, ToChange() As Variant

You are TELLING the Sub to require an explicit parameter - a Variant. Yes?

Array("sp.","spp.","var.") is NOT a Variant. Heck...it is not even an array yet.

Variable = Array(arg1, arg2, arg3...)

Array(arg1, arg2, arg3...) by itself is NOT, well anything really. It is the values of a previously declared Variant - Dim myArray()

BTW: you do not need to declare it as Variant. If VBA is not given an explicit data-type (As String, As Long, As Double...etc.) it assumes the variable is a Variant.

ALL Dim statements with no explicit data-types are Variant.

Dim strClientName.....this is a Variant
Dim lngFinalCost......this is a Variant
Dim dteMonth.....this is a Variant
Dim myArray()....this is a Variant

[duluter]

I see. Thanks for the clarification. I guess I figured that Array() would get evaluated, return a variant array, and that array would get passed to the subroutine.

Kind of like getting a range from a range property and passing that range to a subroutine without first assigning the range to a variable:

Call mySub(ActiveDocument.Tables(1).Range)

I've got my wires crossed somewhere, I suppose. And I'm having a little trouble finding documentation on the Array keyword.


Duluter

[lucas]

http://vbaexpress.com/forum/showpost.php?p=117551&postcount=9

http://vbaexpress.com/forum/showthread.php?t=23560

[fumei]

"Kind of like getting a range from a range property and passing that range to a subroutine without first assigning the range to a variable:

Call mySub(ActiveDocument.Tables(1).Range)"

OK..but your Sub has to be something like:
Sub mySub(aRange As Range)

Yes? Which means the same thing I was stating. The Sub parameter is declared as a Range - and thus a Range is required.

ActiveDocument.Tables(1).Range IS a range.

ToChange() As Variant is declared as a Variant - and thus a Variant is required.

Array(arg1, arg2, arg3...) by itself is NOT a Variant.

Yes, parameter passed as a argument can be evaluated, and is evaluated. Take:

Sub yadda(InputBox("Type something.") As String)



The parameter IS InputBox("Type something.") declared As String.

InputBox("Type something.") IS a string. InputBox IS a string..or rather it is a method that returns a string.

So indeed:

Sub yadda(InputBox("Type something.") As String)


will display an inputbox, get the returned value (as string) and use it...as a string parameter..which is what is declared, and is required.

Again, ToChange() As Variant requires a Variant.

Array(arg1, arg2, arg3...) by itself is NOT a Variant.

Can it be done like you seem to be wanting to do? Yes. Here is how.

1. Declare a Public variable

2. Write a Function, that is declared - and thus returns - a data type of Variant. It is VERY important that the arguments for the function are explicitly declared as ByVal.

VBA, by default, assumes variables are ByRef.

In this case it is crucial that you are working with "real" values. So...ByVal.

3. Write a Sub that requires a Variant - like what you tried to do.

4. Write a Sub that calls the Sub requiring a Variant, BUT...passes the argument through the function. Thus the Function IS used, DOES return a variant, and thus the parameter passed IS a Variant.

The code:

Option Explicit

Public myFilledArray()

Function myArray(ByVal arg1 As String, _
ByVal arg2 As String, _
ByVal arg3 As String) As Variant

myArray = Array(arg1, arg2, arg3)

End Function

Sub FillTheArray(whatever As Variant)
Dim j As Long
Dim msg As String

myFilledArray = whatever
For j = 0 To UBound(myFilledArray())
msg = msg & myFilledArray(j) & vbCrLf
Next

MsgBox "It worked." & vbCrLf & vbCrLf & _
msg
End Sub

Sub TryToCall()
Call FillTheArray(myArray("ssp.", "sp.", "var."))

End Sub
Try executing the Sub TryToCall...not FillTheArray.

Call FillTheArray(myArray("ssp.", "sp.", "var."))


This passes "ssp.", "sp.", "var." as arguments to the Function myArray first.
Function myArray(ByVal arg1 As String, _
ByVal arg2 As String, _
ByVal arg3 As String) As Variant

myArray = Array(arg1, arg2, arg3)

End Function
This takes "ssp.", "sp.", "var." and spits out (returns) myArray as a Variant.

Thus (myArray) is passed as a variant to the Sub FillTheArray(whatever As Variant).

Please note that. It is passed as a variant, NOT an explicit array. Which is why you need:

myFilledArray = whatever


"whatever" - a parameter declared as a Variant - is the Variant myArray (which happens to be an array).

The Public variable myFilledArray is a variant. By making it equal to the whatever Variant, the whatever values are passed to myFilledArray.

Here is an important comparison. This is code in the Sub FillTheArray.
Sub FillTheArray(whatever As Variant)
Dim j As Long
Dim msg As String

'myFilledArray = whatever
'For j = 0 To UBound(myFilledArray())
' msg = msg & myFilledArray(j) & vbCrLf
'Next

For j = 0 To UBound(whatever())
msg = msg & whatever(j) & vbCrLf
Next

MsgBox "It worked." & vbCrLf & vbCrLf & _
msg
End Sub
Look at what I have done. I commented out the myFilledArray = whatever. I try to use the whatever variant directly. Essentially, the code is identical.

A)
For j = 0 To UBound(myFilledArray())
msg = msg & myFilledArray(j) & vbCrLf
Next

B)
For j = 0 To UBound(whatever())
msg = msg & whatever(j) & vbCrLf
Next

EXCEPT...UBound(whatever()) will fail the code: "Subscript out of range."

Why? Because Visual Basic doesn't implicitly dimension unspecified array ranges.

If you hard-code the value:

For j = 0 To 2
msg = msg & whatever(j) & vbCrLf
Next

Then you can use whatever directly. It will not fail.



Are we having fun yet?

[duluter]

Thank you, Gerry. I think I thought that Array() would just be evaluated and return a variant array. I can't find any MS documentation on the Array keyword. Using Google to search msdn yielded nothing that looked promising. I can't find it in the VBE object browser. I mean, I guess it's not that important because I now know that I can't use it quite the way I wanted to (but thanks to your suggested alternate, I can work around that or just save to a variable and pass the variable instead). But I'd still like to find the documentation just to read what MS has to say. Why is it so hard to find the documentation? (Maybe it's just hard for me.)


Duluter

[fumei]

We just missed each other. Please read my last post, it may help.

[duluter]

Steve: Thanks for the links, but my only issue is with the "Array" keyword specifically, not arrays in general. I've got a pretty good handle on how arrays work. Still, informative links.

Gerry: I had read your last post before posting my last comment. I was still a little confused about the Array keyword. However, between then and now, I found the Array Keyword entry in the help file (right where it was supposed to be :)). After reading the entry very carefully I found the ultimate source of my confusion.

Here is basically what I was trying to do initially:

Sub test1()
Call test2(Array("this", "that"))
End Sub

Sub test2(myArray() As Variant)
End Sub


What I should have been doing is this:

Sub test1()
Call test2(Array("this", "that"))
End Sub

Sub test2(myVariant As Variant)
End Sub


The Array keyword returns a variant containing an array, not an array of variants. Thus, when I declared "myArray() as Variant" in my parameter list, I was declaring the wrong type of parameter. I should have been declaring a straight-up variant, not an array of variants.

So, my original thought will work, with the correct syntax:



Sub Main()
Dim oTable As Table
'Set oTable, not shown.
Call ItalicsToRoman(oTable, Array("sp.","spp.","var."), True)
End Sub


Sub ItalicsToRoman (myTable As Table, ToChange As Variant, WholeWord As Boolean)
...

...
End Sub



Two little stinkin' parentheses were the only problems.


Whew.

Thanks again for the help.


Duluter

[fumei]

"Two little stinkin' parentheses were the only problems."

LOL!

True, but do you understand why?

[duluter]

Yes. It's crystal clear now. Case closed on this one.


David

[igotgame]

good job guys