|
 Most
computer applications involve managing key business information. The
preferred tool to store information is a relational database. Remaining
components of the application exist to add information from different data
sources, derive new information through calculations and algorithms, and
present information in formats that provide value.
The Microsoft Office suite includes the key
tools for database storage, report generation, input forms, and charts.
Development costs are considerably reduced using this set of tools.
Moreover, the Office applications are available to Visual Basic
code through an object model. This allows a custom application to
drive the behavior of an Office tool like Excel, and use
Excel as a component of the custom application.
Wedelich Consulting has considerable experience
developing custom software applications using Microsoft Access and
integrated with Microsoft Office. These applications not only
exhibit much lower development costs; they are also easily accepted since
users are already familiar with the Office suite, and they
integrate well with existing user methods - key attributes that lead to
business value.
Database Applications
 The
forms model in MS Access easily handles tab sections and sub
forms. The example on the right illustrates a main form with
information about projects. The search box at the bottom is used to
search for a specific project. Multiple tabs exists for different
data items that are contained within a project (wells, injection events,
samples, production data). Tabs can also be used for input
controls to launch
specialized reports for a particular project. All of the key
information about projects can be viewed from this one form.
These types of database applications can often be
developed with minimal code. In the example above, most of the code
consists of simple event handlers to launch reports when a particular
button is clicked. Applications with minimal code are much easier to
maintain and change as your
business changes. Users can often be taught to make minor changes
themselves.
Companies often have key business information that is
maintained in an Excel spreadsheet. This happens because
users are comfortable with Excel. It is often a good first
step to define the needs for a database application, but the business
information becomes a "data island" - it cannot easily be used by other
parts of the organization. In some cases, users spend considerable
time maintaining these spreadsheets, partially because Excel is not
the best tool to manage data. Wedelich Consulting can help
you get better value by leveraging the advantages of a relational database
while preserving the integration with Excel.
Spreadsheet Applications
Companies often use Excel template files to
collect data from different parts of the organization. Financial
examples include budgeting and forecasting. Working with a large number of
Excel files to collect and process the information can be a
difficult and tedious task. The integration of Visual Basic
with Excel can be used to develop user forms (tool bars and dialog
boxes) to automate operations that work with a collection of Excel
workbooks and worksheets.
Wedelich Consulting developed an Excel
application to help manage a budget process where Excel templates
are used to collect data from different financial locations. The
components of the application include:
-
A navigation user form to quickly move to a specific
location and sheet.
-
Automatic population of history data by integrating
with a database of actual revenue and costs.
-
Creation of rollup workbooks for any desired subset of
locations, along with toolbar buttons to consolidate locations in the
rollup workbook.
-
Creation of
outline rollup workbooks that show both consolidated totals and location
detail.
-
Application of a variance based color coding scheme to
help find variances in either location or rollup workbooks.
-
A spreadsheet template for calculating costs that are
transferred from cost centers to revenue locations, including automatic
transfer of cost data back to the revenue locations.
This application helped remove very tedious tasks from
a normally arduous budget process. The application is
designed for change and can
easily be adapted for future years or for different budget templates.
| 
