Excelling With Excel #1 – Custom Functions Using VBA

October 29, 2014

AIChE

Share This Post

1/4 in the series Excelling With Excel

This fall we're featuring some of our top posts of all time. This great Excel series, which starts with the post below, is a perennial favorite. Check it out If you've not yet had the pleasure. Keep an eye out for more all-time favorites through the end of the year.

Could you imagine your workplace without Microsoft Excel? Absolutely not.

Excel is the language of business from corporate offices on down to manufacturing facilities. When a firm hires a new employee, it is assumed they are 1) breathing and 2) have acceptable Excel expertise.

.. oh, I'm sorry. I've forgotten to introduce myself. My name is Greg, and I'm an orange cat.

With the permission of my young professional ChE friend and the use his delicious Apple laptop, I've decided to contribute to the AIChE ChEnected community.. and I will do this through the use of my soft, orange paws to blog regularly about Excel.

So, without further ado, allow me to introduce Tip#1:

Tip#1: Creating Your Own Functions

Just a disclaimer: This first tip is a little involved, simply because it involves the use of VBA (a programming language), but it is not difficult!

The Skinny on Excel Functions

Excel functions are easy! The difficult part is knowing which one to use. A nice, complete list of Excel's functions can be found here. All you gotta' do is type them into a cell.

Use of the standard SUM function in Excel

Making Your Own Excel Function (!!!)

Alright, crack your paws and get into a comfortable position; this will require some typing. The first thing we need to do is open the Visual Basic editor.

Example - A Simple Modified 'SUM' Function

1) Opening the Visual Basic Editor

Press Alt+F11

2) Open a Module

3) Type/Create Your Function

Type the following into the module:

Function CAT_SUM(x as Integer, y as Integer) CAT_SUM = x + y End Function

CAT_SUM function typed into a module in the Visual Basic Editor

4) Use Your Function in Excel

Comparison of the newly-created SUM_CAT function to the standard SUM function in Excel

Notice the SUM function we had used before has the same result as the CAT_SUM function we have just created.

The Anatomy of A VBA Excel Function

Now that you've seen a quick example, allow me to share with you the basics of how VBA Excel functions are created:

General Layout of VBA Functions

If this function looks familiar, it should. It's the CAT_SUM function in the example above.

The function's beginning is marked by the Function Declaration, and the function's end is marked by the End of Function Declaration. In between, we have the Body of the function in which calculations are performed (usually involving any indicated Input parameters).

Summary

Custom VBA Excel functions can be used to simplify your spreadsheets, but, because they require knowledge of rudimentary VBA, they do require some patience to learn. However, once you've gotten a hold on them, you can do some pretty amazing things (much more complicated than a simple sum)!

If you have any questions about the content of this post, please contact my young professional ChE friend, Todd Krueger.

- Greg the Orange Cat

Cat at Computer Image:http://cleantechnica.com/files/2010/01/computer-cat-witness.jpg

Comments

I don't want to be difficult, but I would ban Excel form doing serious engineering calculations! To be fair it has its place in a limited number of instances but in general, No Thanks! I could elaborate but simply, in response to your question "could I imagine my workplace without Excel?" - the answer is "easily". Best regards.

Excel isn't optimal for many situations, but I certainly couldn't imagine my workplace without it. We do all sorts of analyses with excel as well as prepare system uploads. It is used in all sorts of regular forms and production tools. From an overall look at the company and its use of Excel, its pretty flexible - it can handle very simple tasks (just listing numbers) and very complex ones (sophistocated macros). Some (small) companies actually use excel to store all of their sales and inventory data, a kind of pre-ERP.

Ray, I definitely agree about the "serious engineering calculations" part. Pullin' an Aspen in Excel would be immensely difficult (and foolish)!

Actually, Aspen has developed functions and commands that can be used in Excel VBA. In fact my company right now is trying to put together a program that will extract process data from HYSYS and put it into Excel to be placed into Material Balances and equipment datasheets. Also many hydraulic programs are based in VBA or other programming languages. So to say you could live without Excel is possible, it'd just take you twice the amount of time to do it.

Dear Cat; I liked your posting. How about another one using something other then the Integer ? I always wanted to learn how to use macros...is this a type of macro? David

David, here is a similar example using a double (a number with decimals): http://content.screencast.com/users/LittleKrueger... I'll have to make the 'How to Make / Write / Use Macros' post in the near future..

David, I recently needed to write a macro but had never done it before. I found the book, "Excel Programming" by Jinjer Simon very helpful. Now that I know how, I find myself writing macros all the time. Also, I've found that if I google what I need to do along with "vba", there is a helpful post on a programming forum somewhere.

Dear Greg the Orange Cat, Thanks for the tip! I will definately use this. And I will have to echo the hopes of a future macro post! The world of VBA can get a little scary when you don't know what you are doing.

Greg, I'd like to hear more about using excel to extract data from databases and other systems as well. My excel usage is very rudimentary as I only use it to track my projects. Can macros be used to extract data straight from databases or only from reports created by the databases? P.S. I like your style. Lets meet at the Three Blind Mice restaurant tonight, say around meow:30. I heard the cat-nip is marvelous.

Macros (VBA) can do just that. That will have to be a subject of future post..

Cat - you say you use mac, but then demonstrate in Win Excel, dastardly no? Wikipedia says macro-writing is no longer possible on Mac Excel, do you have a workaround? Is the what the "Automator Workflow" scroll-looking menu the replacement for VBA macros? Do you ever use this feature?

Greg doesn't use a Mac. He was simply capitalizing on the fact that he was pictured using a Mac in the article. Greg, in fact, has never used Excel on a Mac. (he's sorry for the confusion) -Todd on behalf of Greg

Thanks. FYI - I think (haven't tried) that Mac Excel can load and run Win Excel macros, just not record, write, or edit.

P.S. I like the first two excel tutorials, please keep it up. Can you touch on using Excel VBA to interact with other applications? And the late Dr. E. B. Nauman would suggest all calculations can be done in excel as long you are willing to run your script overnight or over the weekend. An underrated tool.

Like Greg, I use VBA. I think Dr. E. B. Nauman is right, but I will say VBA is limited. If not implemented judiciously, VBA can make a spreadsheet unusable, and get you into situations that you don't want to be in (e.g. being responsible for a 'broken' spreadsheet at work). They can also be enormous (non-value-added) time sinks. That said, VBA is still very useful! (and Greg is working on a Macro/Function/VBA/Programming post right now. he'll try to include something about interactions with other applications in there too)

What exactly is better programming language to start off with java or vb?

I just want to add a short note to the above discussion! I think we should know for what reason we use Excel! I think any problem needs its suitable tool to solve! We don't want to do CFD on Excel nor a 2D dynamic simulation of a heterogeneous reaction in a fixed bed with VBA-Excel! In ChE we use it for simple calculations, for data presentation. We use its table capability, simple connection to other application for data extracting and so on... For these cases Excel is more than enough! Sometimes ago there was a debate on using C or Fortran or Matlab for ChE undergraduates. The final answer was any of those software has its own specific area of application! We use Fortran for number crunching, after more than 50 years it is still the best! Matlab is for rapid prototyping and simulation, calculations (not heavy) and quick visualization and C is for system programming and C++ for application development, so none of them can discard the others. I will later add a column on how to use Excel as a great user interface for much sophisticated calculation. It is on how using Fortran libraries with Excel.

Thanks for sharing your insights on Microsoft Exel. Appreciate the tips!

Hi Greg, Thanks for your post, I was always dreaming to put my work in excel into a VBA function, in this connection I would like to ask on how could we possibly transform this formula into a VBA function?

Hi everyone, can anyone tell me if it's possible to use my own defined function or to call it from another Module whether it's custom function or normal macro

Thanks.

Thanks. You can find good tutorials at: http://www.excelonlinetutorials.com

Ray, now I'm really curious

Ray, now I'm really curious about your comment... you are really talking about simple "cell" calculations, aren't you?... I'm not sure if you are talking about writing command lines (VBAs) just like we do in Fortran, Matlab... I do agree that the processing capacity does not allow us to go as further as we can with the 2 example above, however, we definitely can have some fun...