AutoSoft Systems
        AutoSoft Systems 2 Round Hill Court
        East Greenwich, RI 02818
        401.884.5653 Fax
                        401.996.3631 Cell
AMDG       Decision Ready Information! autosoft@aol.com
Commercial & custom multi-user computer software for a variety of applications including performance metrics, statistical analysis, data extraction and merger from multiple large databases, computer simulation and management information systems. Founded in 1982.
We know Excel! Whenever possible, Excel is the user interface, VBA is the programming language, ODBC connects to the database of your choice.
Home About Us Literature Wire & Cable Pharmaceutical & Biotech Construction Management Commercial Products Custom Solutions
Contact Us Events Downloads
Options for Application Configuration
Whoa Nellie! Why use Excel for Application Development? Is everyone using Cloud Based solutions today?
Select the right tool for each application. One cannot cut down a tree with a lathe, nor make a baseball bat with a chain saw.
I asked ChatGPT: Why use Excel for application development? The answer:
Excel is commonly used for application development due to several advantages it offers, especially for users who may not have extensive programming skills. Here are some reasons why Excel is chosen for application development:
1. User-Friendly Interface:
Excel provides a familiar and user-friendly interface with a grid structure that makes it easy for users to organize and input data.
2. No Coding Skills Required:
Excel allows users to create applications without extensive programming knowledge. Users can leverage built-in functions and formulas using a point-and-click interface.
3. Rapid Prototyping:
With Excel, it is quick to create prototypes and mockups of applications. Users can easily experiment with different layouts, calculations, and functionalities.
4. Data Analysis Capabilities:
Excel is known for its strong data analysis capabilities. Users can perform various calculations, create charts, and analyze data directly within the application.
5. Integration with External Data Sources:
Excel supports integration with external data sources, making it possible to import and export data from and to other applications or databases.
6. Custom Formulas and Functions:
Users can create custom formulas and functions using Excel's formula language (Excel formulas and VBA), allowing for the implementation of specific business logic.
7. Macro Recording:
Excel allows users to record and play macros, automating repetitive tasks. This can enhance efficiency and reduce manual workload.
8. Graphical Representations:
Users can create visually appealing dashboards and reports with charts and graphs, which can be helpful for data visualization and presentation.
9. Widespread Adoption:
Excel is widely used in various industries, and many people are already familiar with its basic features. This can facilitate collaboration and ease of sharing applications within a team or organization.
10. Low Development and Maintenance Costs:
For simple applications or prototypes, Excel can be a cost-effective solution compared to developing a full-fledged software application.
While Excel is suitable for certain types of applications and users, it may not be the best choice for complex, large-scale, or mission-critical applications. In such cases, other development platforms or languages may be more appropriate. However, for quick prototyping, simple data manipulation, and basic applications, Excel can be a valuable tool.
I asked ChatGPT: Why use Excel-VBA for application development? The answer:
Excel VBA (Visual Basic for Applications) is commonly used for application development within Excel for several reasons:
1. Integration with Excel:
VBA is specifically designed to work seamlessly with Excel. This tight integration allows developers to manipulate Excel workbooks, worksheets, charts, and data directly from VBA code.
2. Automation of Repetitive Tasks:
VBA enables the automation of repetitive and time-consuming tasks in Excel. Users can write scripts to perform complex calculations, data manipulations, and other operations automatically.
3. Custom Functions and Procedures:
With VBA, developers can create custom functions and procedures to extend Excel's functionality. These can be used to implement specific business logic or perform calculations that are not achievable with standard Excel formulas.
4. Event Handling:
VBA supports event-driven programming, allowing developers to respond to specific events such as changes in worksheet data, workbook opening or closing, button clicks, etc. This enhances the interactivity and responsiveness of Excel applications.
5. User Forms:
VBA allows the creation of custom user forms within Excel. This is particularly useful for building user interfaces, collecting input, and enhancing the user experience.
6. Control Structures and Flow Control:
VBA supports various control structures like loops, conditionals, and error handling, providing a more flexible and powerful programming environment compared to Excel formulas alone.
7. Access to Windows API:
VBA allows access to the Windows API (Application Programming Interface), enabling developers to interact with the operating system and other external applications beyond the capabilities of Excel alone.
8. Reusable Code:
Developers can create reusable modules and libraries in VBA, promoting code organization and maintainability. This is particularly beneficial when building larger and more complex applications.
9. Debugging Tools:
VBA comes with a set of debugging tools that allows developers to step through code, set breakpoints, and inspect variables. This facilitates the identification and resolution of errors in the code.
10. Low Learning Curve for Excel Users:
For individuals already familiar with Excel, learning VBA is often more accessible than learning a completely new programming language. This makes it an attractive option for users who want to extend Excel's capabilities without becoming professional programmers.
While Excel VBA is powerful and versatile, it may have limitations when compared to more robust programming languages for certain types of application development. Nevertheless, for Excel-centric tasks and applications, Excel VBA remains a valuable tool for enhancing automation, functionality, and customization.
I asked ChatGPT: Why use Excel-VBA with a SQL Server back end? The answer:
Using Excel VBA with a SQL Server back end can offer several advantages, especially when dealing with data-intensive tasks and applications. Here are some reasons why this combination is beneficial:
1. Data Centralization:
Storing data in a SQL Server database provides a centralized and scalable solution. Multiple users can access and manipulate the same data, ensuring consistency and reducing the risk of data discrepancies.
2. Data Security:
SQL Server offers robust security features, including user authentication, role-based access control, and encryption. By using SQL Server as the back end, you can enhance the security of sensitive data compared to storing it directly in Excel files.
3. Scalability:
SQL Server is designed to handle large volumes of data efficiently. As your data grows, using a SQL Server back end ensures better scalability and performance compared to Excel files, which may become slow and unwieldy with large datasets.
4. Concurrency and Multi-User Support:
SQL Server supports concurrent access by multiple users, allowing for simultaneous data updates and queries. This is crucial for collaborative environments where multiple users need to interact with the same dataset.
5. Structured Query Language (SQL) Queries:
SQL Server allows you to use SQL queries to retrieve, update, and manipulate data. VBA can be used to execute these SQL queries from Excel, providing a powerful way to interact with the data stored in the SQL Server database.
6. Data Integrity and Referential Integrity:
SQL Server supports features such as data integrity constraints and referential integrity, ensuring that the relationships between tables are maintained. This helps prevent data inconsistencies and errors.
7. Stored Procedures and Views:
You can leverage stored procedures and views in SQL Server to encapsulate complex logic on the server side. Excel VBA can then execute these stored procedures or query views to fetch or manipulate data.
8. Data Analysis and Reporting:
Excel is a powerful tool for data analysis and reporting. By connecting Excel to a SQL Server back end, you can leverage Excel's features for creating charts, graphs, and reports while keeping the data centrally stored and up-to-date.
9. Automation and Integration:
Excel VBA can be used to automate tasks, such as importing or exporting data between Excel and SQL Server. This integration streamlines workflows and reduces manual effort.
10. Version Control:
SQL Server databases can be integrated with version control systems, allowing you to track changes to the database schema and maintain a history of modifications. This is crucial for maintaining data consistency and managing database evolution.
While this combination offers several benefits, it's essential to consider factors such as data security, permissions, and potential network latency. Additionally, it's recommended to follow best practices for handling sensitive data and implementing proper error handling in your Excel VBA code.
There are several options for application configuration depending upon the requirements. This page outlines the various types used by AutoSoft. In all cases the appropriate amount of security will be applied as outlined in this page:
Application Structure
Name: Stand-alone   Read-only on Network Share   Cloud-based  
Details: A self-contained file that performs all necessary functions and stores all of the data required by the App.   The App is stored on a Network shared drive. Multiple users can open the App and work on it simultaneously. All data is stored in a back-end database.   The master copy of the App and all data is stored in a Cloud database. A self-updating copy of the App is stored on every user's computer.  
Pros: Simplest to create and maintain.   Multi-user. All data stays behind corporate firewall.   Multi-user. Authorized users can work on the App from anyplace with an Internet connection.  
Cons: Single user. Version control issues if the App is to be used to evaluate different scenerios.   User needs to be on the corporate network to use the App.   Most costly to design and maintain. Emphasis must be placed on performance in anticipation of jittery or slow internet connection.  
Database Options
Database   Pros   Cons
Access   Free. Simplest to implement. Quite sufficient for most tasks. Easy to set up Backup and Restore procedures. No database server required.   2 Gigabyte limit. Prone to being left in an unstable state if a transaction is interrupted. Not Cloud-based.
SQL Server Express   Free. More stable, more features and faster than Access.   10 Gigabyte limit. Requires database server computer. Must establish backup and restore procedures. Not Cloud-based.
SQL Server   Essentially unlimited storage.   One time purchase. Requires database server computer. Must establish backup and restore procedures. Not Cloud-based.
Azure   Cloud-base. (SQL-Server in the Cloud). Essentially unlimited storage.   Requires monthly subscription.
Other Local (Progress, Oracle, MySQL, )   Use if the database is already operational for another App.   May need to purchase drivers to connect to the database. Not Cloud-based.
Other Cloud (Progress, Oracle, MySQL, )   Use if the database is already operational for another App.   May need to purchase drivers to connect to the database.
AutoSoft Systems | 2 Round Hill Court, East Greenwich, Rhode Island, USA 02818 | 401.885.3631 | Fax: 401.884.5653 | Mobile: 401.996.3631
This web page was last updated at 01/09/2024 12:05 PM and is written in EXCEL!
AXIOM is a Trademark of Consona - USYS is a Trademark of Zumbach - AutoSoft Systems is not affiliated with Consona nor Zumbach