Strategies on Converting Excel VBA Macro to Web Application

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 far longer than necessary on spreadsheets and documents until they find a better alternative. For organizations who are ready to create order from chaos while scaling up, consider these strategies on converting excel vba macro to web application. Key strategies include deployment of full-fledged web applications or packaged solutions. Mindfield client’s 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 Customers for Quality 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.

CONVERT EXCEL

Article banner with Excel icons and article headlineCommon Symptoms on When to Upgrade an Excel Application

  1. Inability to share data with team members because of one master copy available
  2. Bottle-neck issues when having a backlog of data to input
  3. Security issues with not knowing who accessed the spreadsheet and when
  4. Uncertainty of data compliance, this poses privacy issues for datasets especially customer and patient data
  5. Difficulty in protecting proprietary algorithms or processes in VBA code
  6. Challenges with managing VBA code when any team member can modify the code resulting in different outputs
  7. 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 team mates

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: an software initiative allows an organization to define leading practices, which provides cost savings, reduces time to processing, and predictably delivers quality.

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 processes data (source)

Pros:

  1. Very quick to start up and execute
  2. Ability to create a mirrored version of your Excel VBA along with strengths and weaknesses
  3. Zero coding required which means easy for end-users to pick up
  4. Ability to interact and use data from other sources
  5. Allows for the creation of new interfaces on top of the spreadsheet

Cons:

  1. User experience will be a challenge to optimize given that there are only fit-for purpose tools rather than cutting edge frameworks
  2. The importer may be stretched in providing quality of imported code
  3. If there are multiple dependencies across different spreadsheets this may pose a challenge to the application
  4. The new code output may take time for VBA developers to learn and maintain
  5. Uncertainty around security and how the master file  is structured, meaning work to maintain may be exponentially more difficult
  6. Limited gains in business value since inherently this is still a spreadsheet for departments that have outgrown a tabular interface
  7. Application may not scale beyond a 100 users and will require extensive rework to become a high performance system
  8. 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 limited amount of VBA and macro code, whereby the professional is looking to just share a dataset

Pros

  1. Comparable scripting environment making it less daunting to export code
  2. Ability to plug-in to existing extensions on Excel Online and Google Sheets to enrich functionality
  3. Built-in audit history to see who edited data fields and code
  4. Ability to manage user access on who can view and edit the spreadsheet
  5. Low upfront cost option without subscriptions costs or additional hosting hardware

Cons

  1. Requires technical expertise to audit and rewrite existing code
  2. Limited control of underlying hosting provider meaning company will be subject to Google and Microsoft’s  service availability
  3. Will require time and technical skillset to transition a VBA code
  4. Limited fine grain security controls
  5. May not meet government or public agency requirements of having data kept in country
  6. 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 accomplished be 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:

  1. Easy and intuitive way to create mobile apps using spreadsheets
  2. Create prototypes for free using their development and configuration tools
  3. Ability to have your data processing and workflows be mobile accessible for convenience of users
  4. Vendors often have sample applications to build from
  5. Includes basic functionality like user management and log-in for security
  6. Ability to easily integrate from other data sources from the cloud

Cons:

  1. User interfaces are often defined by templates, customizing this would reduce the value using an app maker
  2. Currently automated testing of app functions aren’t available for leading app makers( i.e. AppSheet as of April 16 2018)
  3. High price for avoiding technical development ranging from $1-40 per user on a monthly basis
  4. Will require a company to audit the security of applications particularly with how sensitive data is kept and migrated
  5. Premium features like white labelling or data analytics requires additional costs

Strategy 4: Looking for a Packaged Software

finger cursor over Settings Menu

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:

  1. Allows companies to use existing software to meet current needs
  2. Vendor support to utilize, deploy, and maintain technologies
  3. Working with software vendors can also smooth out the execution of a software project
  4. Organizations can also make use of industry specific packages
  5. Ability to see how other organizations or vendors approach a common business process in their industry
  6. SaaS applications may also provide low overhead costs to a company

Cons:

  1. Excel macros are very unique and finding perfect fit features may not be feasible
  2. Companies will also need to vet clients through proposals or demo sessions
  3. Over stated the product capabilities may fail to deliver crucial features
  4. Companies may sometimes be losing a trade secret or competitive advantage by adopting publicly available software
  5. Lack of ability to create a platform business model where by a company could re-package and sell the software they create
  6. Requires training for users to adopt new practices or changes in  processes

 

Strategy 5: Creating Custom Software

An organization who has existing VBA macros have in essence already created custom software, though in limited availability. 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 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:

  1. Addressed the inherent challenges with over extending an Excel application
  2. Highest level of flexibility compared to the different options
  3. Greatest certainty with meeting business needs and adapting to changes in the future
  4. Ability for a company to create new revenue streams by offering subscription based technology
  5. Having technical staff who can script or create macros significantly reduces the risk of custom software projects
  6. An existing Excel application reduces the risks of custom software projects
  7. Ability to customize and reinvent the way team’s and personnel interact with the data
  8. Allows an organization to reuse the functionality in a scalable manner

Cons:

  1. Custom software requires reasonably skilled developers to execute well
  2. Requires  overhead to run and maintain
  3. May require user training to help staff adapt
  4. Team may need additional skillsets like development, infrastructure, and testing
  5. Often requires an agile approaches especially when redefining processes and endeavouring to push the envelope from best to leading practice

The main factor to having a successful software project is 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.

Convert your Excel Macro to an Application with Proven Experience

Mindfield Consulting has helped Organizations lead their industry by creating powerful technologies birth from Excel VBA and macros, that allowed our client to redefine a global business process to reduce risk and increase delivery velocity of key products. We help organizations find the 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.

 

leave a Comment