Hello Excellers, time for another #macromonday blogpost. In this tutorial we will look at VBA and workbooks in Excel. If you’re like me, you have a ton of Excel workbooks open simultaneously. And it can be tough to keep track of how many are open, especially if you’re working on a project with multiple workbooks. Luckily, there’s an easy way to count how many Excel workbooks are open using a macro. In this blog post, I’ll show you how to create a VBA macro that counts the number of open workbooks, and I’ll also share a few tips on how to use the macro. So read on to learn more!. This simple macro will display a message box to the user with a count of how many Excel workbooks are open.
So, first, let’s write some code, and tuck it away in our Personal Macro Workbook and we can re use that code whenever we need it. Let’s count how many open workbooks there are.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share three free Excel Tips on the every monthmonth and receive my free Ebook, 50 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.
How To Excel At Excel – Macro Mondays Blog Posts.
So, What Does The Macro Do?
This Macro uses the Workbooks.Count property which returns the number of open Excel Workbooks. For ease, we then get Excel to display the result of this in a handy message box using the MsgBox Function. This macro is so simple it hardly needs a walkthrough!
Step 1. Starting The VBA Macro To Count Open Workbooks.
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
When you decide to write an Excel macro, you need to choose where to store it. You have two choices.
- Store it in the current Excel workbook you are working on creating.
- Save the macro in your Personal Macro workbook.
As I already know that this macro is a handy one I will store in in my Personal Macro workbook,
Step 2. Name Your Macro
The first step in writing the VBA macro to count open workbooks is to name macro. Stert by typing Sub then the name of the macro. In this example I keep it simple with CountMyWorksbooks(). Notice once you hit enter on the name of the macro Excel automatically inserts the End Sub line of code. Any other lines of VBA code should be written between these two lines.
Step 3. Use The Message Box Function
The only line of code required is the message box function in this example to DISPLAY information to the user. The syntax of the MsgBox is simple
MsgBox(prompt[, buttons] [, title] [, helpfile, context])
The only required part of the syntax is the prompt. All others are optional. Indicated by the [ ] brackets. In this example it is the result of the Workbooks.Count calculation.
Step 4. Ending The Macro
Once the MsgBox has displayed the value, then Macro ends.
This is a really short macro, but surprisingly handy. We could go a little further and customise the message box. We have only just used the minimum and required part of the function. So we can go ahead and type a title to be displayed to give a little more context for the user.
By amending the code slightly and adding in the Title part of the code,we can add and display some context. When we run the macro again, it makes a little more sense. ( You could type any text for your users to see in the Title Part of the MsgBox function).
