Home > Excel Software Design

Microsoft Excel VBA Macro Programming

Microsoft Excel VBA Programming
02/02
Back <

Dashboards and Problem Visualization Excel Software

Excel VBA can be used to create industrial grade Excel dashboards and various other visualization tools.

Another area that Excel and VBA really shines at is dashboards and in general visual interfaces. The difference between EMAGENIT and other companies in this area is we make them look cutting edge and extremely functional. What we can build for you to visualize your data includes:

Excel Software Design Dashboards

Click to enlarge, scroll < > if hidden.

In the sample Excel software app above, Excel VBA is being used to build charts on a worksheet measuring parameter performances from data stored in 1000+ workbooks (downloads). The user interface for the program is a userform that allows the user to select which parameters to chart and what type of charts to create. It also allows the user to select where charts will be saved and whether to email the dashboard report out automatically.

Project Management Excel Software

Excel VBA can be used to make project management tools affordably that automatically scale.

Microsoft Excel with VBA can be used to create cost effective program management tools that can be rapidly developed in minimal time and are scalable. The tools can use either standard workbooks to house data or pull their data directly from databases. What we can build in this area includes:

Excel Software Design Dashboards

Click to enlarge, scroll < > if hidden.

In the sample Excel software app above, Excel VBA is being used to build project schedules and figure out things like network days, periods, work days...etc. The user interface for the program has many parts including userforms and event driven features that run by interacting with the worksheet. Why not just use MS Project? Because many times not all of MS Project's features are needed and Excel's drawing shapes and worksheet cells coupled with VBA provide the capability to create interactive apps that surpass MS Project.

Database Integration Excel Software

Excel VBA can be used to integrate database information directly in your projects, no download files.

Microsoft Excel VBA and VB.NET posses an incredible array of tools to query and control databases with. The idea is to use Excel as a pre / post database data processor. The idea is to also use Excel as a database visualization tool. We also use VB.NET at this point due to its extraordinary control of databases. Excel VBA can run VB.NET making it seamless to the user. What we can build in this area includes:

Excel Software Design Dashboards

Click to enlarge, scroll < > if hidden.

In the sample Excel software app above, Excel VBA is used to connect to an SQL database and pulls down sample hospital metrics. Shapes representing hospitals are drawn and positioned over the worksheet-based map by Excel VBA and colored based upon whether Net Profit exceeds budget or Net Profit is abnormally high. Drop downs allow the user to select what metrics are being displayed. EMAGENIT has developed many Excel projects over the years that seamlessly integrate and display database data. Companies select the Excel / VBA combo because Excel is second to none at analyzing and integrating multiple databases at once.

Scheduling and Time Management Excel Software

Excel VBA can be used to build robust scheduling and time management tools.

Microsoft Excel and with VBA can be used to create interactive time sheet and scheduler type programs. The Excel worksheet takes center stage here again using its unique abilities for data storage and interfaces. What we can build includes:

All of the above can either interact with worksheet based data or data from a database.

Excel Software Design Dashboards

Click to enlarge, scroll < > if hidden.

In the sample Excel software app above, a user types employee time information into the worksheet interface and Excel VBA calculates the time break down placing hours in the appropriate column. A whole lot of VBA logic is used to determine over time, double time, triple time... depending upon union labor rules. A custom VBA userform is used to display a time-card sheet’s totals and who has overtime. EMAGENIT develops various time management programs for companies because many times the rules are so complex, available software cannot calculate it.

Workbook Correction Excel Software

Excel VBA can be used to sweep through 1000's of workbooks reading, correcting and checking them.

Using Excel VBA, automated programs can be developed that sweep, read and if necessary correct workbook elements, settings and many types of errors. These style programs can rip through thousands of workbooks housed in different folders monitoring them and keeping them up to specifications. If corrected, these changes might include correcting and reshaping tables, fixing and renaming names, redefining ranges, upgrading formulas, fixing queries, upgrading PivotTables...etc. When the programs are done, they can log their change results in worksheets complete with hyperlinks so you can jump to where the problem was located to see what was done. EMAGENIT has designed many of these programs to bring their clients workbooks up to a certain specification.

Excel Software Design Dashboards

Click to enlarge, scroll < > if hidden.

In the sample Excel software app above, Excel VBA is being used to scan 50 workbooks on SharePoint for region information and their projects and organize that data in a 2-week view on a calendar worksheet interface. While scanning the data, the program determines the health of each data workbook logging on a custom VBA userform which have not been updated, which have errors, and which have a green update status. The errors are logged on a different worksheet with hyperlinks that lead back to the workbook with the errors so they can be rapidly fixed.

Powerful Modeling Excel Software

Excel formulas and Excel VBA can be used to create powerful modeling tools that can be used with Solver and incorporate other computer programming languages.

EMAGENIT considers modeling as anything that uses formulas on the worksheet to initiate its primary calculation sequence. There are so many model types, we can not possibly list them all here. What we will do instead is tell you what we generally use to build an Excel model. 

Excel components that you already use. When we develop Excel worksheet based models, we use general formulas, worksheet functions, array formulas, names, Excel tables, Power Query, PivotTables, shapes, charts, Conditional Formatting, Data Validation and ActiveX controls. We use formulas, array formulas and names because they calculate faster than trying to use Excel VBA for all model calculations. Using Power Query and PivotTables allows us to integrate multiple data sources in a model, refresh them and analyze their data. Using these basic features when possible also allows individuals with general Excel skills to manage a model's elements without our help.

Excel VBA to enhance model functionality. While models that use formulas and Excel's built-in capabilities are very powerful, a model's functionality can be boosted many times over by integrating Excel VBA with it. Think of VBA in this instance as a wrapper that manages a model's functionality. EMAGENIT uses Excel VBA to control things in a model like formula creation and fills, model parameter uploads and downloads, cell and range name management, formatting, range clearing, adding and deleting data from tables, data refresh and any type of model reporting. Also when numerical analysis comes into play, we use Excel VBA to create custom worksheet functions that perform those tasks. We also use custom worksheet functions to tie models into other computer languages for greater speed and other programs for "off the shelf" capabilities.

Solver for optimization purposes. We can also tie Excel models into various worksheet problem optimization packages like Crystal Ball and Solver. If you have a model and are trying to find a correct solution, you should be coupling it with an optimization program.

* A note here, user defined worksheet functions or UDF's are constructed in VBA and are executed from the worksheet like a normal worksheet function, think SUM function. They can execute 1000's of lines of computer code from a single cell and work with the like's of Solver or Crystal ball.

Excel Software Design Dashboards

Click to enlarge, scroll < > if hidden.

In the sample Excel software app above, you are seeing a worksheet that controls an entire jet drone model. The workbooks to the right house some of the major drone components. Changing the Design Variables section on this worksheet ripples to all the other workbooks, then their calculations are feed back here to determine range of the drone (Objective Function). Solver is run to change the Design Variables, maximize the Objective Function value and ensure all the Constraints are met. UDF VBA functions are used to calculate various component performances.
02/02
Back <
Need Help? Please call us at 1.805.498.7162

Copyright © 2002-2022

EMAGENIT All Rights Reserved