How to Combine Macro-Enabled Excel Workbooks with Microsoft Forms

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”

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”.

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.

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.

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:

1. You don’t need to manually download results every single time you want to receive the latest information.
2. 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.

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.

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.

Here’s what our online Excel form looks like when we click “Open 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!