Solved: Progress Bar help - method not found

[suresh]

Hi friends,
I was trying to implement progress bar for my project. Here is the error I am getting when I am trying to execute the code:
"Sub of function not defined"

I have a method called "FindServersByName". I have defined this in the SearchForm.

I am wondering that should I need to define this in a Module??

Here is the spreadsheet I am attaching. Please advice on progress bar.

Thanks,
Suresh.

[Jacob Hilderbrand]

Yes, just move "Sub FindServersByName()" to a Module and you won't have this probem.

[suresh]

Hi DRJ,
Thanks somuch for quick response.
I just did that and I am getting the error "Invalid use of Me Key word.
text = Me.SearchTextBox.text
any idea to rectify this bug?

Thanks,
Suresh.

[suresh]

Hi,
I forgot to mention one more thing.
I have over 100 servers and I have 2-300 nodes for each server. The search is taking about 5 minutes. Is there any way to improve the search?
any suggestions are highly appriciate. Thanks in advance.

Thanks,
Suresh.

[mdmackillop]

Hi DRJ,
Thanks somuch for quick response.
I just did that and I am getting the error "Invalid use of Me Key word.
text = Me.SearchTextBox.text
any idea to rectify this bug?

Thanks,
Suresh.

Me is invalid because the code is no longer on the form. Substitute the form name in place of Me

[VBA]
text = SearchForm.SearchTextBox.text
[/VBA]

[mdmackillop]

Hi,
I forgot to mention one more thing.
I have over 100 servers and I have 2-300 nodes for each server. The search is taking about 5 minutes. Is there any way to improve the search?
any suggestions are highly appriciate. Thanks in advance.

Thanks,
Suresh.

If you add
Application.screenupdating = False
at the head of your code and
Application.screenupdating = True
at the end, this may help.
Regards
MD

[Jacob Hilderbrand]

Yea, you will have to use the form name instead of Me. If you are searching a lot, and if each search takes a few seconds or more, it is going to take a while.

There are some things that may speed it up. Insead of writing to each cell in the loop, build a range and write one time.

For example
[vba]
Worksheets("MySearch").Range("B8").Offset(i, 0).Value = "Whatever"
[/vba]
Change To
[vba]
If MyRange Is Nothing Then
Set MyRange = Worksheets("MySearch").Range("B8").Offset(i, 0)
Else
Set MyRange = Union(MyRange, _
Worksheets("MySearch").Range("B8").Offset(i, 0))
End If
[/vba]

Then at the end of the loop just change the value of MyRange.
[vba]
MyRange.Value = "Whatever"
[/vba]

Also make sure that there are no events triggering when you are changing the cells. You should start the macro with.

[vba]
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
[/vba]

And set them back to True at the end of your code. Application.ScreenUpdating = False could speed it up considerable, or maybe not. Depending on what is slowing you down. But it will be faster.

If you have a lot of formulas, turn calculations to manual at the start of you code and back to automatic at the end.

[vba]Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic[/vba]

[suresh]

Hi DRJ, mdmackillop and Team,
I dont know how to appriciate you guys.
Earlier to search for 100 records i had to wait for atleast 5 minutes. Now with your advice (Application.EnableEvents = False, Application.DisplayAlerts = False , Application.ScreenUpdating = False , Application.Calculation = xlCalculationManual,Application.Calculation = xlCalculationAutomatic ) I am able to search even 1000 records in less than 5 SECONDS. You guys really just grt at VBA. I am new to this VBA and thanks somuch for your quick help.
One more advice Could you please help me to close (Solved) this thread or should I go a head and continue my next thread in this(I have couple more things in this project)?
Once again thanks to all for help.

Thanks,
Suresh.

[mdmackillop]

Hi Suresh,
"Solved" is not working at the moment, so I've marked it manually.
Glad to be of help.
Regards
MD