Sabrina Ayon
‘Sub or Function not defined’ is a compilation mistake in Excel VBA. When anything specified by name cannot be found, VBA shows this warning. Before being run, VBA is converted or compiled into machine language. Before any methods are run, compilation errors stop the process. In this article, we will demonstrate how to fix the compiler error ‘Sub or Function not defined’ in Excel VBA.
Download Practice Workbook
You can download the workbook and practice with them.
6 Reasons with Solutions If Sub or Function Is Not Defined in Excel VBA
It is a good practice to compile VBA code after it has been written in order to check for potential mistakes that can arise. A compilation error alert will show if there are compile errors. A possible example of one of these problems is the Sub or Function not defined error. This error may arise for a number of reasons.
Reason 1: Typing Error
The most frequent reason for ‘Sub or Function not defined’ is typos. We have a tremendous advantage in our quest of hiding and seeking if Excel displays in yellow phrase or method it can’t discover.
- Suppose, we write down the following code in Visual Basic Editor.
VBA Code:
Sub Example1()
Worksheet("Sheet1").Select
End Sub- After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.
- But, get the Sub or Function not defined, compiler error.
There are some required keywords in the Excel VBA code. Worksheets are an essential keyword in excel. The Worksheets package contains the ‘Summary’ sheet item. All worksheet items in a spreadsheet are included in the sheets package. The collections in Excel VBA are numerous. As we ignore the s on Worksheets that’s why we get that error message.
Solution: Always Check Typos Before Running VBA Macros
While creating names, always use a minimum of one uppercase and adhere to Microsoft’s addressing policy. Have to use lowercase when typing a name. We recognize a typo once we exit the sentence and the message remains in subscript.
- Now, write the corrected code again.
VBA Code:
Sub Example1()
Worksheets("Sheet1").Select
End Sub- Further, press the F5 key or click on the Run Sub button to run the code.
- And, you won’t get any errors while running the code.
Reason 2: Functions of Worksheets
Although Visual Basic Editor may be the worksheets backstage, not all worksheet accessories were put there. These props are really missing functions from VBA. CountA on the worksheet produces the error ‘Sub or Function not defined.’
- Assume that we are using the VBA code below.
VBA Code:
Sub Example2()
intCount = CountA("B:B")
End Sub- Finally, press the F5 key to run the code or click on the Run Sub button.
And, get the error message.
It is because CountA is a Worksheet Function in Excel.
Solution: Use WorksheetFunction. Before All Functions of Worksheets
While using any worksheet functions we must define those functions as worksheet functions. The production assistant that enables calling spreadsheet functions from VBA is the WorksheetFunction object.
- Write the updated code once more.
VBA Code:
Sub Example2()
int count = WorksheetFunction.CountA("B:B")
End Sub- Additionally, to run the code, hit the F5 key or click the Run Sub button.
- Executing the code won’t result in any errors.
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’ also appears. Using public vs. private processes is the cause of this issue. Standard modules in the Modules folder visible in Project Explorer by default have public Sub and Functions. Any process in the project has the ability to call standard procedures. By using the Private keyword, we can make the operation private.
VBA Code:
Private Sub Example3()And the compiler error will appear.
Solution: Delete Private Keywords from Procedures
In worksheet modules, Subs and Functions are private. They can only be invoked from the worksheet or another operation in that module such as clicking a button. It holds true for user forms as well. 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.
Reason 4: Declaring Procedure That Is Absent from Specified Package
The called process is rarely really absent. You may be lacking a library after you have checked for errors and are certain you have programmed the called operation. The next place to search is Tools, References.
We can get the Sub or Function not defined compiler error if the declaring procedure is absent from the specified package.
Solution: Assign VBA Project a Suitable Name
To solve this problem follow the instructions down.
- 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 before the final library with a check mark. We may simply verify the missing library by scrolling below to the list of packages.
- Then, click on the OK button.
Missing libraries are uncommon and often indicate a change. Maybe you updated to the latest Excel version. You recently bought the latest device. Someone with an earlier version of Excel sent you a workbook. Alternately, you developed your first macro that uses Solver Add-In.
Reason 5: Misspelled Sub or Function
A spelling error is by far the most typical cause of this problem. In the example above, we developed a method to retrieve 20% of the 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- And, run the code to see the result by pressing the F5 key.
However, when we execute the code, we encounter a build error because the function We are calling has improper 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 to your users. 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- Finally, run the code by pressing F5 on your keyboard and you see the result in your worksheet.
This error is simple to make, particularly in complex VBA applications with several methods and modules. To solve this problem.
- Simply, go to the Visual Basic Editor menu bar.
- Subsequently, click on Debug.
- Finally, select Compile VBAProject.
Reason 6: Declaring Routine Not Found in Selected Dynamic-Link Library (DLL)
We have references to a dynamically linked library(DLL), it’s possible that the sub you’re attempting to utilize. So the compiler won’t locate the sub or function you’re attempting to utilize if you don’t declare a dynamically linked library(DLL) or declare the wrong one. A dynamically linked library(DLL) has been built and is intended to supply information or functionality to viable programs such as the Visual Basic Application project we’re working on. The VBA codes load a dynamic library when it is required. Developers utilize DLLs to save time by leveraging developed and tested pieces of code.
Solution: Declare Keywords in Simplest Form after Finding Library Part
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”Check out to learn more about using VBA to call DLL subs and functions: Access DLLs in Excel.
Conclusion
The above solutions will assist you in fixing the Compiler Error: Sub or Function not defined in Excel VBA. I hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can glance at our other articles in the ExcelDemy.com blog!
