Checking a Model List against the Worksheets in the Workbook

[JoelG]

I have a macro that works fairly well for smaller projects, but when the list of models is longer than ~30, the loop to check for the worksheets with the model names gets really slow. My current project has 72 models, and every time I want to do a model list check, I have to wait multiple minutes. Does anyone have any advice to speed up this process?

Please Login or Register  to view this content.

[CK76]

Why not create global object (ex. Scripting.Dictionary), which holds all existing worksheet names when the workbook is opened.

Then use it to check if Model Name exists.

But what should happen if there isn't sheet with model name? Should it be created? Or just leave column B blank?

It'd be easier to help you if you upload sample workbook that demonstrates your need and set up.

[Kaper]

Dictionary is a good idea.
Even simple array could be used for it.
See sample code below (I assumed that there are two options: "Created" or just empty cell in column B).
See the code:

Please Login or Register  to view this content.

[JoelG]

Thank you both for your advice in this case. I actually reordered the logic to go worksheet by worksheet and check the rows and it significantly improved the speed.

Please Login or Register  to view this content.

I am relatively inexperienced in VBA so I am not sure how the Scripting.Library function works. Can someone elaborate on that function?

[CK76]

On second thought, I'd recommend using IsObject to check if sheet exist in workbook (trick I learned from Marc L @ chandoo.org forum).

And using array to loop through range. Something like below.

Please Login or Register  to view this content.

[JoelG]

CK76,
Awesome! Compared to both of my previous methods, your code is instantaneous. Thank you so much for sharing!

[CK76]

You are welcome and thanks for the rep