PDA

View Full Version : Solved: Progress Bar help - method not found



suresh
03-06-2006, 01:15 PM
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
03-06-2006, 03:06 PM
Yes, just move "Sub FindServersByName()" to a Module and you won't have this probem.

suresh
03-06-2006, 03:59 PM
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
03-06-2006, 04:03 PM
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
03-06-2006, 04:23 PM
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


text = SearchForm.SearchTextBox.text

mdmackillop
03-06-2006, 04:31 PM
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
03-06-2006, 04:34 PM
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

Worksheets("MySearch").Range("B8").Offset(i, 0).Value = "Whatever"

Change To

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


Then at the end of the loop just change the value of MyRange.

MyRange.Value = "Whatever"


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


Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False


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.

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

suresh
03-07-2006, 12:07 AM
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
03-07-2006, 01:23 AM
Hi Suresh,
"Solved" is not working at the moment, so I've marked it manually.
Glad to be of help.
Regards
MD