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).
It is easy to use and compatible with both Windows as well as Mac operating systems. This is a unique tool to repair multiple Excel files at one repair cycle and recovers the entire data in a preferred location. It allows you to easily restore all corrupt Excel files including the charts, worksheet properties cell comments, and other important data. This is the best tool that just by scanning once detects and fixes various errors in Excel files and repairs and recover damaged, corrupted, or even inaccessible Excel files as well. It is the best tool to repair all sorts of issues, corruption, errors in Excel workbooks. It is recommended to make use of the MS Excel Repair Tool. But if not then try the automatic repair utility to resolve the VBA error 400 in Excel.
Hope the given manual solutions work for you to resolve the VBA error 400 running an Excel Macros.
Also, check for the macros corruption before trying to run them.ĭon’t Miss: Top Ways to Recover Macros from Corrupted Excel File Effectively Hope turning on the trusted access to VBA fixes the Excel 2016 macro 400 error.Īpart from that also check the codes you are entering as some mistakes in codes may cause Excel error 400.