MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Excel 2011 without UDFs?

with 18 comments

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.

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

  1. 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.

Written by maclochlainn

November 4th, 2010 at 3:40 pm

18 Responses to 'Excel 2011 without UDFs?'

Subscribe to comments with RSS or TrackBack to 'Excel 2011 without UDFs?'.

  1. Looks like this site has new features, and this site has some screen shots and some info that might help you out.

    Hope that helps!

    Craig

    4 Nov 10 at 4:00 pm

  2. I have Mac Office 2011 installed, including VBA. Functions work fine within VBA but I still get the #Name! error if I try and use a function in the worksheet. Any ideas?

    Richard

    8 Dec 10 at 5:59 pm

  3. Richard, Did you perform a standard installation or custom installation of Mac Office 2011?

    maclochlainn

    9 Dec 10 at 10:38 pm

  4. I have same error — #Name!

    I tried,
    =Sheet1.foo() –> (brings up a list of user-defined functions) –> “That name is not valid.”

    =foo() –> #NAME! error

    I have a version from “home use program” but Microsoft says this is same as regular version. I am not able to perform a custom installation — how do you do that?

    brandon

    10 Jun 11 at 2:15 pm

  5. Brandon,

    Check this VBA UDF tutorial I wrote. Step #3 explains you need to insert a module and then put your code there.

    maclochlainn

    11 Jun 11 at 12:43 pm

  6. Hi, I saw this post and, as I was having the #NAME! error, decided to reinstall my excel with the custom installation. However, after the first try didn’t seem to change anything, I thought I needed to trash my original excel install to reinstall a full version. Now, however, it won’t open at all — it keeps saying “Microsoft Excel cannot be opened because of a problem….etc….you may need to reinstall the application…” however repeated re-installations (and removing it from the trash) haven’t helped. Any ideas on how to fix this, or am I just an idiot for deleting my first version? Thanks…

    littleslips

    13 Jul 11 at 3:12 pm

  7. I’m not sure how you removed it but I’m assuming you shut it down and deleted it from the Applications folder, right? Did you reboot before re-installing?

    maclochlainn

    13 Jul 11 at 10:32 pm

  8. Yeah, I deleted it… and no, I didn’t reboot. By the way, sorry — I know this isn’t the precise topic of your post, but I figured, while I was waiting on the Microsoft community to help me out, I’d see if you had any insights into the problem. Thanks for posting about the need for the full installation in the first place — I was going crazy trying to figure out why my UDFs weren’t working…

    littleslips

    14 Jul 11 at 7:46 am

  9. Katherine,

    Don’t forget you need to create a module for you macros?

    Then, you can work in the module:

    maclochlainn

    14 Jul 11 at 11:21 am

  10. That tripped me up too for a while — thanks! Everything’s working properly now…

    littleslips

    14 Jul 11 at 1:20 pm

  11. Many thanks for this! Programming in VBA is so intuitive (/sarcasm) and I’d also missed the “Insert Module” tip!

    John Endahl

    16 Jan 12 at 2:10 pm

  12. Insert Module!! Thank you thank you thank you! I’m working from “Excel VBA for Dummies” and not seeing this in the book–is it a step not required in the PC version? Anyway, I Googled all over for this #NAME? result and found this page and here, near the bottom, found the golden ticket: Insert Module!!!! (Whatever the heck that means.) Now my user-defined functions actually work!

    Dave

    7 Apr 12 at 7:10 am

  13. Insert Module was what did it for me. I was dreading going back to IT to have them re-install Excel.

    JohnB

    15 Jan 13 at 6:26 am

  14. Does anyone know how to implement the CDec function that is missing using the other functions. I got a spreadsheet which contains this function and it would not run on my Mac.

    Boaz

    26 Mar 13 at 6:15 am

  15. On Excel 2011 on Mac OSX I am mightily struggling on couple of things, if anyone can please help give me some directions.

    Issue 1: How do I get the Enumeration to work for a Collection Class in the Excel VBA Macro? I tried editing the class file in TextEdit to insert the Attribute statement and re-importing in Excel VBA. But it failed to bring in the additions and continues to fail the same way as before.

    Issue 2: I am trying to test a string if it contains both numeric and alpha characters. I thought of using regex but now realize (based on some Googling on the topic) that Mac version does not (?) support it. Is there another way in Mac to perform such logic?

    Really appreciate any idea.

    TMaj

    17 Apr 13 at 4:19 pm

  16. OK, I figured out the workaround (custom function) for Issue #2.

    But please if any good soul out there could help me out on issue #1.

    Thank you

    TMaj

    23 Apr 13 at 2:18 pm

  17. I get a different strange behavior.

    From a workbook created on a PC, and opened on a MAC, I get #Name. But if I hit F2 to Edit, and then Enter, to save the cell, it calculates right. I can then save the file on the Mac and reopen and those touched cells auto calculate.

    Marty Zigman

    17 Apr 14 at 11:59 am

  18. Marty, You should report that to Microsoft and provide a test case.

    maclochlainn

    18 Apr 14 at 3:06 am

Leave a Reply