[NB: This is a tutorial note created for members of my Excel Heaven Visual Basic Automation Club – but left open without password protection for viewing by interested non-members]
You’ve been using MS Excel and the Visual Basic Editor for some time now, since we started this series. I am quite aware that it is NOT easy for most of you to make sense of some of the new stuff being presented to you.
However, I want you to know that the human mind is very powerful and regardless of your background, you CAN learn to do ANYTHING you set your mind to.
In the course of developing myself in various fields of interest as a Multpreneur, starting from my 7 year stay in Guinness Nigeria, up till today as a Location Independent Multipreneur, I have discovered that DELIBERATE REPEATED exposure to ANY subject matter is a GUARANTEED way to develop familiarity with it, and ultimately proficiency IN IT.
Just keep going at these tutorials, and sooner than later, you’ll start wondering why you ever thought it would be hard to “get it”. Remember I’m ready to help anytime 
When I first encountered spreadsheet coding sentences in Lotus 1-2-3 macros written by an expatriate boss I had during my graduate training in Guinness in 1995, they looked like total gibberish to me!
But the results I saw them produce in form or automatic data handling and reports/charts generation, even to the point of printing without prompting, I KNEW I wanted to be able to control the spreadsheet application in that manner too.
My motivation stemmed from the fact that I hated the way many of the senior colleagues I was working with used manual calculators for hours, sometimes days, to prepare reports to be used for decision making.
Not only was it stressful for the person preparing the report, it was also a process fraught with risk of avoidable calculation errors arising from such stressful conditions under which it was being done.
I did not want to work that way.
Luckily for me, my expatriate boss, being the Training Coordinator, had a vision to influence a change in the way formal management reporting was being done in the company, using his apps.
One day we got talking about how spreadsheet automation he did could save time, effort and minimize errors, and he looked at me say “You can change the way things are done using this method.”
THAT day my mind was made up. I became a new man, and began sitting with him to watch as he worked, whenever I had free time.
Less than a year later, I was solving spreadsheet automation problems in Guinness Benin Brewery Edo state, where I was assigned.
In the years that followed, up until I quit the company to develop custom spreadsheet software for a living (as I do today), I built a reputation across the company for building custom apps (without being asked) that eliminated paper based data recording, analysis and report generation.
Among other benefits, my apps boosted productivity and made the lives of co-workers better. All of that did not go unnoticed, as I was rewarded with great career advancement opportunities.
You can achieve even better results in your workplace than I did in mine, by developing and applying your spreadsheet automation skills to the benefit your employer and/or clients.
You can do so with my help.
In my time I had NO one to look up to within or outside the company for guidance. Indeed when I quit my job in December 2001, to develop custom spreadsheet software for a living, I found no one else doing it, and many who I thought would understand told me I could not succeed with this idea of earning a living doing Spreadsheet Programming.
But what I saw happening in the Excel-VB industry in places like USA, Singapore, Canada, UK and Australia, convinced me I just needed to identify the right target audience and market;
Today, I enjoy the benefit of having done that, as I continue to attract buyers and clients for my products and development services respectively.
My success despite years of harrowing adversity and rejection derives from having a SOUND foundation in my chosen vocation.
The truth is I LOVE working with numbers and data, to establish trends, and extract meaning/insights for decision making. But I LOVE, even more being able to accelerate that process using a reporting application like MS Excel, which comes with its own in-built dynamic calculation engine.
If you want to succeed in Excel-VB Solutions Development, you need to build a sound foundation of understanding like I do
Mine has enabled me rapidly build myself up to adopt and adapt wisdom from some of the world’s foremost Excel-VB developers to create a unique range of solutions for my target audience.
A good place to start is getting a firm understanding of how Excel creates and manages macros
I’ve shown you how to use the macro recorder to record tasks you carry out as you work.
But if you do not understand how Excel creates, and manages the macros, you may have difficulty finding them as you create more in a particular workbook.
Here are simple tips to guide you:
1. When you create a macro for the first time, MS Excel will create a new code module in the Visual Basic Editor (VBE) under the Object Explorer
2. Open a new, blank workbook and press Alt+F11, to access the VBE. You will see that the” Object Explorer has not folders in it, and only the default worksheet objects (sheet1 etc) are visible there.
3. Now, click the macro recorder button and type in a name for a macro, then go to the workbook interface and type in an entry into a cell.
4. Return to the VBE, and click the STOP button to end the recording. Look into the Object Explorer and you will see that a yellow folder named “Modules” has been created there. Double click on it and it will reveal a code module named “Module 1”
5. While that workbook remains open, every single time you record a new additional macro, MS Excel will append it to the bottom of that same module 1.
6. However when you SAVE, then close and reopen the workbook, and record another macro, the macro recorder will open a new module incrementing based on the number for the last module. So if the last was Module 1, the new one after the workbook is reopened will be Module 2.
7. It is in that new module that the recorder will write all the code sentences for your new macro.
8. Note that it is not possible for you to control where the macro recorder puts your new macro. Instead you simply need to understand how it decides where to put it, as explained above, so that you can quickly easily find your macros at any point in time.
9. Incidentally, regardless of how many modules get created, you should have no difficulty locating your macros in order to edit or run them. All you need to is to call up the Macro dialog box, select the name of your macro from the list it will present you, of ALL the macros in that workbook.
10. Next week, I will discuss this with a practical example. For now, I recommend you open the workbooks I’ve sent you before now to study them based on what I’ve explained here.
RELATED