Microsoft Excel VBA Software | Microsoft Excel Macros

What is a VBA macro. In our Excel software design, we build a custom Excel based solution for your problem from the ground up. We design both general Excel projects (projects containing formulas, worksheet functions, charts, data validation, conditional formatting, queries, logic...) as well as Excel automation projects using VBA and a host of other tools that help with Excel automation. The types of Excel software projects we build include:

• Data Processing
• Data Fusion
• Data Mining
• Report Generation
• Advanced Dashboards and Visual Interfaces
• Project Management
• Databases
• Scheduling and Time Management
• Web Integration
• Inventory Management
• Automated Workbook Corrections
• Modeling

 At EMAGENIT, we use Microsoft Excel as a software development environment because of its flexibility, rapid construction and its ability to flat out do things that other software environments can not do "off the shelf". These "off the shelf" capabilities also makes it very cheap to develop software in. It is also, in our opinion, the premier environment to visual data, integrate data and to perform modeling in.

All EMAGENIT Excel software design is done in the USA by EMAGENIT staff. We do not farm your project out to different countries and programmers. Your proprietary data and company strategies always stay safe.

When necessary, we also combine other computer languages with our Excel VBA software designs to get the best of all worlds. This again exploits the "off the shelf" concept so you do not have to pay for us to build things that already exist elsewhere. We will use Visual Studio and its elements along with various databases and various web codes to accomplish our tasks. Excel VBA integrates seamlessly with these environments.

EMAGENIT works on Excel software projects of all size, difficulty and duration. Our Excel designs cover the most basic of tasks up to projects that can take a few months or years to develop. We can develop simple automation projects to the most advanced modeling, data processing and dashboard style projects you can think of.

EMAGENIT works on Excel software projects of all size, difficulty and duration. No Excel project is to small or big.

As mentioned above, EMAGENIT can integrate other computer languages, databases and programs into your Excel solution to help get the job done. This is what sets us apart from other Excel software developers. It also allows us to do a lot of "how did they do that" type of things. We work in Excel, we use it as our main development platform because of its strengths, but EMAGENIT also uses any of the following below to create a truly unique custom Excel software solution. Basically if it can be tied into Excel in some manner, we use it.

• Visual Basic for Applications (VBA)
• Databases like SQL server, Access, Oracle,...
• Web based databases like MySQL, MS Access,
• Visual Studio using VB.Net and CSharp
• Perl, MacScript...
• The web using VB.NET, JavaScript, CSS, HTML, ASPX, ADO.NET, web based charts,...
• Other Microsoft products like Word, PowerPoint, Outlook,...
• Text files
• Web pages including generating, reading and writing to them

EMAGENIT can integrate other computer languages, databases and even programs into your Excel solution. This is what sets us apart from other Excel developers and allows us to do a lot of "how did they do that" type of things.

We like to say all of the above is just kind of getting warmed up with what we can actually do for customers. We call it hybrid software design, take the best of each tool using its "off the shelf" abilities and combine them together. And by the way EMAGENIT does its own graphic design and builds its own website, like the one you are using right now. It is a good example of our work if you are wondering what our customer projects look like visually and functionally (unfortunately we cannot discuss any of our customer projects on this page due to NDA's).

Back to Top

Microsoft Excel and VBA have the capability to process data from all different types of data sources and data configurations. Using VBA along with the Excel worksheet, high powered data processing systems can be developed in minimum time and with minimum cost. The type of data processing tools we can build include:

• Excel macros that process data located in multiple worksheets
• Excel VBA software that process data located in single and multiple Excel workbooks
• Excel programs that process data located in multiple Excel workbooks and multiple Windows folders
• Excel macros that query (SQL) and process data located in workbooks, text files and databases
• Excel VBA programs that filter and sort worksheet data
• VBA programs that can rearrange worksheet data into different worksheet table configurations
• Excel macros that can summarize parameter information from different data sources and create worksheet summary tables from it
• Excel VBA macros that can take raw data and fill in worksheet tables and forms
• VBA programs that take raw data and generate multiple workbook and worksheet table reports
• Automated construction of pivot tables and their supporting tables from raw data using Excel VBA
• Automated construction of worksheet queries using Excel VBA
• VBA macros that communicate with Google Gmail and Google spreadsheets downloading and uploading data

*For actual report types see Report Generation below

Back to Top

One of the biggest problems facing business today is that their data is spread out over multiple data sources that do not talk to each other. The solution is not to build another data source and try to import everything into which by the way never works out as billed. The better way is to integrate what you have and get it talking to each other. Excel VBA along with the worksheet again can be utilized to read and integrate data from different data sources that can then be used for various exports and dashboards. The data fusion tools web can build includes:

• Excel VBA software that can read multiple workbooks, 100's even 1000's,  pulling key information from worksheet tables and consolidating it
• Excel VBA macros that can scan worksheets in different workbooks for key information, find it, then consolidate it
• Excel macros that can read multiple data sources like workbooks, text files and, databases and fuse that data into useful tables
• VBA programs that pull data from different type databases and integrate their data into useful tables
• Excel macros that can read data from text files or worksheets, clean it and prep it, then upload it in the proper format to databases like Oracle, SQL Server and Microsoft Access
• Excel VBA macros that can transfer data from one database type to another getting databases that cannot talk to each to talk

*For report types see Report Generation below

Back to Top

The condensed definition of data mining is defined as the process of detecting patterns in data using various methods. While this is a very broad field with many facets, Excel VBA and the worksheet shine at detecting patterns in data and then processing the data based on those patterns. It is the worksheet and its "built-in" attributes that really makes it happen. What we can build in this area includes:

• VBA programs that can detect and fix anomalies in worksheet, text file or database data and then process it. This includes everything from detecting data errors to detecting data records that exceed specifications
• VBA macros that can detect incorrect records and information in worksheets then flag them in separate tables or with colors, fonts,...
• Excel VBA programs that can detect incorrect records in text files and databases and then report them in a worksheet table
• Excel VBA software that reads corrupted text file data, corrects it, then processes it. Very big with programs that output their data as text file downloads and use spaces as delimiters
• Excel macros that read stacked worksheet tables, tables with multi row headers, tables with changing headers, adjusts and processes them
• Excel VBA programs that search for relationships between worksheet tables and then processes the data using the relationships
• Excel macros that search worksheet tables and text files to see if information from one is contained in the other and then either flags the results or uses them
• Excel programs that scan indented worksheet tables, the data is indented by columns on the worksheet, and then processes it. Very big in business programs that download to Excel

*For report types see Report Generation below

Back to Top

Microsoft Excel coupled with VBA is an incredible report environment with the capabilities to make all different types of report formats. Using VBA along with charts, the worksheet, formulas, pivot tables, pictures and drawing shapes, your imagination is pretty much the limit of what report type can be generated. VBA can also be used to transfer data to a variety of other programs including Word, PowerPoint, Outlook... basically anything that VBA can command.

After the raw data is processed using the data processing, data fusion and data mining techniques mentioned above, this is what we can build for you to visualize your data:

• Excel VBA software that automatically creates and manages Windows folders placing reports in them
• Automated summary worksheet tables
• Automated chart construction of all different chart types
• Automated chart construction of multiple embedded charts on multiple worksheets using drawing shapes to overlay and mark data
• Automated pivot table and pivot chart construction on multiple worksheets
• Excel VBA software that automatically fills out worksheet forms and tables
• Automated illustrations of table data including time lines, processes, plant layout, maps, technical drawings,...
• Excel  macros that transfer data, illustrations and charts to Word and PowerPoint

Back to Top

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 cool. What we can build for you to visualize your data includes:

• Various VBA automated charts that display key performance indicators, metrics and information of interest
• Various technical schematics, 2-d layouts and process diagrams
• Plant layout and product tracking through the plant

Back to Top

Microsoft Excel with VBA can be used to create cost effective program management tools that can be rapidly developed in minimal time. 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 VBA software that turns worksheets into various colored displays that display project status's
• VBA macros that arrange and integrate project status into weekly and monthly worksheet displays
• Excel macros that visualize project status and deployment via timelines using worksheets and drawing shapes
• Excel VBA software that visualizes project milestones as timelines using worksheets and drawing shapes
• Interactive project status worksheets that when clicked on display project specifics, team members,...
• Excel macros that can automatically display project resource allocations as charts and other worksheet based reports

What makes a system like this so flexible and easy to upgrade is when the database is taken out of the picture and replacing it with standardized workbooks housed on SharePoint. Queue the preverbal hearing the pen drop in the room. With the database out of the way, upgrade efforts focus on the visual displays and reports, not upgrading the databases, the queries,.... Adding project information becomes as simple as adding an extra row of data in a specified place in a workbook. We know, we have built systems like these that integrate hundreds of team workbooks and they consistently beat out the larger programs because of their flexibility and ease of use.

Back to Top

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 and 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:

• Automated database queries using Excel VBA and VB.NET
• Excel macros that talk to Oracle, SQL Server, Access, workbooks, text files...using SQL
• Automated database record inserts, updates and deletions using Excel VBA and VB.NET
• Excel VBA programs that visualize database data on the worksheet using cells, drawing shapes, pictures and charts
• Excel VBA software coupled with VB.NET that process database data and output it to the Excel worksheet
• VBA macros coupled with VB.NET that upload worksheet based data to various databases

Back to Top

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:

• Various automated worksheet based time cards using Excel VBA
• Various automated worksheet based appointment tracking programs using Excel VBA and VB.NET
• Various automated worksheet based personnel and project schedulers using Excel VBA and VB.NET
• All of the above can either interact with worksheet based data or data from a database

Back to Top

Microsoft Excel, VBA and VB.NET can be used to read and control web pages and web based databases. VB.NET is especially valuable here with its built-in capabilities and again VBA can communicate with it seamlessly. What we can build in Excel VBA includes:

• Excel macros that can read and control web based databases
• Excel VBA software that can read web pages and their contents such as forms
• Excel VBA software that can scan web pages for certain html components like tables, divs,... and return that information to worksheets
• Excel macros that can control web pages and run their code like JavaScript, VB.NET, CSharp,...directly from Excel

Back to Top

Microsoft Excel and VBA can be used to supplement inventory management programs. While it is possible to build one from the ground up using Excel, VBA, VB.NET and SQL Server, you do not really want to do that because there are a lot pre-made ones already in existence. That said, Excel and VBA can be used to supplement these programs by tacking on capabilities they lack, especially the older systems. What makes Excel so well suited for this task is the worksheet and its simple ability to act like a scratch pad and insert, move and delete data. Believe it or not, that is what makes it all work for this type of problem and makes Excel such an attractive place to create this type of program in. What we can build in this area includes:

• Excel VBA software that reads inventory data and tell you what inventory you need to order and what date
• Excel VBA programs that read inventory data and tell you what inventory you are getting low in
• VBA programs that can re-sequence inventory balancing the inventory you have on hand removing unnecessary orders or adding orders
• Excel macros that can flag inventory based on specific order triggers
• Excel programs that build new worksheet based reports that visual inventory states

Back to Top

Using Excel VBA, automated programs can be developed that sweep and correct workbook elements, settings and many types of errors. These style programs can rip through say a thousand workbooks, housed in different folders, and make global changes to them. These changes might include correcting and reshaping tables, fixing and renaming names, redefining ranges, upgrading formulas, fixing queries, upgrading pivot tables...etc. When the programs are done, they can log their changes 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 spec so Excel VBA software can then be designed to use them.

Back to Top

EMAGENIT considers modeling as anything that uses formulas on the worksheet to initiate its primary calculation sequence. EMAGENIT builds custom models for business, engineering and science from the ground up. There are so many model types, we can not possibly list them all here. What we will do instead is specify what we can do as far as modeling capabilities when we construct them. Normally when EMAGENIT is asked to develop a custom model, the customer specifies the equations they want to use and what outputs they want to see to so it can be a fusion of several categories of model types.

Basic Worksheet Models

EMAGENIT develops basic formula and worksheet function driven models when the customer wants individuals with general Excel skills to be able to manage them after they are developed. We will also use built-in Excel data features to enhance their operational abilities. What we can build includes:

• Basic worksheet models using formulas and various worksheet functions
• Adaptive worksheet models using formulas, worksheet functions, logic and names that react autonomously to changing information
• Adaptive worksheet models using formulas, worksheet functions, logic and names that scan for information and do advanced table lookups
• Worksheet models that query various databases, workbooks and text files integrating and using their information
• Worksheet models that use advanced array formulas and logic to perform calculations
• Worksheet models that tie into the web and query information
• Worksheet models that use Solver and Crystal Ball

VBA Enhanced Worksheet Models

While models that use basic formulas are very powerful, one of our modelers use to build aircraft models using just that, the real kick comes from integrating VBA with them. Think of VBA as being a wrapper around the model that controls it and some amazing things can be done. What we can build includes:

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 the SUM function. They can execute 1000's of lines of computer code from a single cell and work with any style application like Solver or Crystal ball that drive the worksheet. In other words, they make an array or normal formula look like a model T.

• Worksheet models that employ advanced numerical techniques (forecasting, statistics, simulation, curve fitting, integration,..) using user defined worksheet functions (UDF's)
• Worksheet models that employ advanced logic using user defined worksheet functions (UDF's)
• Worksheet models that employ advanced mathematics (use your imagination here, read the next line) using user defined worksheet functions (UDF's)
• Worksheet models that that tie into other computer languages and run their code using user defined worksheet functions (UDF's).
  • This is primarily used for calculation speed and to get at mathematical libraries hosted in other computer languages like the IMSL libraries
• Worksheet models that perform advanced table lookups and data integration using user defined worksheet functions (UDF's)
• Worksheet models that perform advanced database queries and data fusion using user defined worksheet functions (UDF's)
• Upload and download VBA programs that control your model information storage.
  • In other word, do not make 1000 copies of your model storing a different model in each workbook
• Batch file VBA programs that can load stored models into your model and track and organize the output

What is an Microsoft Excel Macro

An Excel macro is a computer program that is written or recorded and is stored in a workbook file. The correct name to use instead of macro is actually VBA procedure or VBA for short, but we will talk about that in the following paragraph. For now understand both mean the same thing in modern times and their names are used interchangeably when discussing automation in Excel.

When a macro is run, it can perform a series of automated tasks in Excel such as copying and pasting, rearranging data, automated chart creation, worksheet formatting, data processing, pivot table construction... the list is endless. Microsoft Excel macros are also capable of executing sophisticated logic instructions as well as performing iterative tasks like going through a folder for a specific file or scanning Excel for an open workbook or a specific worksheet for a value. When you think of automating Excel with macros, just think of what you do manually except computer code is written to do the same thing.

The word Macro is actually misleading being confused with the earlier Macro language that Microsoft Excel used in the early 90's. The macros back then where stored on what looked like an Excel worksheet, these sheets being called Macro Sheets. They still exist in Excel today, if you right mouse click over a worksheet tab and select Insert/MS Excel 4.0 Macro you can see one (do not use, however, as they are obsolete). The code on these sheets look like a series of formulas. An Excel macro today is actually a Visual Basic procedure, which is written in VBA which stands for Visual Basic for Applications (What is VBA). So people use the terms VBA macro, Excel macro, Excel VBA macro, Excel VBA... to describe the newer VBA code as mentioned above.

Where are Excel VBA Macros Stored?

When commanding Microsoft Excel, you usually write a VBA Sub procedure or in some instances a Function procedure. These procedures are stored in a workbook file in a module and can be viewed in the VBA Editor by pressing the Alt+F11 keys while in Excel. When the workbook file storing the VBA macro is distributed, the code also goes along with the workbook. When the workbook is opened, its Excel VBA code is opened. When a workbook is closed, the VBA procedures are closed. Once a macro is saved in a workbook and the workbook reopened, the VBA macro must be enabled for it to run. Microsoft Excel will prompt you with a run dialog box or a run/enable button which appears underneath the formula bar. When Excel VBA code is saved in an Excel 2007 workbook and above, the workbook file must be saved out as an Excel Macro-Enabled Workbook or the code will be lost when the book is closed. Excel 2003 and below does not make this distinction.

What are the Capabilities of Excel VBA

An Excel VBA Macro is not only capable of automating the Excel environment, it can also command other programs that are VBA compatible. In other words, you can be in Excel and command Word, Internet Explorer and SQL server in the same procedure. A macro can also talk to other computer languages like C#, VB.NET and FORTRAN (yes FOTRAN, lean, mean and chuck full of libraries for numerical analysis). What this brings to the table is being able to access capabilities and speed that VBA does not process. Do not underestimate the power of Excel VBA, it has Excel at its command, other programs, other languages and can do the most sophisticated mathematics and analysis that you want to do. Developing VBA macros in Microsoft Excel takes about a sixth of the time as other traditional programming environments (sometimes quicker) and can flat out do things that are impossible in other computer languages without a great deal of effort. There are many EMAGENIT projects that started as Band-Aids till bigger systems were developed and then took over as the main programs because the other systems could flat out not be developed.

Why Recording a Microsoft Excel Macro Does Not Accomplish Your Goal

A lot of books and consulting companies push that being able to record a macro in Excel is all you need to put together a program. In some instances, consulting companies will claim to know how to write macros for Excel and actually just switch on the recorder and edit the code a little. Plugging that all you need to know is how to use the Macro Recorder to control Excel is a very false misleading statement. The Macro Recorder is really an ineffective tool when used to try to build an Excel VBA Macro from scratch. The biggest failure comes in the way it records the object expression (path) to what you are trying to control in Excel. Object oriented programming revolves around the single concept that you create an object expression (think of the way you drill down Windows Explorer) identifying what you are trying to control then tell that object (program element) what to do. The recorder does not record code this way, it uses an active strategy which means it will generate code that will toggle the workbook to the worksheet to get you into position, then use a Selection command most of the time to identify what you want to control. The following is the way the Excel Macro recorder typically records:

Sheets("Sheet1").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet3").Select
Range("E5").Select
Selection.PasteSpecial

The problem with the code is that it is not robust in execution and very vulnerable to errors. Notice it does not even identify the workbook to control which could be a very big problem. In some instances, the recorder will not even record a task or record it only partially. In reality, the macro recorder is only really good for figuring out the command you want to carry out like Copy or PasteSpecial and how they look when typed (syntax). In recent versions of Excel, the recorder does not even do that very well either. Also the recorder does not put in place logic, loops or variables which are key to creating a functional Excel VBA macro. If you hire a firm to create a Microsoft Excel VBA program, be sure to be on the look out for a lot of Select, Selections and Actives in the computer code. If you see this, they probably used the recorder to write the macro in which case you should ask for a refund.