[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

Excel: How to loop through entire workbook performing the same Sub

Hello, I'm working with Bloomberg data in excel, and have a vba program that will effectively force refresh bloomberg data, allow it to load, all that.  My issue is that I'm unable to have the program perform the same Sub on every sheet in a workbook.  I've tried numerous solutions I found on the internet, but none seem to loop through each worksheet.

My request:  My goal for this program is to have a Sub loop through each worksheet and perform the BloombergRefreshLinks Sub and its accompanied sub procedure ProcessData.
0
Caleb Harper
Asked:
Caleb Harper
  • 3
  • 2
1 Solution
 
SteveCommented:
OK, there needs to be a bit of work done to tidy up your code there.
For example you do not need to use Call as often.

        Call Range("B2").ClearContents
'is the same as
        Range("B7").ClearContents

Open in new window


To do all sheets in the workbook you need to be explicit in the worksheet you are working with.
An example of how to do this would be:

Sub ClearSomeRanges()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    ws.Range("B2").ClearContents
    ws.Range("B7").ClearContents
    ws.Range("A10:K10").ClearContents

Next ws

End Sub

Open in new window

Note how now it will cycle through all worksheets, one at a time.
It will change the object ws each time, then using ws.range will specify the range on the worksheet.

This is the basis of how you will need to modify your code to cycle each sheet.
Other subs may also need to work with the worksheet.
This can be done by creating the Dim ws As Worksheet outside the code at the top and calling it Global:
Global ws As Worksheet

Sub ClearSomeRanges()

For Each ws In ThisWorkbook.Worksheets

    ws.Range("B2").ClearContents
    ws.Range("B7").ClearContents
    ws.Range("A10:K10").ClearContents

Next ws

End Sub

Open in new window


Do not use selection and select, delete both to create a one line joined by one dot.
try to just call things by their range names:
                 Call Range("P2").Select
                    Selection.Copy
                 Call Range("B2").Select
                    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False

'will become
                 ws.Range("P2").Copy
                 ws.Range("B2").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False

Open in new window


Code which selects is always slow and almost always unnscessary.
If you see something such as ActiveWorkbook.Worksheets("Tickers").Select try to understand why it is there. If it is there to leave the user viewing the sheet at the end then it is useful. Otherwise it is likely better to dim a variable as the Worksheet and then work with it that way.

I understand there is a lot to take in there, so feel free to ask for clarrification on what is not clear.
If you still are having dificulties I can re-code the whole lot, but you may find it more useful to have a go yourself with the direction above. Up to you.

ATB
Steve.
0
 
Caleb HarperAuthor Commented:
Hello, thanks so much for your help, I feel very close to solving this thing!  I just have a couple issues left to fix:

What I've changed:  I've tidied up the code, using your advise.  I've also consolidated all of the code into one module.  

Program goals now:  The program needs to do the following:                        
For each worksheet, execute Call "RefreshStaticLinks1234", wait for x seconds for data to load, execute ProcessData1234 sub-procedure until data is loaded, then go to the next ws and repeat.

Problem:  The program isn't looping correctly through each sheet because of two reasons (at least, what I think they are):  Application.OnTime isn't applying to each ws variable within the For Each loop, and the sub-procedure ProcessData1234 isn't executing at the correct time for each worksheet.  Currently, the program is performing ProcessData1234 only on the last worksheet.

Please note that RefreshDataBB module is the one I'm actively working on.
WIP-8.08.2014TEST.xlsm
0
 
SteveCommented:
I cannot find the code for "RefreshEntireWorksheet"
Is this in another workbook?

To understand where it is failing, will need the "RefreshEntireWorksheet" code too.

Ta
Steve.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Caleb HarperAuthor Commented:
Steve,

"RefreshEntireWorksheet" is a function contained in Bloomberg's excel add-in library and is password protected, so unfortunately I don't think I could find the code for you unless you know how I could access the password-protected referenced module.

However, I don't think it should prevent you from diagnosing this problem (hopefully at least).  "RefreshStaticLinks" is executed for every worksheet correctly, every time.  However, the program skips over the "ProcessData1234" sub and immediately repeats "RefreshStaticLinks1234" for the next worksheet.  

I think the issue is with the "Application.OnTime" line, I think it is setting a application-wide timer without pausing the code as well.  Unfortunately, Bloomberg's links cannot update if any macro code is running.  Is there any way to pause code in an Application.OnTime manner within a loop?
0
 
SteveCommented:
Caleb,
Hi there, the issue is that the macro is dependent upon the Bloomberg Addin.
I do not have the addin, and thus we do not have access to the code within it to test or change where nescessary.
Adding multiple sheets to the single sheet arcitecture will need changes to the hidden code.
This change, though challenging if we had access to the addin, is practically impossible without it.

There are other programming experts in the community and they have proved me wrong in the past when I think something may not be acheivable. So I tend not to say 'it can't be done' as some here may prove it to be possible. I was hoping that another expert would jump in to save the day.

 Sorry I cannot be of more assistance,
 I don't like to leave a question unanswered,
 But this one is beyond me without the code in the addin.

ATB
Steve.
0
 
Martin LissRetired ProgrammerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now