Excel Bi-Directional Temperature Converter

A revised version of this content is now available at blog.officewizkid.com


I recently received a question on how to develop a bi-directional temperature converter in Excel. Sounds simple enough but the trick here is the bi-directional statement.

The requirement is as follows:

Let’s assume that Cell B4 represents temperatures in Fahrenheit and Cell D4 represents temperatures in Celsius. Changing one of them will need to automatically change the other one.

The end result should look like the following:

Excel Temperature Converter

Developing an expression that can do a one-directional conversion form Fahrenheit to Celsius is easy. Just type in D4 the expression:


=(B4-32)*5/9

Developing an expression that can do the reverse (still a one-directional conversion) form Celsius to Fahrenheit is easy. Just type in B4 the expression:


=D4*9/5+32

But doing both is much tougher. It creates bi-directional dependency between two cells that as far as i can tell can not be done with simple expressions.

The workaround I found is by going down-level to the VBA level and writing a macro to handle a “worksheet change” event. The event will be triggered every time a cell is changed, making this solution a little bit heavy when dealing with large Excel files. It also requires the Excel to be saved as .xlsm or Excel Macro-Enabled Workbook.

Here is a capture of the code:

Excel Worksheet Change

Let’s review these statements one by one.

First please open the VBA editor in Excel (see old post about that) and use the drop-down lists to first select “Worksheet” and then “Change”. This will generate for you an empty Private Sub called Worksheet_Change.

The logic here is not too complex and has two parts:

Check that the B4 (or D4) cell was part of the Target change range, for that we check if the intersection between Target and “B4” is not empty.


If Not Intersect(Target, Range("B4")) Is Nothing Then

Next we use the “ActiveSheet.Range” function to select cell “D4” and set its value based on conversion calculation as a function of “B4” value.


ActiveSheet.Range("D4").Value = (ActiveSheet.Range("B4").Value - 32) * 5 / 9

Once completed, we can exit from that Sub.

 

Advertisements

Excel Global User Defined Functions

Let’s assume you developed a great user defined function (See here and here). The user defined function (UDF) is available for this specific Excel file and works as you would expect.

But what happens when you need to create a new Excel file that also needs to use the same UDF?

For now, you simply have to re-create the UDF manually in every Excel file you create. And that is not really great from couple of reasons: First it is annoying. Additional manual steps to do every time you create a new Excel file. The second reasons is that if you decide to improve the UDF, you now have to go back to each Excel file and manually implement the change in the UDF again. And that is really not fun and a potential bigger issue with multiple revisions of the same functions running around your file system.

Luckily Microsoft provides a way to create Global UDFs. It is global in the sense that it is automatically enabled to every Excel file running in your machine. The trick is to turn the UDF inside an Excel Macro-Enabled-Workbook file (.xlsm) to an Excel Add-In file (.xlam) and save this new file in the right location so it is always enabled.

As a reminder this is our beloved Half function:

Function Half(value)
Half = value / 2
End Function

Instructions below explain how to convert the workbook to Add-In and how to enable it:
1. Edit the Excel Macro-Enabled-Workbook file (.xlsm).
2. In Excel, go to file properties and change the title to a meaningful name. Let’s say “Global-Functions”.
3. And save-as Excel Add-In (.xlam)
Save-as Excel Add-in

4. Go to the Finder and copy the new file Global-Functions.xlam to the Excel’s Add-In folder.
If is a rather “secret” folder and it changes from Windows to Mac and across every Excel version.

In my case for Excel 2011 on Mac it is in this location:
Applications->Microsoft Office 2011->Office->Add-Ins

5. The last thing we have to do is to enable the Add-In. Open Excel. Click on the Tools menu. Select the Add-Ins… option. And a pop-up windows will be opened.

6. Click on the checkbox to enable the Add-In.

Excel Enable Add-In

And that’s it. Now every time you create a new Excel file the UDF will be automatically available.

As always, more information is available in the Microsoft site here.

Excel User Defined Function With Optional Parameters

In a previous post (long time ago) we talked about creating a simple user defined function (UDF) called Half:


Function Half(value)
Half = value / 2
End Function


In this post I would like to talk about UDF that accepts optional arguments.

Here is a simple example for that case. Requirement to build a function named “Div”:

  • The function accepts as one argument named “Value”.
  • The function also accepts a second argument named “divideBy”.
  • As you can guess, “Div” calculates a division of “Value” by “divideBy”.
  • But “divideBy” is optional. If “divideBy” is not present then the function assumes a constant value of 1. I.e. Divide “Value” by 1.

To meet these requirements we need two things:

The first thing we will need is the ability to define an argument as optional: VBA provides a simple way to declare arguments as optional by simply prefixing the argument with the text “Optional”.

The second thing we will need is the ability to know if a given argument is present or not: For that VBA provides another very simple function named “IsMissing”. The function accepts an argument and returns a boolean. Value of “True” means that the argument is not present.

With that we have everything we need to write the function Div:

2015 VBA user defined function with optional parameters

Very simple indeed! Give it a try!

Also read some of the additional information at:

IsMissing Function

How to describe your Excel User Defined Function

In the previous entry (http://wp.me/pW1Iy-f9) we talked about “Excel User Defined Functions” and we created a very simple example of such custom function called Half. This function receives a single numeric argument and calculates its half value.

It this entry I will be explaining how to add descriptions to the function and its arguments so it will look and feel like any other pre-built function. There are some limitations and I will describe that as well.

The first thing to do is to open a VBA window. To do that, select the “Developer” tab and click on the left-most button labeled “Visual Basic”.

In the editor just type the following text:

Sub Half_Descriptor()
    Dim argDescription(1) As String
    argDescription(0) = "A numeric argument"
   
    Application.MacroOptions Macro:="Half", _
        Description:="Recieved a single argument and returns its half value", _
        Category:="My Category", _
        StatusBar:="Returns the half value of a numeric argument", _
        ArgumentDescriptions:=argDescription
End Sub

The Visual Basic editor will take care if understanding that this is a new Sub and will treat it correctly. The new Sub is very simple and is making one call to the Excel API to a method called Application.MacroOptions. This Method accepts the name of the UDF, the description, optionally help and an array of argument descriptions. It also can accept a name of a Category or a number representing one of the out of the box Categories for easier hierarchy to find your new Function.

More information can be found here:

http://msdn.microsoft.com/en-us/library/ff838997(v=office.14).aspx

Next step is to run the function from the VBA developer environment. To do that, make sure that your mouse pointer is pointing to the code of the Help_Descriptor sub, and click F5. It will run the Sub and you are done.

Now the new function will appear in the expression Function wizard, i.e. when you flick on the following button:

2013-09 Excel Describe User Defined Functions.2

Now the Half function is there:

2013-09 Excel Describe User Defined Functions.3

And also there is a wizard for each argument that looks like this:

2013-09 Excel Describe User Defined Functions.4

All of that is great. But here comes the bad news. There are still limitations. The one thing that bugs me most is that I could not find a way to add the description also to the tooltip of the formula typing box. Same as there is one for out of the box formulas such as Average:

2013-09 Excel Describe User Defined Functions.5

This is something that will be a great addition for a future Microsoft Excel release…

Excel User Defined Functions

Excel has a rich set of pre-defined functions: Functions for math calculations, finance calculations, date manipulations and many more. But as rich as it gets, there are always cases you need to build your own special function and you want to use it in the same ease of use as you would use the pre-defined functions.

Luckily, Excel supports the development of custom functions or “User Defined Functions” as Microsoft calls them. In this blog entry I will demonstrate the basics of this concept using a simple example: A custom function called “Half” that accepts one parameter and divide it by two.

The first thing we will need to do is to expose the “Developer” options that are needed for development of user defined function. Right-click on the Excel upper ribbon and select the “Customize the Ribbon” option:

Excel User Defined Functions.1

Then make sure the “Developer” main tab is checked.

Excel User Defined Functions.2

At this stage a new “Developer” menu should appear and look something like the following:

Excel User Defined Functions.3

In the newly added “Developer” Ribbon click on the “Macros” button to create our new function. A pop-up window will be opened. It is a little bit strange window. When one first open it and there are no already defined macros, all the buttons are grayed and you may find yourself asking “What now?”. So don’t worry, just start typing the name of the function in the “Macro Name” field at the top of the window and the “Create” button will get active. In our case we want to create a function called “Half”:

Excel User Defined Functions.4

Click on the “Create” button and a new window will get opened. This is the developer environment for user defined functions (and other macros).  The language we will use is called “Visual Basic for Applications” or VBA and you can read more about it here:

http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx

If you follow the instructions thus far your screen should look like this:

Excel User Defined Functions.5

For our use case, we will change the type of the macro from Sub to Function. We will also accept a parameter called “Value” and finally we will divide “Value” by 2 and return the new value by assigning it to an implicit parameter that has the same name as the function. In our case “Half”.

The code will look something like the following:

Excel User Defined Functions.6

Switch back to the Excel windows and let’s give it a try:

Excel User Defined Functions.7

Try the function with constant values or with reference to other cells:

Excel User Defined Functions.8

You have just completed the definition of your first User Defined Function in Excel.

The last weird thing happens when you try to save the Excel file. You will get a notification like the following:

Excel User Defined Functions.9

Click “No” and make sure that you save the Excel file as a Macro enabled file. In simple words, file type should be “xlsm” and not the regular “xlsx”.

More information and examples can be found here:

http://office.microsoft.com/en-us/excel-help/create-your-own-worksheet-functions-HA001054846.aspx