MacLochlainns Weblog

I had a chance (30 minutes) to play with Excel 2011 on the Mac OS X today. I was shocked to discover that Excel 2011 didn’t appear to support User-Defined Functions (UDFs) like Excel 2010 for Windows. My understanding was that this release would be one where it implemented Visual Basic for Applications (VBA) like Windows. Initially I thought it didn’t but I bought my own copy, did a full install with Visual Basic, and it appears that Microsoft has delivered. Oops, my bad for assuming the machine I played on originally had a solid installation. It appears to have had only a standard installation.

Watch out because UDFs fail with a #NAME! error on a standard install of Excel 2011. While they’re found in the Insert Function dialog in both cases, they only appear to work with a full installation. The downside for Insert Function is that, like Excel 2008, it has no quick poplist to narrow the function choices to groups. We have the big list once more.

Here are my test functions:

Function hello()
  hello = "Hello World!"
End Function
 
Function hellowhom(nameIn As Variant)
  hellowhom = "Hello " + CStr(nameIn) + "!"
End Function

I think I found the trick to get Excel 2011 recognize and run User-Defined Functions. Make sure you do a custom installation and check Visual Basic for Application. Anyway, you can test these functions like that shown below. Column A contains the text of the formulas (a preceding single quote does that), and column B contains actual function calls.

Enabling the Developer ribbon took a few moments when I got my own copy. I figured that updating this was better than making a new post on the blog and linking them. It’s a three step process.

1. Navigate to the Excel menu item and select Preferences…, as shown below.

2. In the Excel Preferences shown below, click the Ribbon icon.

3. In the Show or hide tabs, or drag them into the order you prefer: box shown below, enable the Developer checkbox.

It’s awesome, now accountants and economists can switch to Mac OS X without having to host a virtual machine with Microsoft Excel.

Rebuilding new reference environments on my MacPro, I started with Ubuntu 10.04.01 LTS (64 bit), I had to recall the step to install VMWare Tools. It’s quite simple but I know my students may need the steps to configure a VMWare virtual machine and it may benefit others. While this Ubuntu help page is a good start it isn’t a step-by-step configuration guide.

1. Navigate to the VMWare Menu, choose Virtual Machine and in the drop down menu Install VMWare Tools. This will mount a virtual CD in the Ubuntu virtual machine.
2. Open a terminal session by choosing Applications, within the drop down choose Accessories, and in the subsequent drop down choose Terminal. It will launch a terminal session for command-line entry. The screen shot will look like the following.

3. From the command-line perform the following tasks:

cd /media/VM*
cp VMwareTools*.gz /tmp
cd /tmp
gunzip VMwareTools*.gz
tar -xvf VMwareTools*.tar
cd vmware-tools-distrib
sudo ./vmware-install.pl

4. After starting the vmware-install.pl, accept all the default prompts. Alternatively, as Josh points out enter the following to skip the prompts:

sudo ./vmware-install.pl --default

5. After the configuration completes, you’re prompted to restart the X-Windows. The easiest way is to reboot. You click on the upper right corner to get the drop down menu to restart Ubuntu. A screen shot follows.

This completes the VMWare Tools installation on Ubuntu but unfortunately, you may need to setup the network connection. In a couple instances, the Ubuntu installation appears to have corrupted the VMWare networking process. The result is that the DNS setup on Ubuntu didn’t work.

When the Ubuntu /etc/resolv.conf file is empty. You should first restart the VMWare network. This can be done without rebooting your native Mac OS X. Open a Terminal session, and navigate to the following directory and restart the VMWare network or use this command with backquoting.

sudo /Library/Application\ Support/VMware\ Fusion/boot.sh  --restart

If your Ubuntu /etc/resolv.conf file is empty, you can manually edit it. The last line in this sample depends on your IP subnet. I’ve entered it by assuming that you’re on 192.168.75.0 to 192.168.75.255 with a 255.255.255.0 network mask. You can refer to this prior post for the details on how you find your VMWare NAT subnet.

When your Ubuntu /etc/resolv.conf file is empty, add these values:

# Generated by NetworkManager
domain localdomain
search localdomain
nameserver 192.168.75.2

Hope this helps some folks.