I received my paperback copy of Microsoft Excel 2019 VBA and Macros, by Bill Jelen and Tracy Syrstad, on December 31, 2018. I finally managed to work my way through it cover to cover and highly recommend this book for anyone wanting to learn how to be more productive with Excel using VBA.
This book presents a wide range of topics where you will learn how to take advantage of VBA and the new features available in Excel 2019. With the assumption that you already know how to use Excel, this book helps you to understand and take advantage of the many capabilities and features of VBA itself to enhance your experience as a user and to create effective and efficient applications.
If you want to increase your productivity with Excel 2019, then I highly recommend getting your copy of Microsoft Excel 2019 VBA and Macros. This is another welcome addition to our growing library of Excel books.
ISBN-13: 978-1-5093-0611-4
ISBN-10: 1-5093-0611-0
Pages: 585
Accompanying Excel workbooks with code, data sets, and bonus macros are available online for download.
I recently discovered a website titled “Excel Off The Grid” while doing some research for one of our projects. The post I read was well written, succinct, and the examples were easy to follow. Although our application required a little more depth, the information presented would be more than sufficient for most people wanting to use the features as discussed.
As I often receive requests from web masters to post links to their Excel based sites, I am quite particular on those that make the list. I will only feature web sites that I have learned to trust over time and the information provided serves a greater benefit to the Excel community.
After reviewing a number of posts on “Excel Off The Grid“, I found the information presented to be relevant, helpful, and some not readily found in your typical Excel books and distinguishes this site from others.
Excel Off The Grid is insightful and worth the time to learn a little more about the world’s most powerful spreadsheet application.
Quite some time ago I created a backend interface to serialize and record data for labels printed through Crystal Reports as generated from our ERP system. Unfortunately, the ERP system makes no provision to enter or update all of the key information required to support traceability of the labels after they are printed.
To solve this problem, I used Excel VBA to create a front end label manager to edit or update labels with the relevant information. The window appears modally when the “Manage Labels” button is clicked on the ASN E2 Data worksheet (pictured below), allowing us to continue working with our workbook as required.
I could have written the front end as an independently managed application, however, it is a natural extension of other features and capabilities supported by the workbook in which the VBA code resides. No code is presented in this post, however, some familiarity with Excel VBA and User Forms is assumed.
When the “Manage Labels” button on the worksheet is clicked, a “Label Details” window appears as pictured above. After confirming or selecting the Server Location and clicking Connect, a Customer Code is selected from the ComboBox. We then click the “Get Labels” button to load the labels for that Customer accordingly. More granular search capabilities are also provided.
The core functionality supported by the front end is the ability to edit the data associated with any given label. Text boxes are used to edit data for the fields indicated. A check box next to each field is used to make the field “sticky”.
Note that although each field can be set independently, all “sticky” checkboxes can be set or cleared by pressing the “Set Sticky” or “Clear Sticky” button respectively.
Sticky Fields
Normally, text boxes are updated when moving from one record to the next. However, there may be times when we want our data to persist or carry over from one record to the next so we only have to enter it once.
For example, at the time of printing and when parts are being produced, we can’t possibly know the Packing Slip number in advance. If multiple containers (each having a uniquely serialized label) are shipped, we can simply make the Packing Slip “sticky” and update the records without having to re-enter the packing slip for each individual label. Similar logic applies to the remaining fields.
The “Reset” button is used to overwrite the TextBox with data from the original record. To do this, we take advantage of the “Tag” property of the TextBox. When the data is read from the database, a copy of the data for each field from the original record is stored in the “Tag” for each TextBox. The “Tag” can store up to 2048 characters and is more than sufficient for our purposes.
Functionality is also provided to Swap the data between the current field value and the original data value of the field by using the up/down arrow button immediately to the left of the “Reset” button.
Note that all fields can either be reset to the original data contained in the record or cleared entirely by pressing the “Reset Fields” or “Clear Fields” buttons on the Edit page respectively.
Carry On
This type of functionality is not typical for most applications though it is not necessarily unique. I recall seeing a “record level” Carry ON type of functionality years ago where the data from the previous record would carry over to the next record and edited as required. However, this only applied to new records as they were being entered.
Risks
If the user “forgets” that a sticky field is set, it is possible that some data may be changed unintentionally. By default, all of the check boxes for the sticky fields are clear (off) when the edit page is presented and must be turned on intentionally by the user.
On the other hand, consider the risks associated with data entry where the user would have to enter the same data multiple times for a larger number of records.
Editing Multiple Records (Global Update)
Though not presented as part of this post, it is also possible to select multiple records from the main “label display area” and globally apply changes to “common fields”. Of course, “global” edits to multiple records presents it’s own challenges and risks and is the topic worthy of it’s own post.
Final Thoughts
You may already be using Sticky fields or perhaps considered making this capability available to your users. There are certainly times where this type of functionality may not be desirable and certainly where the risks of “contaminating” your database may be high. You will note that we did not provide the ability to edit all of the fields for a given record for this very reason.
The efficiency and effectiveness of an application extends from the underlying code to the actual person using the application and is one of the reasons why this was included as part of our ongoing Lean Code series. Saving the user from having to re-enter duplicate data into multiple fields across large record sets can save a significant amount of time and effort and minimizes the risk of error where “exact” duplication of data is a necessity.
Not too long ago, I was approached by a systems consultant to consider writing an Excel solution to create a weekly production schedule for one of his clients. The reason for using Excel will become clearer in a moment.
The current process
Sales representatives submit a unique Excel spreadsheet / file for each customer order. All orders are saved to a common subdirectory on the company server. Every Monday, an administrator then opens each spreadsheet and extracts information for each individual order and manually enters the relevant data into a “master” spreadsheet. After formatting the entries, the production schedule is then created.
In this case, the client was typically managing 200 to 250 unique orders per week and the time required to create the master spreadsheet could range anywhere from 4 to 6 hours. Since the sales team was already acclimated to using Excel, the client was reluctant to consider an alternative order management process.
The Solution
I agreed to take a look at the application and after an hour of writing some Visual Basic for Applications (VBA) code, I produced a working model that automatically processed over 200 files and created a production schedule in less than 28 seconds! I also provided functionality to allow access to each file directly from the master as well as a few other bells and whistles!
The client was impressed by (couldn’t believe) the results and, as with any successful effort, requested additional functionality that they also never thought was possible.
Could this be you?
Although Excel is by far one of the most popular spreadsheets on the market, it is perhaps still one of the most underutilized desktop application in business today. Although I can only speculate, I think one of the reasons for this is the overwhelming thought of having to read an ever-growing volume of books having an ever-increasing number of pages with each new release.
I would also suggest that some of Excels functions such as pivot tables and look up capabilities are so powerful that people just assume they must be complicated when this really isn’t the case at all. I can only suggest trying them before making a judgement.
While a number of Excel solutions exist in the form of templates ranging from very specific applications such as our free Excel templates for OEE to the more general and commonly used applications such as dashboards, there are many more applications where “canned” solutions just don’t exist.
Naturally, because many people have access to Excel, they find themselves creating their own solutions “on demand” as was the case with the production schedule above.
An even greater concern is the number of “orphan’ spreadsheets that exist outside the scope of any managed data infrastructure. The danger here is that these solutions may unknowingly compromise the integrity of the current management system.
I contend that orphan solutions eventually become the hidden or “unknown” extension of the existing infrastructure and present a real opportunity yet to be uncovered by many organizations.
“Homegrown” Solutions
For cases where customization is required, someone in the organization is usually tasked with creating a spreadsheet solution. Unfortunately, these same individuals are not necessarily as capable as we may first think. I discussed this concern to some degree in Lean Office with Excel and VBA and also became a topic of discussion at Daily Dose of Excel’s Learn VBA to be Lean.
The result is often a solution that “appears” to meet the requirements – at least on paper. I have found that these same spreadsheet solutions seldom take advantage of even the simplest functions that Excel has to offer including the most basic arithmetic functions such as SUM as suggested by the animated graphic that accompanies this post.
Although the articles referred to above focus on Visual Basic for Applications (VBA), there are many other very powerful functions that can be used to make better use of Excel’s functionality and increase it’s value in terms of providing effective and efficient solutions.
Getting the Knowledge
There are a number of ways to learn Excel ranging from formal training, reading books, and, of course, the internet. Although I have been using spreadsheets since their inception, I still consider myself to be a student in many respects.
With a little knowledge in hand, I recommend visiting the sites that I have learned to trust as listed on our Websites (Excel) page. Each of these sites present powerful solutions to many common problems. They also offer full explanations and practical examples that you can use in your own spreadsheets.
Excel is best learned by doing. Having an application to work on and exploring solutions that may be available to you can be a most rewarding experience. This is especially true when they can be implemented and put to good use with immediate results.
Getting to Lean
Obviously all of this learning isn’t going to happen over night, however, you will be surprised how much can be learned in a relatively short period of time. The time saved by creating a more efficient solution in many cases will offset much of the time spent learning.
The initial challenge is to determine what spreadsheets solutions are in existence and why. The number may surprise you. If your company has an IT department, I would task them with this assignment.
As demonstrated by the case study presented earlier, the hours saved can be significant. The solution I created offers the added advantage of being able to refresh the production schedule at any time, in real-time. In other words, it is now an everyday event, not just a “Monday” event.
Your skills will improve over time and with each application. As you increase your learning, old solutions can be upgraded to reflect even more functionality adding even more value. The more comfortable you become with Excel, the more useful it will be as a tool for data analysis in other areas of the company, including lean initiatives.
I continue to be surprised by the quality of solutions that some companies have created simply because they took the time to understand and learn more about Excel for themselves. In some cases companies have hired outside support to help them in the process.
There are pros and cons for how spreadsheet solutions are developed and you should consider them as you look to develop your own solutions. Of course you can always be the Excel Hero by taking the time to learn Excel for yourself. The added benefit is how much more you may learn about other processes in your company. Finally, you may just be surprised how in demand these skills are.
Speaking of hero’s, I recently added ExcelHero.com to our list of trusted sites and to Daniel Ferry’s credit, it also inspired the title of this post.
VBA for Excel has once again provided the opportunity to demonstrate some basic lean tenets. The methods used to produce the required product or solution can yield significant savings in time and ultimately money. The current practice is not necessarily the best practice in your industry. In manufacturing, trivial or minute differences in methods deployed become more apparent during mass production or as volume and demand increases. The same is true for software solutions and both are subject to continual improvement and the relentless pursuit to eliminate waste.
Using Excel to demonstrate certain aspects of Lean is ideal. Numbers are the raw materials and formulas represent the processes or methods to produce the final solution (or product). Secondly, most businesses are using Excel to manage many of their daily tasks. Any extended learning can only help users to better understand the Excel environment.
The Model:
We recently created a perpetual Holiday calendar for one of our applications and needed an algorithm or procedure to calculate the date for Easter Sunday and Good Friday. We adopted an algorithm found on Wikipedia at http://en.wikipedia.org/wiki/Computus that produces the correct date for Easter Sunday.
In our search for the Easter Algorithm, we found another algorithm that uses a different method of calculation and provides the correct results too. Pleased to have two working solutions, we initially did not spend too much time thinking about the differences between them. If both routines produce the same results then we should choose the one with the faster execution time. We performed a simple time study to determine the most efficient formula. For a single calculation, or iteration, the time differences are virtually negligible; however, when subjected to 5,000,000 iterations the time differences were significant.
This number of cycles may seem grossly overstated, however, when we consider how many automobiles and components are produced each year then 5,000,000 approaches only a fraction of the total volume. Taken further, Excel performs thousands of calculations a day and perhaps even as many more times this rate as numbers or data are entered on a spreadsheet. When we consider the number “calculations” performed at any given moment, the number quickly grows beyond comprehension.
Testing:
As a relatively new student to John Walkenbach’s book, “Excel 2003 Power Programming with VBA“, speed of execution, efficiency, and “Declaring your Variables” have entered into our world of Lean. We originally created two (2) routines called EasterDay and EasterDate. We then created a simple procedure to run each function through 5,000,000 cycles. Again, this may sound like a lot of iterations but computers work at remarkable speeds and we wanted enough resolution to discern any time differences between the routines.
The difference in the time required to execute 5,000,000 cycles by each of the routines was surprising. We recorded the test times (measured in seconds) for three separate studies as follows:
Original EasterDay: 31.34, 32.69, 30.94
Original EasterDate: 22.17, 22.28, 22.25
The differences between the two methods ranged from 9.17 seconds to 8.69 seconds. Expressed in different terms, the duration of the EasterDay routine is 1.39 to 1.46 times longer than EasterDate. Clearly the original EasterDate function has the better execution speed. What we perceive as virtually identical systems or processes at low volumes can yield significant differences that are often only revealed or discovered by increased volume or the passage of time.
In the Canadian automotive industry there are at least 5 major OEM manufacturers (Toyota, Honda, Ford, GM, and Chrysler), each producing millions of vehicles a year. All appear to produce similar products and perform similar tasks; however, the performance ratios for each of these companies are starkly different. We recognize Toyota as the high velocity, lean, front running company. We contend that Toyota’s success is partly driven by the inherent attention to detail of processes and product lines at all levels of the company.
Improvements
We decided to revisit the Easter Day calculations or procedures to see what could be done to improve the execution speed. We created a new procedure called “EasterSunday” using the original EasterDay procedure as our base line. Note that the original Wikipedia code was only slightly modified to work in VBA for Excel. To adapt the original Wikipedia procedure to Excel, we replaced the FLOOR function with the INT function in VBA. Otherwise, the procedure is presented without further revision.
To create the final EasterSunday procedure, we made two revisions to the original code without changing the algorithm structure or the essence of the formulas themselves. The changes resulted in significant performance improvements as summarized as follows:
For integer division, we replaced the INT (n / d) statements with a less commonly used (or known) “\” integer division operator. In other words, we used “n \ d” in place of “INT( n / d)” wherever an integer result is required. This change alone resulted in a gain of 11 seconds. One word of caution if you plan to use the “\” division operator: The “n” and “d” are converted to integers before doing the division.
We declared each of the variables used in the subsequent formulas and gained yet another remarkable 11 seconds. Although John Walkenbach and certainly many other authors stress declaring variables, it is surprising to see very few published VBA procedures that actually put this to practice.
Results:
The results of our Time Tests appear in the table below. Note that we ran several timed iterations for each change knowing that some variations in process time can occur.
EasterDay = 31.34375
Original Code uses INT( n / d ) to convert Division Results
EasterSunday = 20.828125
1. Replaced INT ( n / d) with (n \ d)
EasterDate = 22.28125
Original Code – Alternate Calculation Method
Re-Test to Confirm Timing
EasterDay = 30.9375
Original Code uses INT( n / d ) to convert Division Results
EasterSunday = 20.921875
1. Replaced INT ( n / d) with (n \ d)
EasterDate = 22.25
Original Code – Alternate Calculation Method
Re-Test to Confirm Timing
EasterDay = 30.90625
Original Code uses INT( n / d ) to convert Division Results
EasterSunday = 21.265625
1. Replaced INT ( n / d) with (n \ d)
EasterDate = 22.25
Original Code – Alternate Calculation Method
Re-Test to Confirm Timing
EasterDay = 31.078125
Original Code uses INT( n / d ) to convert Division Results
EasterSunday = 9.171875
2. Variables DECLARED!
EasterDate = 22.1875
Original Code – Alternate Calculation Method
Re-Test to Confirm Timing
EasterDay = 31.109375
Original Code uses INT( n / d ) to convert Division Results
EasterSunday = 9.171875
2. Variables DECLARED!
EasterDate = 22.171875
Original Code – Alternate Calculation Method
The EasterSunday procedure contains the changes described above. We achieved a total savings of approximately 22 seconds. The integer division methods used both yield the same result, however, one is clearly faster than the other.
The gains made by declaring variables were just as significant. In VBA, undeclared variables default to a “variant” type. Although variant types are more flexible by definition, performance diminishes significantly. We saved at least an additional 11 seconds simply by declaring variables. Variable declarations are to VBA as policies are to your company, they define the “size and scope” of the working environment. Undefined policies or vague specifications create ambiguity and generate waste.
Lessons Learned:
In manufacturing, a 70% improvement is significant; worthy of awards, accolades, and public recognition. The lessons learned from this example are eight-fold:
For manufacturing, do not assume the current working process is the “best practice”. There is always room for improvement. Make time to understand and learn from your existing processes. Look for solutions outside of your current business or industry.
Benchmarking a current practice against another existing practice is just the incentive required to make changes. Why is one method better than another? What can we do to improve?
Policy statements can influence the work environment and execution of procedures or methods. Ambiguity and lack of clarity create waste by expending resources that are not required.
Improvements to an existing process are possible with results that out perform the nearest known competitor. We anticipated at least being able to have the two routines run at the similar speeds. We did not anticipate the final EasterSunday routine to run more than 50% faster than our simulated competitive benchmark (EasterDate).
The greatest opportunities are found where you least expect them. Learning to see problems is one of the greatest challenges that most companies face. The example presented in this simple analogy completely shatters the expression, “If it ain’t broke, don’t fix it.”
Current practices are not necessarily best practices and best practices can always be improved. Focusing on the weaknesses of your current systems or processes can result in a significant competitive edge.
Accelerated modeling can highlight opportunities for improvement that would otherwise not be revealed until full high volume production occurs. Many companies are already using process simulation software to emulate accelerated production to identify opportunities for improvement.
The most important lesson of all is this:
Speed of Execution is Important >> Thoughtful Speed of Execution is CRITICAL.
We wish you all the best of this holiday season!
Until Next Time – STAY Lean!
At the onset of the Holiday project, the task seemed relatively simple until we discovered that the rules for Easter Sunday did not follow the simple rules that applied to other holidays throughout the year. As a result we learned more about history, astronomy, and the tracking of time than we ever would have thought possible.
We also learned that Excel’s spreadsheet MOD formula is subject to precision errors and the VBA version of MOD can yield a different result than the spreadsheet version.
We also rediscovered Excel’s Leap Year bug (29-Feb-1900). 1900 was not a leap year. The leap year bug resides in the spreadsheet version of the date functions. The VBA date function recognizes that 29-Feb-1900 is not a valid date.
If multiple containers (each having a uniquely serialized label) are shipped, we can simply make the Packing Slip “sticky” and update the records without having to re-enter the packing slip for each individual label. Similar logic applies to the remaining fields.
The “Reset” button is used to overwrite the TextBox with data from the original record. To do this, we take advantage of the “Tag” property of the TextBox. When the data is read from the database, a copy of the data for each field from the original record is stored in the “Tag” for each TextBox. The “Tag” can store up to 2048 characters and is more than sufficient for our purposes.
