|
|
|
|
|
|
|
|
|
|
|
|
AutoSoft Systems |
| |
|
|
|
AutoSoft
Systems |
2 Round Hill
Court |
| |
|
|
|
East Greenwich,
RI 02818 |
| |
|
|
|
401.885.3631 |
| |
|
|
|
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 |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
AMDG |
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 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: |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| ExcelSecurity.htm |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|