how to speed Find/Replace in Excel(Using VB)

[Guest]

In my project,one excel file is used as template.there are many tags in the
file and these tags will be replaced.
I use VB to reach to the aim through Excel Activex,but I notice it is very
slow.
it took more than ten seconds to replace all tags.
how can I do it? thanks.

 

[Guest]

Can you explain in more detail what you are doing and post any relevant code.

 

[Tim Williams]

If you put all of your code in VBA is it much faster?

Using VB to interact with Excel is often a lot slower than using VBA within
Excel, particularly if there are a lot of "transactions" in your code (eg.
if you make many calls from VB to Excel). This is because your
communication is between two processes (VB/Excel) rather than within the
same process as happens when you use VBA in Excel.

Try to reduce the number of calls you make to Excel, or put some of your
code in an add-in and use VB to load it into the Excel process.

Tim.

 

[Guest]

My code is very easy.
value = replace(findRange.value, contents(0), contents(1))
findRange.value = value

 

[Guest]

when I use VBA in Excel. I find it does't speed the operation.
with more and more tags that will be replaced, the operation is slower and
slower.
it cost more than 5s if 22 tags will be replaced
how can I do?

my code(Using VBA):
Sub Macro1()
Dim tags As ArrayList
Dim i As Integer
Dim j As Integer
Set tagsfs = New FileSys
'in tags.txt,tagname and value put in it,for example
'tag1=value1
'tag2=value2
tagsfs.setFilePath "d:\tags.txt"
Application.DisplayAlerts = False
Set tags = tagsfs.getAllLines
For i = 1 To Sheets.Count
Sheets(i).Activate
For j = 0 To tags.Length - 1
'tags.getValue(index): tagname=value
contents = Split(tags.getValue(j), "=", 2)
Set findRange = Cells.Find(what:=contents(0))
While Not findRange Is Nothing
findRange.Activate
'if tags is #LT_CHART#, add one picture
If InStr(1, contents(0), "#LT_CHART#") > 0 Then
ActiveSheet.Pictures.Insert( _
contents(1)).Select
Cells.Replace what:="#LT_CHART#", Replacement:=""
Else
Cells.Replace what:=contents(0), Replacement:=contents(1)
End If
Set findRange = Cells.FindNext(After:=ActiveCell)
Wend
Next j
Next i
End Sub