what language can you use with visual basic for excel

It can connect directly to a database, but what if the data you need is produced by another system, or you don’t have direct database access to the data you need? Data is often copied and pasted from reports from other systems to Excel, often with no way of knowing when the data was last copied or even if it is complete. Connecting Excel to external data sources can be tricky. When another user has to use the same spreadsheet there’s a high chance because they’re not familiar with the nuances of how it works they’ll stumble across some error that was never tested for. The are typically no unit tests written for VBA code, and the only functional tests are often by the author of the spreadsheet on a limited set of inputs. Excel spreadsheets are subject to very little in the way of tests. The task may still be reasonably simple, but because of the duplication required and the fact that each cell can only hold one unit of data a spreadsheet can sort of explode into something way too complex. Ranges get duplicated to handle more and more cases or multiple sets of data until it’s hard to reason about what’s going on.

A spreadsheet may start off fairly simple with a few cells and formulas. The following is a list of a few weaknesses in how Excel spreadsheets are typically developed.

What is it about Excel that makes it prone to errors? Nothing in itself, but there are various things that developers are used to that reduce the same sorts of risks in non-Excel based solutions. Most companies don’t have the resources to have developers dedicated to each business user, and even if they did then communicating what’s needed and iterating to get the desired outcome would struggle to compete with an individual ‘knocking together’ an Excel spreadsheet. Technically they may be correct, but that assumes that everyone has the time and appetite for learning to be a developer (which takes most of us many many years). Software developers often argue that the same can be achieved in their favourite programming language.

Someone using Excel can do an incredible amount of complex work far more effectively than with any other tool. The main reason why Excel is so heavily used is worker productivity.

With such risks, why do companies still use Excel, and what can they do to prevent similar situations?

What language can you use with visual basic for excel