Excel Macros and VBA in Petroleum Engineering

Microsoft Excel is at the heart of most engineering work that is done now. Initially started out as a spreadsheet for data entry, excel has evolved to become a software that is capable of doing anything that you can logically explain it to do. This includes creating forms, calculating the most complex equations (even numerical analysis), running detailed what-if scenarios and even creating games like hangman. When graduating from university, i wasn't aware of the extent till which I will be using excel in my professional work. To be honest, 80% of my working time is spent on updating excel sheets and using modified excel sheets to conduct engineering calculations.

So what makes excel so useful in almost all engineering domains? Well, although excel can handle basic algebra and other formulas very well, excel can be programmed to carry out repetitive tasks by creating Macros. Macros are programs that carry out a repetitive task for the user once its is recorded. For e.g., if you copy a set of data from Sheet-1 to sheet-2 everyday, a simple macro can be recorded in excel onetime and button can be used to carryout this whole process of copy-paste automatically. There is no limit to how complex a task can be when recording macros. As long as the same action has to be repeated, a macro can be used to automate the process.

While macros are magical, they still have a major flaw - they will only replicate the process you recorded for them. For e.g, if the process includes copying data from cell A1 to cell B1, they will keep on doing the same thing rather than moving on the next cell after copying the first. To customize the task, or add functionality that involves more than replicating a task, we can use Visual Basic excel or excel programming called vba.

VBA stands for visual basic for applications and is a programming language used to create added programs for excel (and other ms office applications). The possibility of what can be created with vba is endless. From personal experience, I believe learning vba is very easy as the syntax is

natural english (such as "form1.show" to show a form). With the added advantage of formulas and charts available in excel, it is very easy to create a custom program for engineering calculations using excel and vba. I have created and seen programs as complex as a reservoir simulator and oil-water coning modelling created on excel and vba.

Following is the screenshot of an excel program we created to monitor our campaigns on google adwords. We call it Adwords Analyzer and its a handy tool that helps us in analyzing what is working best in marketing our products.

This is a splash screen that is shown as the program loads.