2

This might sound like a simple question but I'm creating a macro in VBA that uses regular expressions. It turns out VBA doesn't inherently support regex and I need to add a reference to 'Microsoft VBScript Regular Expressions 5.5' to gain access to regex functions in Excel.

All my searching online tells me you need to go to Tools -> References menu to add/remove references that can be used in VBA.

The Tools -> References menu in Excel as obtained from Google

For the life of me I can't see where the Tools -> References menu is in my Excel. I've enabled the Developers tab and it's not there.

This is what I see

If it helps, I'm using Excel on Office 365 (which Wikipedia tells me is Office 2016) on a Mac. Please help me find how I can access the References menu in Excel (whether or not it can be accessed via Tools -> Menu or some other way). Thanks.

desktop
  • 25
  • 1
  • 1
  • 5
  • First press ALT-F11 to open the VBA editor. Tools references is in there. I don't know how different this is on a Mac though. You might also consider something called 'late binding'. This does not require Tools References and is a bit less likely to fail on another users PC. http://stackoverflow.com/questions/4556910/how-do-i-get-regex-support-in-excel-via-a-function-or-custom-function – Nick.Mc Mar 13 '17 at 03:33
  • Found it! After opening the VBE, the References link is actually located on the bottom-left side of the VBE screen, and not in Tools -> References as in previous versions of Excel. – desktop Mar 13 '17 at 04:03
  • Edit: I did more research and it turns out Mac versions of Excel don't have access to the VBScript.RegExp reference to use regex in VBA... hmm [Link](http://stackoverflow.com/questions/13995007/how-to-develop-an-excel-macro-that-contains-regular-expressions-so-it-will-work) – desktop Mar 13 '17 at 04:12
  • Thanks very much for returning with your observations :) – Nick.Mc Mar 13 '17 at 10:14

2 Answers2

1

You need to open the Visual Basic editor. On the ribbon in Excel, select Developer, then Visual Basic. This will open the VBA interface in a separate window. On this new window, you should see the Tools -> References option.

Tony
  • 2,658
  • 2
  • 31
  • 46
0

You need to open the Visual Basic (for applications) Editor, or VBE. On the Developers tab, look for "Visual Basic". It should be the left-most item.

Or, you can press Alt-F11.

The VBE is where you'll find the Tools menu, and the References submenu.

Rich Holton
  • 662
  • 5
  • 12
  • Found it! After pressing Alt-F11 and opening the VBE, the References window can be opened by clicking on the link in the bottom-left corner, and not by going to Tools -> References as in previous versions of Excel. [Image of references menu in Excel 365](http://imgur.com/a/IfRZE) – desktop Mar 13 '17 at 03:55