Progress Bar - extend run time

[funkymaggot]

Hi All,

My first time here and a total newbie to Macros/VBA and the like. Although I have been dabbling for a few weeks and have come on leaps and bounds. I am looking for some help regarding a progress bar I have added to a file at work. My apologies if this is long-winded.

I have 1 excel file which is a direct download from SAP and another excel file which takes the information contained in the download and turns it into meaningful data.

When I run the macro the following happens:

1) Unwanted data (columns) from the download are removed and a filter is added to one of them.
2) The data is filtered by a set of criteria then copied and pasted onto a worksheet in the breakdown. There are 3 bits of criteria each pasted onto 3 different sheets.
3) The data pasted onto the 3 sheets then does a lookup to another file to autofill supplier information.

All together the process moves anywhere from 100 to 2000 rows of data (depending on when in the month the download is done) and does a lookup of the same amount.

I added a progress bar using the standard MS bars and the code suggested by johnske successfully. However, the bar only runs while the macro is sorting and moving the data, then freezes on the screen while the lookup is happening.

Here's the crunch question - how, if possible at all, do I get the progress bar to run throughout the lookup process as well??

I hope someone can help,

Kind regards,

Mick.

[Simon Lloyd]

I'm not sure as i don't have the expertise to really point you in the right direction but a couple of things may help, [VBA]With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
Application.EnableEvents=False[/VBA]at the start of your code and [VBA]With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
Application.EnableEvents=True[/VBA]at the end

[funkymaggot]

Thanks for the response Simon,

I'll give it a try.

I don't have any expertise at this at all, so anything that might help is gratefully accepted. I am learning this as I go by downloading other peoples helpful ideas and hashing them around as best I can.

I'll post back if it works.

Mick.

[Bob Phillips]

Can you post a link to johnske's suggestion so that we can try it?

[Simon Lloyd]

also try this http://vbaexpress.com/kb/getarticle.php?kb_id=792 its by Mvidas (Matt)

[funkymaggot]

Can you post a link to johnske's suggestion so that we can try it?

Unfortunately, I can't post links yet, as I haven't made enough posts. You can find it if you search for progress bars using excel and johnske's name in the search criteria.

Thanks for any help you can give,

Mick.

[Simon Lloyd]

Heres the Link

[funkymaggot]

Simon,

Many thanks for your help, unfortunately, the 1st idea didn't work. I couldn't get the IE progress bars to work at all, but I will put that down to my lack of knowledge and experience.

Thanks anyway,

Mick.

[Bob Phillips]

Can you post us a workbook to try with?

[jwise]

Assuming I am calling a subroutine and sending it arguments, is it a two-way passing of data or is it better to use "public variables" if the sub is going to modify this data (logically send it back)? For example:

[vba] Dim abc as Strng
Dim xyz as String
...
Call Sub1(abc, xyz)
...

Sub1 (abc as String, xyz as String)
...

abc = abc & "This is an added string of characters"
...

End Sub [/vba]

This is important DEPENDING on the way VBA allocates storage for variables. The alternative would be:

[vba] Public abc as String
Public xyz as String
...

Call Sub1
...[/vba]

[Bob Phillips]

You should have started your own thread.

It can be one way or two way, depending as to whetrher you pass the parameter by value or by reference.

Try this

[vba]

Sub TestCalls()
Dim abc As String, xyz As String

abc = "abc"
xyz = "xyz"
Call Sub1(abc, xyz)
MsgBox abc & vbNewLine & xyz

abc = "abc"
xyz = "xyz"
Call Sub2(abc, xyz)
MsgBox abc & vbNewLine & xyz
End Sub

Private Sub Sub1(ByRef abc As String, ByRef xyz As String)

abc = abc & "This is an added string of characters"
xyz = xyz & "This is an added string of characters"
End Sub

Private Sub Sub2(ByVal abc As String, ByVal xyz As String)

abc = abc & "This is an added string of characters"
xyz = xyz & "This is an added string of characters"
End Sub
[/vba]

[jwise]

I goofed. I thought I was creating a new thread, and I do apologize for any inconvenience I may have caused.

Consider the issue closed. I do thank xld for his suggestion.

[funkymaggot]

XLD,

Sorry for the delay in replying to the thread, I have been away with work for a couple of days and the hotel didn't have a network I could connect to (not be using them again!!).

I will try and upload the files tomorrow when I get back to work.

Thanks for the help guys, it's much appreciated.

Mick.