Excel Archives | Eppert Consulting

12/09/2023

Office Scripts vs. VBA: Programming Excel for the Web and Desktop

Stylized Excel Logo

Microsoft Excel has long provided the ability for users to write “macros”—pieces of programming code that let people automate both simple and advanced tasks. The official name of these macros is Visual Basic for Application (VBA) because they use Microsoft’s Visual Basic programming language.

VBA macros are extremely powerful, and we’ve used Excel macros and VBA for decades to create enterprise-grade software for accounting, retail, logistics, manufacturing, and dozens of other industries.

One of the drawbacks of Excel macros has been that it couldn’t automate Excel for the Web. Microsoft announced a step forward in this direction by launching a new web-friendly version of Excel VBA called “Office Scripts”.

Office Scripts vs. VBA: Major differences and similarities

The way Office Scripts work is fundamentally different to Excel VBA. Although Office Scripts is unlikely to ever replace VBA, it provides certain features that VBA can’t. Together, VBA and Office Scripts form a powerful combination.

Here are the major differences and similarities between these two powerful technologies:

Programming language

Desktop Excel Macros use the Visual Basic programming language, which tends to be easier for beginner programmers to learn. Office Scripts is based on JavaScript, the most popular programming language in the world, and used on virtually every modern website today.

JavaScript is more complicated to pick up for people without programming experience and is more challenging to debug.

Macro recording

Most casual users will likely use the “Record Macro” option for both desktop and web versions of Excel to create their macros. This Record Macro tool is designed for people who have little to no programming knowledge.

Recording macros is useful for simple office automation but is hopelessly inadequate when it comes to doing anything more advanced. To leverage advanced features of Excel VBA and Office Scripts, a knowledge of programming is essential.

Availability

Office Scripts is available exclusively to users with an Office 365 subscription. Excel VBA can be used by anyone with a valid Excel licence, whether Office 365 or standalone.

Integration

Both Excel VBA and Office Scripts allow for extensive integration between services, but they do it in different ways. Usually, Excel VBA is the best choice for desktop integrations, and Office Scripts is typically better for web integrations, but it all depends on the use case.

Office Scripts automation is achieved through Microsoft’s purpose-built Power Automate tool.

Power Automate lets users create workflows between web-based apps. Microsoft provides several pre-built automations, although these will likely need tweaking based on your use cases.

Integrating Excel VBA code with many other solutions is also possible, even web solutions. In just a few lines of code, Excel on the desktop can integrate with many other tools, both on the desktop and on the web, but this typically requires advanced programming knowledge.

Code that runs on Excel events

Using VBA, it’s possible to create full-scale applications with code that triggers based on Excel events, such as editing a cell or opening a workbook. This means that VBA-powered workbooks can function like first-class desktop applications. The experience is smooth and fast.

Office Scripts offers none of this functionality. To run Office Scripts, users must either click the relevant script button in their spreadsheet, or you have to connect the script to triggers defined in Power Automate.

Power Automate Triggers open up an immense amount of use cases that aren’t possible with Excel VBA, but Office Scripts’s lack of support for events limits that usefulness when using them directly inside a workbook.

Speed

Office Scripts are painfully slow when users run them directly. We created a simple script to update the value of a single cell through a button click and it took several seconds to perform this trivial task.

A similar script in Excel VBA would run in microseconds.

For large-scale processing tasks that need to be run on more than a few rows, Excel VBA is the obvious choice. If the task can be run in the background as part of a web automation, then Office Scripts is also an option.

Referring to other workbooks

Office Scripts can’t refer to other workbooks like VBA can.

In VBA, it’s possible to refer to any workbooks in the code and manipulate them in any way you wish. Office Scripts only let you run the script in the current workbook.

It’s possible to run an Office Script in a different workbook if it’s defined as part of a Power Automate flow, but the structure is completely different and far more complex than in Excel VBA.

If you want to refer to multiple workbooks using Office Scripts, you might want to hire an Excel specialist to help you.

Debugging code, and code readability

VBA provides a fully-fledged development environment that lets developers easily create reusable code for large applications. Office Scripts are limited to single code snippets that can quickly become messy for large projects.

The cumbersome structure of Office Scripts is probably the best clue that Microsoft never intended it to replace VBA, but rather to use it primarily with Power Automate for sophisticated background processing.

Debugging is also far more challenging in Office Scripts because of the tool’s inability to set “breakpoints”—places where programmers can pause the currently executing code to inspect what’s happening before it continues. This feature is what makes VBA one of the easiest languages in the world to program in. Office Scripts doesn’t support this, which is surprising seeing as similar functionality has been created in other online programming tools.

Possibly Microsoft will add this feature in the future. For now, debugging is a task better suited to experienced web developers.

Where Office Scripts shine: Web-based automation with Power Automate

Office Scripts shines as a web automation tool that runs in the background, connecting web-based tools through triggers, to process data. Using Power Automate, businesses can create scripts that do an extensive variety of things, such as:

Updating an Excel workbook when a user submits an online form.
Updating an Excel workbook when a user receives an email.
Automatically running Excel scripts when an Excel file is added to a SharePoint library.
Tracking file additions in SharePoint.
Connecting Excel with ChatGPT for advanced processing.
Scanning receipts added to OneDrive and putting the details in an Excel file.
Generating PDFs and sending them for signature.
Connecting Gmail with Excel.
Connecting Social Media channels to Excel.
And many more options.

Microsoft already offers numerous ready-made templates that let you connect well-known web apps. An experienced programmer can then take these templates and turn them into powerful business tools with advanced Office Scripts code, or create new combinations using Office Scripts.

A desktop version of Power Automate also exists, which lets users automate actions on their desktop.

Can you convert existing Excel VBA macro files to web-friendly office script files?

The short is not really, and certainly not at the click of a button.

Converting any existing Excel VBA application into an Office Scripts version would require extensive work from a VBA and Office Scripts programmer.

Many Excel VBA applications depend heavily on events, and Office Scripts’s lack of event functionality means this code will need to be replaced by buttons that users click, or Power Automate automations.

Probably, the most realistic solution to convert an Excel VBA project to Office Scripts would be to create a web version with fewer features that works in tandem with the desktop version, but it depends on your specific use case.

Will Office Scripts replace VBA?

Office Scripts won’t replace VBA, just as VBA won’t do everything that Office Scripts does. The two tools serve different purposes in the current ecosystem and will often work best when used together.

Excel VBA is best for fast processing and smooth apps that run on desktops. Office Scripts is best for web-based automation.

Also, if users absolutely must work on a web version of Excel, Office Scripts fills a gap that now makes that possible to some degree. Office Scripts is also useful for companies that use non-Windows operating systems, such as Linux or MacOS, and it will allow users of these operating systems to automate parts of Excel.

VBA has been used extensively in business applications because it lets businesses create professional desktop applications in a fraction of the time compared to other programming languages. When you know the ins and outs of VBA programming, it’s possible to create applications that look and feel like apps written in advanced other languages.

Expert Excel and Office Scripts development

Eppert Consulting has been building business-grade Excel VBA and Web Applications for over 20 years. Our team of developers is experienced in all the technologies necessary to create a robust desktop or web-based Excel app for you and your team, using either VBA or Office Scripts.

To learn more about our services, contact us today for a no-obligation chat.

07/01/2023

How to Combine Macro-Enabled Excel Workbooks with Microsoft Forms

Person working on computer

One of the questions we are commonly asked is whether a Macro-enabled Excel workbook is better than Microsoft Forms for collecting and processing data.

The answer isn’t the same for everyone. In some cases, Microsoft Forms might be better. In others, a macro-enabled Excel Workbook is crucial. Often, a combination of both technologies is the ideal solution, especially for business use cases.

Instead of giving you a definitive Yes or No answer, let us explain what each of these technologies is about so you can decide for yourself which might be best for your particular needs.

Basic definitions — Excel Workbooks, Macro-Enabled, and Microsoft Forms

In its simplest definition, an Excel Workbook is an Excel file made up of spreadsheets. Most people are familiar with this concept.

A macro-enabled workbook is an Excel workbook with computer programming code that runs in the background to automate certain data processing functions. These programming functions are called “Macros”. (Macro is short for “macroinstruction” but we’re not going to get more technical than that.)

Users can record simple macros in Excel, Microsoft Word, Outlook, and various other Microsoft Office products. This functionality alone has turned Office into one of the most powerful software tools on the planet. The breadth of use of macros cannot be overstated.

Although average users can record macros themselves, computer programmers can create macros that turn Excel into a truly enterprise-grade software solution. Macros can serve everything from one-person businesses to multinational corporations of many thousands of employees.

Typical uses for macros include:

Automating repetitive data processing functions
Speeding up repetitive tasks
Breaking down complicated formulas
Automatically sending emails
Connecting to an external database
Performing web-related tasks
And thousands of other uses.

Microsoft Forms

That leaves us with Microsoft Forms. This is simply an online tool for collecting information. Microsoft Forms are incredibly easy to create, and the information collected is stored in an Excel spreadsheet.

Forms offer no further functionality, but they do have the benefit that they are accessible online so you can collect information from anyone.

What are the pros of MS Forms versus MS Excel?

Comparing Microsoft Forms to Microsoft Excel is like comparing apples and oranges. They were built for different purposes.

But, as we’ll show you shortly, you can combine Excel and Forms to create a fantastic solution where you get the best of both worlds — forms combined with the macro-enabled power of Excel.

The pros of Microsoft forms are:

They are fantastically easy to create. You simply go to forms.office.com and start building a form with all the fields you need.
They are available online so that anyone can access them.
They have error-checking built into them so that users can type in only the correct type of information (such as dates in a date field, instead of random text).

How to create a Microsoft Form

There are two ways to create a Microsoft Form. They are exactly the same except in the way the form’s results are provided to you.

If you use a free Office 365 account, you can use the following procedure:

1. Visit forms.office.com and click “New Form”

New Microsoft Form

2. Create the form by clicking “Add New” and then add as many fields as you need. For our example, we added “First Name” and “Last Name”.

CReate MS office form

3. When you are done adding all the fields you need, click “Collect Responses” on the top right. This will give you a link that you can share with people so they can fill in the form. You will be given the option to make the form public, visible only to members of your organisation (if you are using a paid version), or to specific people.

Share Microsoft Office Form

4. After people fill in the form, you will be able to download the responses by clicking on the “Responses” tab and then clicking the “Open in Excel” link on the right.

View Microsoft Form Responses in Excel

If you click “View Results”, you will be taken to a separate screen that shows the results on the web.

The “Open in Excel” option downloads an Excel file with all the results in it. Every time someone fills in the form, you need to download a new file of results and all the results will be in there, even previously downloaded results. This is the primary difference between the free and paid version, which we will explain in a moment.

At this stage, it is entirely possible to manipulate the results in the downloaded Excel workbook and analyse them using a different macro-enabled workbook. Basically, the macro-enabled workbook would have a button that then processed the data in your downloaded workbook. (We can create this macro-enabled workbook for you if you don’t know how to do it yourself.)

One of the major cons to using this option is that, if you add any formulas to the downloaded Excel file, or even edit it in any way, those edits will be lost when you download the latest results because you will be downloading a different file every time. If you only plan on collecting one set of answers, then this isn’t a problem. But if you want to collect form responses regularly, this quickly becomes a deal-breaker.

We need a solution that provides responses in a single Excel workbook. This is only possible for paid Office 365 accounts. But this solution is the most flexible of all.

How to save Microsoft Forms data into a common Excel file

For paid versions of Office, there is a better way to create a form so that:

You don’t need to manually download results every single time you want to receive the latest information.
Results remain synchronised no matter how many times you download the file and edit the information or add formulas to it.

To do this, instead of going to forms.office.com to create your form, go to excel.office.com and create an Excel Spreadsheet. For this example, we will start with a blank workbook, although you could choose whatever type of workbook you want to create.

Click “Insert” in the ribbon and then click “Forms -> New Form”.

If you don’t see the “Forms” option, please note that you must be in a paid version of Microsoft Office. This option is not available on the free version.

Click insert form in MS Excel online

From here, the rest of the procedure of creating the Microsoft Form will feel familiar. You create the Microsoft Form as before, and click “Collect Responses” when you’re done to receive a link to share with people whom you want to share the form with.

Test form within Excel

But this is where it changes: When it’s time to collect the form responses, you will notice that there is a cloud symbol next to the “Open in Excel” option. That’s because clicking that icon does not download a separate Excel workbook every time, but rather opens the Excel form you created earlier.

Test form within Excel MS form responses in Excel

Here’s what our online Excel form looks like when we click “Open in Excel”.

MS form responses viewable in Excel

Any formulas you add to this file will be saved along with the file and won’t be lost every time you need to view the latest results.

Additionally, the file will also be available in your OneDrive folder on your desktop so you can simply open it to view the latest results.

Excel Macros with Microsoft Forms

As described previously, you can then have a separate Excel file with a button in it that opens your results file and processes the data with all the power of Excel Macros. This would be similar to the option when using the free version, except that you won’t need to download the file every time you want to get the latest data. The Excel form with the latest responses is already on your computer’s OneDrive folder.

The reason Macros shouldn’t be saved directly in the results file is that it could cause a conflict if someone is inputting data into the form while you are working on it on your computer. But you can certainly add formulas and make other edits in the results file. They will not be lost every time you click “Open in Excel.”

The easiest way to combine Excel macros with Microsoft Forms

We have been creating powerful Excel-enabled Workbooks for businesses for decades. Although it is possible to create macros yourself, there are certain intricacies involved in combining Microsoft Forms with Microsoft Excel to make the solution truly robust.

The intricacies are not complicated, but they are typically things that inexperienced users are not aware of.

The good news is that we have collections of code that already deal with these intricacies so that we don’t even need to charge you for dealing with them, and so can simply build your custom Excel solution on top of that basic code so that you can truly leverage the power of macro-enabled Excel Workbooks and Microsoft Forms.

To learn more about connecting Macro-Enabled Excel Workbooks and Microsoft Forms, contact us today!

03/03/202029/02/2020

4 Ways to Know if an Excel Expert is Really an Expert

Professional man walking in daylight — Photo by bruce mars on Unsplash

To properly define an Excel expert, we must know the meaning of the word expert.

ex·pert (noun): one with the special skill or knowledge representing mastery of a particular subject — Merriam Webster

“I am a self-proclaimed expert” seems to be the mantra of the modern age. Degrees and doctorates at established universities, coupled with a long track record of competent service in a given industry, were once the criteria for defining expertise. But the emergence of ambiguous fields of expertise such as Search Engine Optimization, User Experience Studies, and Social Media Analysis has blurred the meaning of the word “expert.”

Excel is a little like that. There is no university which offers a degree on “Excel Expertise.” But there are ways to tell if the Excel expert you’re about to hire really is an expert or just a self-proclaimed guru.

1. Can the purported Excel expert program in VBA-Excel’s built-in computer programming language?

Programming code on computer screen — luis gomes at Pexels

If he cannot, he is immediately not an expert. There is no need to read any further.

“VBA” stands for “Visual Basic for Applications.” It is Microsoft Excel’s “hidden” programming language. Its power and potential for solving everyday business problems with Excel cannot be understated. Professional and competent use of this feature can turn any mediocre Excel workbook into a workhorse of dazzling efficiency.

All of the programming language’s possibilities are impossible to enumerate here. Take a look at some of our customised business tools for Excel to get only an inkling of what can be done with Excel programming.

And that’s just scratching the surface.

A true Excel expert doesn’t merely dabble in Excel’s programming language. He is a master at it.

The more complex your project’s needs are, the more VBA programming will be required as a skill.

2. Can the expert demonstrate his knowledge of Excel functions and formulas quickly and easily?

Magnifying glass on maths formula — Ian Panelo at Pexels

An Excel specialist who does not have an absolute and total command of the majority of Excel’s built-in functions and formulas has no right to call himself an expert.

This one is easy to spot, even if you don’t know the formulas yourself, because a true expert should also be able to explain the formulas at a basic level to you, and in a way you can understand.

Ask questions about the potential project. Have him discuss with you how he would solve certain problems regarding the workbook. People who want only to dazzle you might leave you feeling more confused after such a conversation. A true expert will leave you feeling enlightened.

The true Excel expert knows his formulas and can explain them clearly when asked about them.

3. Are his workbooks visually appealing as well as functionally correct?

graphs on computer screen — Photo by Luke Chesser on Unsplash

Excel programming and Excel functions are the guts of the vehicle–the engine, the transmission system.

When a person first starts learning about Excel, often they get embroiled in the potential of these formulas. Their workbooks become overly complex. They sometimes want to try every single formula merely because it exists.

Your true Excel expert has been through this phase early in his career. He has fiddled with the engine, taken out his spanner and wire-cutters and pliers and toyed with everything from the spark plugs to the interior light. He knows how the car’s internals work through and through, and knows what is important and what is not.

After that, he can get into presentation.

No one wants to drive down the road in a sports car which looks like a jalopy.

The true Excel specialist will know how to make the final workbook shine. He will make it look good when it’s printed. The input cells will be easy to see. The cells with formulas in them will be protected so that they are not mistakenly overwritten.

Design is not something done as an afterthought. It is best done during the workbook’s creation. And if your Excel pro truly knows what he’s doing, the final design will be clean and simple, yet professional.

4. Does he have knowledge and experience in features seemingly unrelated to Excel?

Too many people consider Excel a tool found only in the accounting department, or in a stock broker’s arsenal.

Additionally, some people think that Excel can work only with Excel itself. In truth, Excel can work with almost all tools and software on your computer as well as on the internet.

A knowledge of Excel’s built-in programming technology is required for this. It opens vistas wide and large on Excel’s potential for small and large businesses.

At Eppert Consulting, we have programmed Excel tools which connect and work with Amazon Web Services, Dropbox, Google Maps, Microsoft Word, Adobe Acrobat Reader, Microsoft Outlook, Microsoft Access Databases, SQL and Oracle databases, and many, many other services and software. We have programmed tools which encompass PDF document manipulation, web uploads and downloads, image processing, the works.

If your expert is truly an expert, he has worked with a multitude of external services which enhance and accentuate Excel’s inherent features.

The scope, truly, is limitless.

Summary of how to choose an Excel expert for your project

Summary on paper — Photo by Glenn Carstens-Peters on Unsplash

No Excel specialist can claim expertise in Excel without an in-depth knowledge of Excel’s built-in programming language. Lack of its use is the difference between “good workbooks” and “spectacular workbooks.”

If he does not show a fluidity of understanding of Excel’s many functions, he is no expert. A true expert in Excel has such an in-depth knowledge of the subject that using it is a piece of cake for him.

A veritable Excel expert knows how to present his Excel projects. He is familiar enough with the inner working of Excel to have enough time left over to make your project also look sleek and spectacular. And the very best Excel experts do this all within budget.

Excel is not an island, and the truly competent Excel professional will know how to interlink Excel with other software and tools so that they work together. This way, he can provide a suite of functions which improve and facilitate complex tasks within your business.

Sure, there are also the obvious aspects of determining if someone really is an expert at something: Does he have good references? Does he have a wide and varied number of clients? Does his work speak for itself?

But these are things which one must determine with any business, not only Excel specialists. The above 1 – 4 are specific to Excel, and anyone claiming to be an Excel expert must pass with flying colours on each and every one of these points.

If you were to run Eppert Consulting’s skills and résumé through the above 1 – 4, I would hope you found us to be just the right Excel experts you needed for your next project.