The first step in any assignment is still to understand what the customer wants and to build a “mock-up” of the application, which we teach in our introduction courses. For a client the easiest part if to dream up what they want to learn from an Excel™ worksheet, which therefore forms the core of the work assignment. Depending on the nature of the application you may already have access to the data, or finding the underlying data may be the next challenge you have to overcome.

Next are the specific business calculations, and here you need to be careful that you use the correct set of formulas to get the application functioning correctly. Create tests on a separate temporary worksheet to explore the calculations with different sets of data values that you review with the client, to make sure that what you end up with is correct. Save these calculations – over time you build a library of “typical” calculations that can streamline future assignments and thereby make you more efficient as a problem solver.

You must explore all ways to get at the data that may be from external sources, and how that data may be transformed into worksheet tables for Excel™-based access. While you may now have your biggest problems under control, there are still potential design challenges that you need to resolve before you develop the worksheet. In most projects the first week will be focused on the analysis of the work, and possibly another week to sort out the data.

The next step in the project is to design the overall process in which Excel™ worksheets will be used. There is a world of difference building a system for a one-person operation and one that must collate and integrate from several sources and/or multiple contributors. The flexibility of Excel™ can tempt contributors to become creative, and in doing so the Excel™ logic is easily corrupted, so you must design a mechanism that minimizes the risk of data corruption by segregating the “interface” worksheet for each of the contributors.

In a VBA-managed environment we can create processes to transform external data into “data collection” worksheets that become the internal database for your process. As you identify potential exposures to external data sources that may need validation, you can make notes on how to develop validation logic, and you can also start to design the user documentation that explains the proper use of the application. Once you finalize the worksheets layout in the analysis and design stages you can even take screen images that can be included in the documentation so that prospective users can follow the end-to-end application processing cycle.

To learn more about doing that, consider “Business Management Training – Office Management Courses” in the “MS-Office Applications Training” section that explains how to grab images that can be edited and then inserted into your documentation. If you provide good design and operation documentation you may require 2 - 3 weeks to complete this phase of the work, but it saves time in the end if you can avoid having to go back and change the worksheets later.

Based on your design, the next step in the project is the development of the actual Excel™ logic. This can become the easiest part of the work once you develop the experience and Excel™ quirks become familiar to you and are easily avoidable “gotchas”. Remember that the VBA does not have to duplicate what you can do in a basic Excel™ worksheet, because formulas can be calculated under VBA control: the programming you add in VBA will be the heavy-duty processing while custom reports may simply be based on the results worksheet, and you can often complete the report logic in a simple formula-based worksheet structure to make it easier to adapt when needs change.

As before, the client already has a guide to review so that they can provide feedback on what they may want changed to better suit their operations. It is much easier to get early feedback than to wait until the solution is completed, because going back and changing is always a potential source for errors (and in particular in VBA logic). The beauty of Excel™ in populating the right cells with copies of tested formulas will still be an asset, but since the VBA is more rigid you have to be conscious about how you keep as much of that flexibility in play as possible.

As far as effort is concerned, we average 1 day per major bundle of functionality coded in VBA. Some macros can be quite involved with many embedded processes that take longer to complete and integrate. Allow 1 – 2 days per interface file that must be converted to an Excel™ worksheet format for processing. In addition, we estimate a day for a simple worksheet to 3 days for a complex worksheet, and within a project you may develop multiple workbooks with multiple worksheets, so that provides a guideline of how long an average development stage is.

The final step in the project is to thoroughly test the results against a set of known expected outcomes to make sure your project is accurate and ready for use. Unfortunately many clients want to rush this part of the project, but it is critical that you confirm the correct operation and verify that all of the calculations are correct. You may need a scientific calculator that has a complete set of mathematical and business calculation functions to review the results of each calculation: these tools are no longer a major cost.

For most worksheets testing can be completed in less than a day, but finding a bug can mean more than a fair effort to narrow down the origin of specific problems. As a general rule estimate a day for each worksheet for testing, as well as 1-2 days for each major macro (including data conversion code). After the testing is completed and satisfactory, finalize the worksheets by placing the client company identification on the worksheets, including the title and ownership identification. Leave this to the end, so that there is no risk that people use incomplete worksheets that you may have saved as a precaution while you were developing the final product.

How do you market these services? Start by building a portfolio of projects you complete, but only use the test version and make sure the customer is not identified in those results. Between projects done while you learn and the projects you do for customers it will not be difficult to build a portfolio of results that you can insert in (a) presentation binder(s). As you expand your inventory you may create separate presentation binders for different types of applications. Based on the kinds of project you worked on define the true customer benefits that you delivered, and especially challenges you resolved for the customer: those challenges are what prospects can identify with, and if they want to know how you plan to address those the portfolio demonstrates that you have indeed solved those challenges for other customers.

One of the benefits to you is that when you create such an inventory the work becomes less and less daunting, as you can refer to previous examples (not only the reports, but the test workbooks as well). You build a core competency in your initial work, while over time you have the opportunity to expand and take on other challenges that keep you on your toes. You can market yourself as a freelancer and charge fair hourly rates (or even project rates) and over time you can sell solutions (based on the test workbooks) that you can adapt to new businesses’ needs to show that the development risk is minimal given your organized approach.

Learning Formats       BEBC_VBA

This course is currently available in a classroom setting (public or company private) with approximately 30 contact hours. Students are encouraged to bring their own laptop with MS-Office™ software installed, so that they can start building a library of Excel™-based business solutions. The goal of this course is to provide a close simulation of what it is like to sell your skills to a prospective customer in order to discover what your true passion is.

PDF – Certificate Of Completion

Each course offers a certificate of completion that identifies the course, the student, and a brief description of the course. To receive a certificate the student must have attended at least 80% of the course sessions. This personalized certificate is forwarded to the student by Email.

PDF – Course Notebook

Each course includes a notebook in PDF format that provides the minimum knowledge the student must master in order to obtain the certificate. In the notebook you will find references to other study materials. Students receive the notebook by Email when their registration is confirmed.

PDF – Program Overview

An overview of this study program can be downloaded from the website by right-clicking on the program link on the enquiry page.

PDF – Current Training Schedule

A list of upcoming training sessions can be downloaded from the website by right-clicking on the schedule link on the enquiry page.

Registration – Service Providers

To register for any training course please look on the enquiry link page of your service provider (from where you accessed this website). On the page you will find a registration request form where you can order the course that you are interested in. The availability dates will be provided to you, along with payment instructions if you decide to go ahead.