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
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.[caption id="attachment_2317" align="alignnone" width="363" caption="Use of the standard SUM function in Excel"][/caption]
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
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
4) Use Your Function in Excel[caption id="attachment_2322" align="alignnone" width="387" caption=" Comparison of the newly-created SUM_CAT function to the standard SUM function in Excel"][/caption]
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:
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).
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