Upgrade Excel to VB.Net and Microsoft Access
The learning curve for the popular Microsoft Excel Spreadsheet is short and it's easy to use. It is straightforward to store data, perform numerical calculations, format cells, and create charts and graphs. Excel is ideal for a one-time analysis. It can analyse trends in data for informed decision making. It can create "What-if" scenarios.
In contrast, Visual Basic with a Microsoft Access database is designed for easy storing, reference and maintenance of high volume data.
As the requirements and volumes increase, Excel spreadsheets become difficult to modify and manage – and are particularly error prone. When this happens, it comes time to think of a VB.Net and Access upgrade.
The Problems inherent with Excel
The problems emerge when Excel is taken to the extreme – when trying to turn Excel spreadsheets into an administration database.
Spreadsheets are ideal for creating one time analyses, but they can become a nightmare to maintain as data and complexity increase over time.
- Most organisations have many copies of spreadsheets with minor differences that can become unmaintainable
- There is no facility for maintaining historical data – different versions of the same spreadsheet are common
- Excel worksheets are limited to 65000 rows on a 32 bit CPU
- Excel worksheets are limited to a single user
- The records are unstructured
- Repeated data is difficult to eliminate and maintain
- Excel has only very limited reporting
- Pivot Tables can increase file sizes exponentially
- Lack of validation of data will result in suspect output
- Calculations and Formulae are seldom tested and are prone to error
- Changes are seldom subject to scrutiny and validation
- Automatic updating of changes to Formulae can have erroneous side-effects
- Audit trails of changes are never created
- Fields can overflow and lead to the exclusion of the most significant numeric values
- There is no editing facility for individual cells
- Selecting and extracting individual records is awkward
- Excel is not user friendly when maintaining large record numbers
There are enough negatives to caution the user about extending the usage of Excel beyond its original purpose.
The Problems with Macros and Visual Basic for Applications (VBA)
There are also many negatives with the programming language used with Excel.
- Excel uses a clumsy, cell by cell, approach to select cells
- VBA and Macro error handling is generally lax or non-existent
- The Macros generated are usually cell specific – and seldom handle changes correctly
- Macros can return erroneous results that result in the wrong decisions or inaccurate financial information
- The VBA programming logic of Excel is complex when referencing cells
- Multiple worksheets are messy to reference and code
VB.Net in contrast, is modern, easier, better to use, has a superb development environment – compared to VBA and Macros.
The Benefits of a Conversion to Visual Basic.Net and Microsoft Access
The benefits of converting from Excel are:
- All the Excel data can be easily imported to Access Tables
- A greater degree of automation can be achieved compare to Excel
- Database and logic programming is straight forward using Visual Basic.Net
- Input Data can be validated and high quality results assured
- All the historic information from the Excel spreadsheets can be stored in the Access Database and made readily available to users
- The Access Reporting software offers superb flexibility in presenting results, in detail or in a grouped summary
- Intricate Excel spreadsheets and Formulae can still be created and accessed from Visual Basic.Net
- The size of the stored data will be dramatically reduced, especially where Excel pivot tables are used
- The new system will handle multiple users
- All the charting and graphs of Excel are available to Visual Basic.Net
- Records can be more readily selected
Microsoft Access will help maintain Data Integrity
Access offers Referential Integrity between tables to ensure data is consistently defined across tables. Referential integrity ensures that the relationships between the tables remains synchronised after updates and deletes.
The data is normalised by eliminating repeating groups. Data is stored in only one place and can be easily referenced, instead of being repeated as happens in Excel.