The workbook is open, so the Subs in MyFunctions run from the Developer tab. See MyFunctions in the screenshot above? MyFunctions is simply VBAProject renamed in a macro workbook. Every Excel workbook has a built-in VBAProject. Your own macro workbooks can behave like Solver. At Solver project is near the top of the list of references, so you don’t have to scroll down to find it. The Solver project is not added to VBA when you enable the Solver Add-In, as shown below. Or you created your first macro that calls Solver Add-In. You received a workbook from someone with an older version of Excel. Perhaps you upgraded to a newer version of Excel. Most of the time, you can simply scroll down the alphabetical list of libraries and check the missing library, then choose OK.įortunately, a missing library happens infrequently, usually related to a recent change. If VBA has identified a missing library, the last library with a checkmark will start with MISSING, followed by its name. Tools, References is the next place to look.įrom VB Editor Tools menu, choose References. After you check for typos, and you’re sure you coded the called procedure, perhaps you are missing a library. Less frequently, the called procedure is truly missing. IntCount = WorksheetFunction.CountA('A:A') Issue 3: Missing Procedure The WorksheetFunction object is the “stage hand” that lets you call worksheet functions from VBA, like this: Worksheet functions like CountA cause “Sub or Function not Defined”: These “props” are functions that don’t exist in VBA. VB Editor may be the backstage to the worksheets in front, but not all worksheet props have been brought backstage. Tip: All VBA collections end with “s”: Workbooks, Sheets, Cells, Charts, etc. The Worksheets collection contains all the worksheet objects of a workbook. The “Summary” worksheet object is a member of the Worksheets collection. When you leave the statement, and the name stays in all lower case, you have found a typo.Ĭontrary to its message, “Sub or Function not Defined” is not limited to procedures. Typos are the most common cause of “Sub or Function not Defined.” If Excel highlights (in yellow or gray) the keyword or procedure it can’t find, you have a great head start on your game of Hide and Seek.īest practice: Follow Microsoft’s naming convention and always include at least one capital letter whenever you create a name (exception: counter variables like n). Issue 5: Declaring a Routine That Doesn’t Exist in the Specified DLL.

Excel mac developer tab has disappeared