‘Sub or Function not defined’ is a compilation error in Excel VBA, that is displayed whenever anything specified by name cannot be found. Before being run, VBA code is converted or compiled into machine language, so compilation errors will stop the code from running. In this article, we will discuss the various reasons for this error, and how to fix them.
Reason 1 – Typing Error
The most common cause for ‘Sub or Function not defined’ errors is typos. Excel makes it simple to identify these errors by highlighting in yellow any phrases or methods it can’t find while compiling the code.
- Enter the following VBA code in the Visual Basic Editor:
Sub Example1()
Worksheet("Sheet1").Select
End Sub- Run the code by clicking on the Run Sub button or pressing the keyboard shortcut F5.
We receive a Sub or Function not defined compiler error.
There are some required keywords in the Excel VBA code, Worksheets being one of them. In our code, we left the s off Worksheets, causing the error message.
Solution: Always Check Typos Before Running VBA Macros
When creating names, always use a minimum of one uppercase letter, and adhere to Microsoft’s addressing policy.
- Enter the corrected code:
Sub Example1()
Worksheets("Sheet1").Select
End Sub- Press the F5 key or click on the Run Sub button to run the code.
There is no longer an error running the code.
Read More: VBA Object Variable or with Block Variable Not Set in Excel
Reason 2 – Functions of Worksheets
Not all worksheet functions are included in VBA. Using one of these missing Worksheet Functions, the CountA function for example, in VBA code will produce the error ‘Sub or Function not defined.’
- Enter the VBA code below:
VBA Code:
Sub Example2()
intCount = CountA("B:B")
End Sub- Press the F5 key or click on the Run Sub button to run the code.
We get the error message because CountA is a Worksheet Function.
Solution: Use WorksheetFunction Before All Functions of Worksheets
Worksheet Functions must be defined as Worksheet Functions in VBA code. The production assistant that enables calling spreadsheet functions from VBA is the WorksheetFunction object.
- Enter this updated code:
VBA Code:
Sub Example2()
int count = WorksheetFunction.CountA("B:B")
End Sub- Hit the F5 key or click the Run Sub button.
Executing the code no longer results in any errors.
Read More: [Fixed!] Subscript Out of Range Error in Excel VBA
Reason 3 – Procedures Are Not Available
When the procedure is not accessible to the calling procedure in the same workbook, the error ‘Sub or Function not defined’ appears. Using private processes is the cause of this issue. The Standard modules in the Modules folder that are visible in Project Explorer by default have public Sub and Functions. Any process in the project has the ability to call these standard procedures. By using the Private keyword, we can make the operation private, but then it is no longer a publicly accessible procedure.
VBA Code:
Private Sub Example3()The compiler error will appear.
Solution: Delete Private Keywords from Procedures
In worksheet modules, Subs and Functions are private, meaning they can only be invoked from the worksheet or another operation in that module such as clicking a button. The same holds true for user forms. By removing the Private keyword from a method in a standard module, you may fix ‘Sub or Function not defined’. Consider worksheet modules as private property and standard modules as public parks.
Read More: Reasons And Solutions for Excel Object Required Error in VBA
Reason 4 – Declaring Procedure That Is Absent from Specified Package
We can get the Sub or Function not defined compiler error if the declaring procedure is absent from the specified package. You may be missing a library. The place to check is Tools >> References.
Solution: Assign VBA Project a Suitable Name
STEPS:
- Select References from the Tools menu in the Visual Basic Editor.
This will open the References window. If VBA has found a missing library, its name will start with MISSING and a check mark.
- Verify that the library is missing by scrolling through the list of packages.
- Click on the OK button.
Missing libraries are uncommon and often indicate something external has changed. Maybe you updated to the latest Excel version or switched to a new device. Perhaps someone with an earlier version of Excel sent you a workbook. Or maybe you developed your first macro using Solver Add-In.
Read More: [Fixed!] Excel Application-Defined or Object-Defined Error in VBA
Reason 5 – Misspelled Sub or Function
A spelling error is by far the most typical cause of this problem. In the example below, we developed a method to retrieve 20% of a percentage. We are attempting to call that function in the second process to obtain the error for a specific number in the range A1.
VBA Code:
Function GetError(bb As Double) As Double
GetError = bb * 0.2
End Function
Sub Get_Compiler_Error()
Dim bb As Double
Dim bb1 As Double
bb = Range("A1")
bb1 = GetCompilerError(bb)
End Sub- Run the code by pressing the F5 key to see the result.
We encounter a build error because the function we are calling has incorrect spelling.
Solution: Check Name of Sub or Function Before Calling
The simplest method to avoid these run-time issues is to compile the code before making it available publicly. The code will then be marked with any build faults so that you may repair them.
VBA Code:
Function GetError(bb As Double) As Double
GetError = bb * 0.2
End Function
Sub Get_Compiler_Error()
Dim bb As Double
Dim bb1 As Double
bb = Range("A1")
bb1 = GetError(bb)
End Sub- Run the code by pressing F5 on your keyboard.
The error is resolved.
To solve this problem:
- Go to the Visual Basic Editor menu bar.
- Click on Debug.
- Select Compile VBAProject.
Read More: How to Fix Compile Error in Hidden Module in Excel
Reason 6 – Declaring Routine Not Found in Selected Dynamic-Link Library (DLL)
If you have references to a dynamically linked library (DLL), it’s possible that the sub you’re attempting to utilize isn’t there. The compiler can’t locate the sub or function you’re attempting to utilize if you don’t declare a DLL, or declare the wrong one.
Solution: Declare Keywords in Simplest Form after Finding Library
To declare a sub or function in your code, use the Declare keyword in its simplest form after doing some research to find out which library it is a part of. The code is:
VBA Code:
Declare Sub library “libraryName”Read More: [Fixed!] Invalid Forward Reference in VBA
Download Practice Workbook
Related Articles:
- [Fixed!] Unable to Set the Visible Property of the Worksheet Class
- Because of Your Security Settings Macros Have Been Disabled
- [Fixed!] Method Open of Object Workbooks Failed
- [Solved]: User Defined Type Not Defined in Excel VBA
- [Solved!] Excel VBA “Argument Not Optional” Error
