Find & replace issue with Greek letter

[Harry88]

I am developing a macro to find Greek letters in a normal font and replace them by using the InsertSymbol function with the Symbol font. The macro works fine with all letters except sigma, because this letter has two forms – a regular one "σ", and a final one "ς" for use at the end of words – but the find function is not distinguishing between the two forms.

The following macro loops through a "vFindText" array that should find both forms of the letter and replace them with corresponding characters from the "vReplaceText" array using the InsertSymbol function.


Sub Macro1()
Dim vFindText As Variant
Dim vReplaceText As Variant
Dim oRng As Range
Dim i As Integer


vFindText = Array(ChrW(963), ChrW(962))
vReplaceText = Array(-3981, -4010)
For i = 0 To UBound(vFindText)
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = vFindText(i)
Do While .Execute
oRng.Select
oRng.InsertSymbol Font:="Symbol", _
CharacterNumber:=vReplaceText(i), _
Unicode:=True
oRng.Collapse 0
Loop
End With
Next i
End Sub


If the macro is run on the attached document #1, the result should be as in the attached document #2, with the two forms of the letter converted separately, but currently it replaces them both with the same form. How can I get the macro to work as specified?

30569
30570

[June7]

Why was code not in document?

Consider this revision:


If AscW(Selection.Characters(1).Text) = AscW(.Text) Then
oRng.InsertSymbol Font:="Symbol", _
CharacterNumber:=vReplaceText(i), _
Unicode:=True
End If

[Harry88]

Thanks for your reply. With your work-around, the macro does two loops: a first loop where it finds all the sigmas but skips the regular ones and replaces only the final ones, then a second loop where it finds and replaces only the regular ones – which does achieve the required result with the macro in this form.

However, the macro in its full form (below) includes a prompt to enable the user to choose whether each match is replaced or skipped, and with your work-around, the user gets prompted twice for the regular sigmas, once in the first loop, and again in the second loop.


Sub Macro1()
Dim vFindText As Variant
Dim vReplaceText As Variant
Dim oRng As Range
Dim i As Integer


vFindText = Array(ChrW(962), ChrW(963))
vReplaceText = Array(-4010, -3981)
For i = 0 To UBound(vFindText)
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = vFindText(i)
Do While .Execute
oRng.Select
lAsk = MsgBox("Replace Symbol", vbYesNoCancel)
If lAsk = 2 Then GoTo lbl_Exit
If lAsk = 6 Then
If AscW(Selection.Characters(1).Text) = AscW(.Text) Then
oRng.InsertSymbol Font:="Symbol", _
CharacterNumber:=vReplaceText(i), _
Unicode:=True
End If
End If
oRng.Collapse 0
Loop
End With
Next i
lbl_Exit:
End Sub


Is it possible to modify the work-around so that the user gets prompted only once for each match?

[June7]

Not sure what you mean by 'each match'. If regular sigma character is in the text four times, isn't that 4 matches and 4 prompts? But since you say there are only 2 prompts, once for each loop, if you want only 1 prompt then don't put it inside a loop.

How is this replace operation initiated by user?

[Harry88]

The attached document #3 has four sigmas: two regular ones "σ" (in the first two lines), and two final ones "ς" (in the last two lines); i.e., it contains four matches.

If you run the macro from post #3 on this file, and answer "Yes" to replace each match when prompted, then it goes as follows.
- First it does a loop in which it finds each of the four sigmas in turn; and in this loop, it replaces the final ones but doesn't replace the regular ones.
- Then it does a second loop in which it finds only the two regular sigmas, and in this loop it does replace them.

In other words, currently the user gets prompted once for each of the two final sigmas but twice for each of the two regular sigmas; i.e., four prompts in the first loop resulting in two replacements (the two final sigmas), and two prompts in the second loop resulting in two replacements (the two regular sigmas), so six prompts altogether resulting in four replacements.

I'm wondering what your suggested addition ("If AscW(Selection.Characters(1).Text) = AscW(.Text) ...") does to distinguish the two sigma forms, and why all four sigmas currently are not replaced the first time they are matched, and whether the code can be modified so that the user gets prompted once for each match; i.e., four prompts resulting in four replacements?

30572

[June7]

Or put popup in correct place in loop. Try this revision:


Do While .Execute
oRng.Select
If AscW(Selection.Characters(1).Text) = AscW(.Text) Then
lAsk = MsgBox("Replace Symbol", vbYesNoCancel)
If lAsk = 2 Then GoTo lbl_Exit
If lAsk = 6 Then
oRng.InsertSymbol Font:="Symbol", _
CharacterNumber:=vReplaceText(i), _
Unicode:=True
End If
End If
oRng.Collapse 0
Loop

Should Dim the lAsk variable. Do you have Option Explicit at top of module?

[Harry88]

That works fine, thanks. (The full macro does include "Dim lAsk as Long" but I inadvertently omitted it in this cut-down version ... well spotted!)

Can you explain briefly what the work-around does ("If AscW(Selection.Characters(1).Text) = AscW(.Text) ..."); i.e., how it distinguishes the two sigma forms?

[June7]

Can't miss when it slaps you with compile error.

Best I can say is the Find operation doesn't distinguish between the two characters. Search for one and it finds both. This can be demonstrated by manual execution of the Find/Replace dialog. However, the AscW() function returns a different number for each character (the 962 and 963 used by ChrW() function) so VBA can see the difference. The conditional tests if search text matches found text.

[Harry88]

Thanks again for your help.

[Harry88]

I stumbled on this alternative, which appears to have the same result. (The two lines in blue are commented out, and the one in red is added.)


Sub Macro1()
Dim vFindText As Variant
Dim vReplaceText As Variant
Dim oRng As Range
Dim i As Integer
Dim lAsk as Long


vFindText = Array(ChrW(962), ChrW(963))
vReplaceText = Array(-4010, -3981)
For i = 0 To UBound(vFindText)
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = vFindText(i)
.MatchCase = True
Do While .Execute
oRng.Select
' If AscW(Selection.Characters(1).Text) = AscW(.Text) Then
lAsk = MsgBox("Replace Symbol", vbYesNoCancel)
If lAsk = 2 Then GoTo lbl_Exit
If lAsk = 6 Then
oRng.InsertSymbol Font:="Symbol", _
CharacterNumber:=vReplaceText(i), _
Unicode:=True
End If
' End If
oRng.Collapse 0
Loop
End With
Next i
lbl_Exit:
End Sub


This has the upside of running faster, as there are no "false" instances of the "If AscW(Selection.Characters(1).Text) = AscW(.Text) Then ..." condition to be skipped during any loop. However, are there any potential downsides to this alternative?

[June7]

I wouldn't know. Have to let you do testing to determine that. But that does explain why both symbols are treated same. One is lower case and one is upper case and non-case sensitive is default. Specifying MatchCase as True forces case sensitivity in the Find action. Cool!!.

Don't think I tried that in my manual test.

[Harry88]

OK thanks. I have done some testing, and it appears to work as required. I will report back if any issues turn up.

There are actually four Greek letters that have two lower-case forms.

30588

For some reason, Word's Find function distinguishes between the two forms of the first three pairs (theta, pi, phi) even with the Match Case option off; only the two forms of sigma are treated as identical unless the Match Case option is on.