Since the inception of VBA, one method of getting input from users has been through VBA forms.
However; one of the major new elements of the Power Platform is the low-code environment PowerApps. It allows users to develop applications much faster than in the traditional VBA environment in Excel.
While the PowerApps platform provides a great way for users who are familiar with Excel to roll out automation rapidly, it has some limitations. When working with smaller applications, such as a spreadsheet, setting up a cloud data set may be impractical.
What is PowerApps?
PowerApps is a low-code platform that can connect to multiple data sources, including SharePoint lists, Outlook email and SQL server.
It can also connect with Excel; although it can only connect with data that is stored in tables, and the files need to be stored on the Web (in a OneDrive or SharePoint site).
While there are multiple types of apps (including full-featured web sites), the comparable type of app to VBA forms is the Canvas App. These allow users to provide a large number of customizations to the look and feel of the application.
What is an Excel VBA Form?
An Excel form can use a number of possible controls, and uses VBA in order to provide the code for the controls.
As a result, a VBA form can have a large number of possible actions; the VBA language is very capable of handling any Excel automation task!
User forms are generally used in order to provide an easy way for users to interact with data on an Excel sheet that may not be stored in a data table.
When should you use a PowerApp?
PowerApps provide a number of advantages over using a VBA form in order to gather information.
Cloud Data Required
If there are multiple users who are accessing the data in the spreadsheet, then the data can be stored in a OneDrive or on a SharePoint site, or in a database such as the Common Data Service.
VBA is not available in the online version of Excel. In order to run a VBA form, the spreadsheet would need to be downloaded on to a local computer.
Mobile Access Required
Power Apps are designed for the cloud. This means that if it is easiest for users to gather information on a mobile device, it can be customized to allow for information to be gathered effectively.
While there is a mobile Office application, it is not user friendly and will discourage most casual users.
End User Customization / Development
The code required in order to customize a PowerApp for gathering data is straightforward for an intermediate to advanced level Excel user.
As a result, apps can be deployed rapidly, and with little input required from the development team.
Strong Data Governance Required
In order to protect sensitive data, and to ensure that information cannot be shared between systems, data governance policies can be set up to control information.
When should you use a VBA form?
Since the inception of PowerApps, the number of use cases for VBA forms has decreased. However; there are still cases where a VBA form is the best method to get input from a user.
No Office 365 Subscription
At this time, it is still possible to licence Excel 2019 without subscribing to Office 365.
In order to use PowerApps, a subscription is required. As part of Office 365, some elements of the PowerApps platform (including interaction with Excel) are enabled.
However; automation using VBA is unrestricted.
Data Stored On Premises
If data is not stored on the cloud, then data will need special connectors in order for PowerApps to gain access to the data.
If the data is stored in the spreadsheet itself (and not in a data table), PowerApps cannot access the data in the spreadsheet, even with connectors.
In a VBA form, it is possible to execute any command within VBA. This allows for a great deal of control over the spreadsheet.
It is possible to make modifications such as refreshing pivot tables, adjusting formats; effectively, any possible action that can be done manually using Excel!
With PowerApps, it is possible to manipulate data within a spreadsheet’s data table, but the ability to modify other contents of the sheet is limited.
A PowerApp can run an Office Script through Power Automate, but at this time Office Scripts are still a preview feature.
Which form method should I use?
Unless you require specific functionality that is only offered through VBA, using VBA forms are not recommended.
According to the developer web site Stack Overflow, VBA was the most dreaded programming language in 2019.
Microsoft has acknowledged the lack of popularity of Visual Basic by stating that they “do not plan to evolve Visual Basic as a language“.
VBA can continue to be used for very simple automation. Other than that, other methods should be used for more advanced processes.