Solved: Using TXT file as array filler

[lynnnow]

Hi Gerry:

I understand that there are some words that are being replaced with the same word and it is intentional. This is so that the original word is not overwritten with the inappropriate word, it will just get highlighted. Since I needed to use the same word for the replacement but highlight it, I used this technique. In the example that I provided I did use the "Trial" and "Trail" option only for code check purposes. It is not used in runtime. However, there are some spellings that are replaced with the correct spellings, for eg. judgement vs. judgment (since judgement is a british spelling of the word and our firm is in the US). These words are highlighted with red to make it stand out, since it is a fatal error if british spellings are found by the client.

Joost:

That tweak was marvellous. With your help, I've now made the following edit to the code, which still needs a bit of a nudge. I'm having a difficulty in setting the highlight color from the variable.

the code looks like this now:

[VBA]
For j = 1 To i
If MyList(j, 0) = "*" Then
ColorUse = MyList(j, 1)
j = j + 1
End If
Options.DefaultHighlightColorIndex = ColorUse ' This is where I need help
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Highlight = True
With Selection.Find
.Text = MyList(j, 0)
.Replacement.Text = MyList(j, 1)
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = True
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = True
End With
Selection.Find.Execute Replace:=wdReplaceAll
Next
[/VBA]

It shows me a type mismatch error. I know that wdDarkRed and the other color codes are VBA standard, however, can't it be passed thru a variable? If it can be, is there any other method of passing these codes to that line?

Thanks for all the efforts guys...

[MOS MASTER]

It shows me a type mismatch error. I know that wdDarkRed and the other color codes are VBA standard, however, can't it be passed thru a variable? If it can be, is there any other method of passing these codes to that line?

Thanks for all the efforts guys...

You're most welcome!

Wel you've only pasted part of you code so we pretty much know nothing. (No offence) If you ask a question please post the whole code so we can see important things like Dim statements for dimensioning the variabels.

You use: ColorUse as a variable.
You don't use the Naming convention (like strColorUse or sColorUse) so that I could guess it was a String (I think it is)

First of al if you wan't to replace a Constant from a Enum you have to pass it in the right Variable dimension and use it's numeric index in the Enum to pass it.

So wdDarkRed = 13 in wdColorIndex (Enum)
To retrieve 13 go to the VBE en press F2. In the search box paste wdDarkRed and press enter. To your lower left you'll see something like:
Const wdDarkRed = 13

So you're code needs to pass 13 in this line:
Options.DefaultHighlightColorIndex = ColorUse

Now you need to no first which type DefaultHighlightColorIndex expects so highlight is and press F1.
The help states that the Value is Long so your Variable "ColorUse" should be dimmed to Long

Like: Dim ColorUse As Long

So the only line you have to change in your current code is the Dim statement. (I presume cause I didn't seen all)

HTH,

[lynnnow]

Thanks again Joost

This is the entire code now as I use it. It works perfectly. I've edited the Words Source.txt file to have the wdColorIndex Enum instead of the value. This is the complete code as it now stands:

[VBA] Sub SpellChecker_Modified()

Dim tmp
Dim CheckList As String, TextLine As String
Dim ColorUse As Long
Dim MyList(5000, 2)
Dim i As Long, j As Long

CheckList = "C:\Lincoln\Projects\words source.txt"

Open CheckList For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Check for end of file.
i = i + 1
Line Input #1, TextLine ' Read line of data.
tmp = Split(TextLine, vbTab)
MyList(i, 0) = tmp(0)
MyList(i, 1) = tmp(1)
Loop
Close #1

'CheckArray:
For j = 1 To i
If MyList(j, 0) = "*" Then
ColorUse = Val(MyList(j, 1))
j = j + 1
End If
Options.DefaultHighlightColorIndex = ColorUse
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Highlight = True
With Selection.Find
.Text = MyList(j, 0)
.Replacement.Text = MyList(j, 1)
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = True
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Next
Options.DefaultHighlightColorIndex = wdYellow
End Sub
Public Function Split(Expression As String, Optional ByVal Delimiter As _
String = " ", Optional ByVal Count As Long = -1, _
Optional ByVal Compare As Integer = 0) As Variant

Dim lPos1 As Long
Dim lPos2 As Long
Dim lIdx As Long
Dim lCnt As Long
Dim arResult() As String

'Initialize the variables
lCnt = 0
lPos1 = 1
ReDim arResult(99)

'Search for the delimiter.
lPos2 = InStr(1, Expression, Delimiter, Compare)
Do While lPos2 > 0 And ((lCnt <= Count) Or (Count = -1))
'Delimiter found, extract the substring between the delimiters.
arResult(lCnt) = Mid$(Expression, lPos1, lPos2 - lPos1)
lCnt = lCnt + 1
If (lCnt Mod 100) = 0 Then
'Increase array size if needed.
ReDim Preserve arResult(UBound(arResult) + 100)
End If
'Move to end of last delimiter found.
lPos1 = lPos2 + Len(Delimiter)
'Search for the next delimiter.
lPos2 = InStr(lPos1, Expression, Delimiter, Compare)
Loop

If lPos1 < Len(Expression) Then
'Extract last substring.
arResult(lCnt) = Mid$(Expression, lPos1)
lCnt = lCnt + 1
End If

'Resize the array to correct size.
If lCnt > 0 Then
ReDim Preserve arResult(lCnt - 1)
Else
ReDim arResult(-1 To -1)
End If

'Return the array.
Split = arResult

End Function
[/VBA]

Now all I have to do this amend the default location for the txt file and then the system is a go. Thanks again

[MOS MASTER]

Hi,
Glad we could help you and good luck on your project!
ps..don't forget to mark your thread solved.