Excel is a magnificent tool and when pushed to its limits can even help run entire departments or coordinate schedules of hundreds of staff members. Organizations often manage their processes for longer than necessary on spreadsheets and documents until they find a better alternative. For organizations that are ready to create order from chaos while scaling up, consider these strategies on converting excel VBA macro to web application. Key strategies include the deployment of full-fledged web applications or packaged solutions. Mindfield clients have found success in creating production-level applications from their secret sauce leading to reductions in operating costs, mitigation of operational risks, and augmentation of ability to deliver customer value at each stage of the value chain.
Trusted by for Quality Anywhere Applications
|In my role as a project manager at the North West Company we hired Mindfield Consulting to develop several custom .net applications for us. Timelines were consistently met and I was always impressed with Mindfield’s responsiveness and ability to quickly grasp the problem and offer up creative solutions. Applications were of very high quality and I wouldn’t hesitate to recommend Mindfield Consulting for any custom development.
Common Symptoms on When to Upgrade an Excel Application
- Inability to share data with team members because of one master copy available
- Bottle-neck issues when having a backlog of data to input
- Security issues with not knowing who accessed the spreadsheet and when
- Uncertainty of data compliance, this poses privacy issues for datasets especially customer and patient data
- Difficulty in protecting proprietary algorithms or processes in VBA code
- Challenges with managing VBA code when any team member can modify the code resulting in different outputs
- Difficulty in processing large datasets, often leading to grinding performance
Experiencing any of these symptoms implies that an Excel application may be holding back your team from achieving greater productivity while increasing risks of data and confidential information exposure. Leaders should consider immediately the following options to de-risk and optimize their departments by unlocking the potential within VBA code.
Benefits of Converting VBA Macro to Web Application
Transitioning from an Excel VBA into a web application has a number of benefits inherent with adopting custom software technology. The most inherent benefits are :
- Increased adoption to innovation: typically VBA and macros are kept hidden or may be difficult to be made available to other teammates
- Reduce security risks: web application increases the amount of audit and logging capabilities available, paving the way for compliance and security
- Reduce costs of maintaining legacy VBA code: having a web development project can increase the shared understanding of developed technology
- Creation of industry-leading practices: a software initiative allows an organization to define leading practices, which provides cost savings, reduces time to processing, and predictably delivers quality.
Executive Summary Infographic on Converting Excel VBA to an Online Solution
Strategy 1: Using Automated Wizard to Convert VBA Programs to Code
Software packages like EASA can help import an entire VBA or macro with just a few quick steps using their import wizard. This allows a mirrored representation of a spreadsheet program that can be made available over the internet or hosted in a private server. The video provides a snapshot as to what the process could look like, this is a great particularly for organization that want their program up and running quickly.
Most applicable: for organizations looking to share Excel files with VBA and macros that process data (source)
Pros:
- Very quick to start up and execute
- Ability to create a mirrored version of your Excel VBA along with strengths and weaknesses
- Zero coding required which means easy for end-users to pick up
- Ability to interact and use data from other sources
- Allows for the creation of new interfaces on top of the spreadsheet
Cons:
- User experience will be a challenge to optimize given that there are only fit-for-purpose tools rather than cutting edge frameworks
- The importer may be stretched in providing quality of imported code
- If there are multiple dependencies across different spreadsheets this may pose a challenge to the application
- The new code output may take time for VBA developers to learn and maintain
- Uncertainty around security and how the master file is structured, meaning work to maintain may be exponentially more difficult
- Limited gains in business value since inherently this is still a spreadsheet for departments that have outgrown a tabular interface
- Application may not scale beyond 100 users and will require extensive rework to become a high-performance system
- Since the application stores a master spreadsheet the corruption of this file could mean loss of data
Strategy 2: Converting Excel VBA to an Online Database
There are dozens of applications that offer the ability to share a spreadsheet online. This would also require creation of VBA and Macro codes within a separate coding environments. The most often used providers include Google Sheets and Microsoft Excel Online which require a user account on either networks. The capabilities of these options are far reaching as they provide the same capability to code and automate your data processing procedures. Both Google Sheets and Excel Online offer scripting capabilities along with user interface development.
Most applicable for: spreadsheets that have a limited amount of VBA and macro code, whereby the professional is looking to just share a dataset
Pros
- Comparable scripting environment making it less daunting to export code
- Ability to plug-in to existing extensions on Excel Online and Google Sheets to enrich the functionality
- Built-in audit history to see who edited data fields and code
- Ability to manage user access on who can view and edit the spreadsheet
- Low upfront cost option without subscriptions costs or additional hosting hardware
Cons
- Requires technical expertise to audit and rewrite existing code
- Limited control of underlying hosting provider meaning company will be subject to Google and Microsoft’s service availability
- Will require time and technical skillset to transition a VBA code
- Limited fine grain security controls
- May not meet government or public agency requirements of having data kept in-country
- Subject to changes in terms of agreements by Google or Microsoft with limited control
Strategy 3: Using Google Sheets and App Makers
Migrating a VBA app to an online platform could be accomplished ye using Google sheets along with app makers (AppSheet and Microsoft Power App), it allows users to minimize the amount of code they write while providing modern graphical user interfaces for mobile apps. Despite the ease of use and maintenance, app makers do require a subscription charged out monthly on a per user basis.
Ideal for: Ideal for companies who want to test out a new concept or prototype, though subscription costs may prove prohibitive
Pros:
- Easy and intuitive way to create mobile apps using spreadsheets
- Create prototypes for free using their development and configuration tools
- Ability to have your data processing and workflows be mobile accessible for convenience of users
- Vendors often have sample applications to build from
- Includes basic functionality like user management and log-in for security
- Ability to easily integrate from other data sources from the cloud
Cons:
- User interfaces are often defined by templates, customizing this would reduce the value using an app maker
- Currently automated testing of app functions aren’t available for leading app makers( i.e. AppSheet as of April 16 2018)
- High price for avoiding technical development ranging from $1-40 per user on a monthly basis
- Will require a company to audit the security of applications particularly with how sensitive data is kept and migrated
- Premium features like white labelling or data analytics requires additional costs
Strategy 4: Looking for a Packaged Software
An Excel system has been use to support department procedures or tracking and managing workflows and documents. If simply exporting your spreadsheet is insufficient to meet objectives, another step professionals may take is to identify software packages that may fit their needs.
Ideal for: companies who have clear understanding of their requirements so as to assess product fit-gap with packaged software
Pros:
- Allows companies to use existing software to meet current needs
- Vendor support to utilize, deploy, and maintain technologies
- Working with software vendors can also smooth out the execution of a software project
- Organizations can also make use of industry-specific packages
- Ability to see how other organizations or vendors approach a common business process in their industry
- SaaS applications may also provide low overhead costs to a company
Cons:
- Excel macros are very unique and finding perfect fit features may not be feasible
- Companies will also need to vet clients through proposals or demo sessions
- Overstated the product capabilities may fail to deliver crucial features
- Companies may sometimes be losing a trade secret or competitive advantage by adopting publicly available software
- Lack of ability to create a platform business model whereby a company could re-package and sell the software they create
- Requires training for users to adopt new practices or changes in processes
Strategy 5: Custom Excel to Web Application Service
An organization that uses VBA macros already has custom software, though there is much more that can be accomplished when using modern web programming tools. Creating a custom application allows an organization to define business processes more clearly while paving the way to automate and scale its’ operations. An organization may consider creating their own software to replace a spreadsheet. This approach properly resolves the symptoms faced by an over-burdened macro or Excel file. Given that there is an existing skillset for a company to create Macros in the first place, current team members may also be able to be leveraged as part of a project.
Ideal for: organizations that want to create a competitive edge using technology or have found no package to meet their needs effectively
Pros:
- Addressed the inherent challenges with overextending an Excel application
- Highest level of flexibility compared to the different options
- Greatest certainty with meeting business needs and adapting to changes in the future
- Ability for a company to create new revenue streams by offering subscription-based technology
- Having technical staff who can script or create macros significantly reduces the risk of custom software projects
- An existing Excel application reduces the risks of custom software projects
- Ability to customize and reinvent the way team’s and personnel interact with the data
- Allows an organization to reuse the functionality in a scalable manner
Cons:
- Custom software requires reasonably skilled developers to execute well
- Requires overhead to run and maintain
- May require user training to help staff adapt
- Team may need additional skillsets like development, infrastructure, and testing
- Often requires an agile approach especially when redefining processes and endeavoring to push the envelope from best to leading practice
The main factor to having a successful software project is a clear product vision in understanding the business needs and outcomes while engaging skilled resources that can self-manage and evoke clarify in the defining business rules.
Over $1,000,000,000 and Counting Earned by Mindfield Customers
Mindfield Consulting has helped Organizations lead their industry by creating powerful technologies birthed from Excel VBA and macros, which allowed them to redefine a global business process to reduce risk, increase service capacity, and ultimately grow revenues multiples. We help organizations find the right mix of packaged and custom software from planning service delivery to remote locations to global trading systems for precious materials and resources. Mindfield Consulting has over two decades of experience delivering technology that meets every mission. Our client’s win when they are empowered with the right technology and the right people for over 19 years across Canada. Mindfield specializes in custom software development, elearning for corporate training, and jira expert consulting.
One comment
Rob
13 August 2021 at 7:13 am
I have A VBA file (about 6 KB) TO CREATE DATE-CORRELATED LEGAL DOCUMENTS FOR REAL ESTATE TRANSACTIONS.
AS YOU PROBABLY KNOW, MICROSOFT’S SECURITY MEASURES MAKE IT ALMOST IMPOSSIBLE TO PROPERLY RECEIVE AN ATTACHED FILE WITh MACROS.
I’d like potential clients to use it for free for 10-15 days, and your services seem like a good option to achieve this and other objectives.