How to fix sub or function not defined error in Excel VBA
‘Sub or Function not defined’ is an error that is found by the compiler while running the code in Excel VBA. It occurs when anything specified by name cannot be found. Before being run, VBA is converted or compiled into machine language. If the compiler finds any errors it stops the execution process.
In this article, we will discuss the most common reasons for this error and demonstrate how to fix the compiler error ‘Sub or Function not defined’ in Excel VBA.
Reason 1: Typing Mistake in the name of function
The most common reason for ‘Sub or Function not defined’ is the typing mistake. Excel shows the error message at once when it couldn’t find the match of the function’s name you used in the code. Let’s assume that we wrote the following line in VBA Editor. If we try to run the code we’ll get the ‘Sub or Function not defined’ error. In this case VBA will highlight the problematic line
Step 1 – Check for Code highlighted by VBA Compiler
- When you run the above code VBA will automatically halt with the error message and will help you by highlighting the very first keyword which caused the problem as shown above.
Step 2 – Use correct keyword to avoid error
- As the keyword Worksheet was highlighted, we know that the problem was in this line and in this keyword. The correct keyword is Worksheets. So, using the proper keyword will remove the error as shown above.
- Now run the code again by pressing the play button and it will run without any error this time.
Reason 2: Using functions of Worksheets without proper keyword
Although Visual Basic Editor is built for Microsoft Applications to work backstage, not all worksheet accessories were put there. We can use all functions available in the Worksheets in VBA as well. However, we need proper keywords for that and if we don’t know what was that or we miss those keywords then we’ll have the ‘Sub or Function not defined’ error, as shown above.
Step 1 – Check for Code highlighted by VBA Compiler
- When you run the above code VBA will automatically halt with the error message and will help you by highlighting the very first keyword which caused the problem as shown above.
Step 2 – Use correct keyword to avoid error
- As the keyword SUM was highlighted, we know that the problem was in this line and in this keyword. SUM is a function available in the worksheets to calculate the Sum of a range of cells or numbers. However, in VBA these functions can be used with the WorksheetFunction keyword only. So, using the proper keyword will remove the error as shown above.
- Now run the code again by pressing the play button and it will run without any error this time.
Reason 3: Calling a user defined function with wrong name
The third major reason for this error is calling a user defined function with a wrong name or misspelled name. In this case we’ll have the ‘Sub or Function not defined’ error, as shown above.
Step 1 – Check for Code highlighted by VBA Compiler
- When you run the above code VBA will automatically halt with the error message and will help you by highlighting the very first keyword which caused the problem as shown above.
Step 2 – Use correct function name to avoid error
- As the function name Sum_Ranges was highlighted, we know that the problem was in this line and in this keyword. Sum_Range was the actual name of the function and therefore, using the proper function name will remove the error as shown above.
- Now run the code again by pressing the play button and it will run without any error this time.