Sub or function not defined vba excel ошибка

Обновлено: 04.07.2024

Option Explicit - начинающие программировать в Visual Basic могут увидеть данную строку в чужом коде, либо случайно в своем. Хотя кто-то может быть уже знает, что это и зачем и использует данное объявление намеренно. Я же постараюсь максимально подробно описать смысл этой строки и её полезность для кода в первую очередь для тех, кто еще не знает для чего она.

Строка данная записывается в самом начале модуля, самой первой строкой. Перед этой строкой ничего более не может быть записано, кроме, разве что других подобных строк(есть еще другие :-))

так же редактор VBA выделит ту переменную, которая не объявлена. Первое время это может раздражать. Да и вообще: зачем это? Вы и без всех этих объявлений неплохо жили. А вот зачем

во-первых: объявление переменных считается хорошим тоном при программировании
во-вторых: правильное присвоение типов недурно экономит память
ну и в-третьих(я бы даже сказал в главных): это помогает избежать неявных ошибок кода при несовпадении типов данных

А теперь перейдем к сути и попробуем разобраться в чем же польза от использования Option Explicit . Ниже приведен простой код:

Выполните данный код без строки Option Explicit . Какое значение выдаст MsgBox? Ничего. Что за странность? Ведь явно видно, что переменной присвоено значение текста. Ничего больше не происходит. Но переменная все равно пуста. Мистика. А теперь запишите первой строкой в модуле Option Explicit :

Еще один классический пример, когда Option Explicit спасет от лишних мозговых штурмов. Имеем простую функцию пользователя(UDF), которая берет указанную дату и возвращает её в заранее заданном формате в текстовом виде:

Function GetDateAsText(Optional ByVal Дата As Date) If Дата = 0 Then Дата = Date End If GetDataAsText = Format(Дата, "DD MMMM YYYY") End Function

Хоть функция и короткая, но даже в ней не сразу порой бросается в глаза опечатка(представим, если функция в реальности строк на 200). В итоге чаще всего автор функции не понимает, почему при записи её на листе она возвращает не дату вида " 21 мая 2016 ", а 0 и начинает пошагово выполнять функцию, искать ошибки в логике кода и т.д. Но если поставить в начало модуля Option Explicit , то при первом же выполнении этой функции VBA подсветит нам " GetDataAsText = ", указывая тем самым, что GetDataAsText в чем-то отличается от заданного имени функции - GetDateAsText . Банальная опечатка: GetDat a AsText - GetDat e AsText .

Так же эта строка поможет избежать неявных ошибок и в других ситуациях. В частности, при обращении к другим приложениями(Word, Outlook и т.д.). Например, в Excel применяются именованные константы для многих задач. Одна из распространенных - поиск последней ячейки в столбце: llast = Cells(Rows.Count, 1).End(xlUp).Row
здесь xlUp является именованной константой, значение которой равно числу: -4162 . В других приложениях такой же подход. Это избавляет от необходимости помнить на память все значения констант и обращаться к ним при помощи intellisense. Но действуют эти константы исключительно внутри своего приложения(можете обратить внимание, у Excel константы начинаются с xl , а у Word - с wd ). И т.к. объявлены эти константы в других приложениях - Excel про них не знает(как и другие приложения не знают про константы Excel). Для примера возьмем простой и рабочий код замены в Word:

Dim wdDoc As Object Set wdDoc = objWordApp.ActiveDocument With wdDoc.Range.Find .Text = "привет" .Replacement.Text = "привет" .wrap = wdFindContinue .Execute Replace:=wdReplaceAll End With

Где wdFindContinue для Word-а равно 1, а wdReplaceAll = 2. Но это происходит только при выполнении изнутри самого Word-а(или при раннем связывании через Tools -References. Подробнее про это можно почитать в статье: Как из Excel обратиться к другому приложению).

Если же скопировать и выполнять данный код из Excel, то работать он будет не так как задумали. Дело в том, что Вы считаете, что Excel работает с обозначенными константами( wdFindContinue , wdReplaceAll ) наравне с Word-ом. Но Excel на самом деле про них ничего не знает. И если директива Option Explicit будет отключена, то Excel просто назначает им значение по умолчанию - Empty. Которое преобразуется в 0. А это совсем иной поиск получается, т.к. должны быть значения 1 и 2. А если бы Option Explicit была включена, то Excel выделил бы их и указал, что они не объявлены. И тогда можно было бы сделать либо так:

Dim wdDoc As Object Const wdFindContinue As Long = 1 Const wdReplaceAll As Long = 2 Set wdDoc = objWordApp.ActiveDocument With wdDoc.Range.Find .Text = "привет" .Replacement.Text = "привет" .wrap = wdFindContinue .Execute Replace:=wdReplaceAll End With

либо так(что удобнее, на мой взгляд):

Dim wdDoc As Object Set wdDoc = objWordApp.ActiveDocument With wdDoc.Range.Find .Text = "привет" .Replacement.Text = "привет" .wrap = 1 .Execute Replace:=2 End With

Так что думаю, не стоит недооценивать значимость строки Option Explicit при написании кодов. В довершение хотелось бы Вас обрадовать, что вписывание данной строки в начало каждого модуля можно сделать автоматическим: поставить в опциях редактора галочку: Tools-Options-вкладка Editor-Require Variable Declaration. Теперь во всех новых созданных модулях строка Option Explicit будет создаваться самим редактором VBA автоматически. К сожалению, в уже имеющихся модулях Вам придется проставить данную строку самим вручную. Но это того стоит, поверьте.

Необходимо определить процедуру Sub, Function или Property, чтобы ее можно было вызвать. Эта ошибка имеет следующие причины и решения:

Вы неправильно указали название процедуры.

Проверьте и исправьте написание.

Вы попробовали вызвать процедуру с другого проекта, не добавив на него явным образом ссылку в диалоговом окне "Ссылки" (References).

Добавление ссылки

Отобразите диалоговое окно Ссылки.

Найдите название проекта, содержащего процедуру, которую необходимо вызвать. Если название проекта не появляется в диалоговом окне Ссылки, нажмите кнопку Обзор, чтобы найти название.

Установите флажок слева от названия проекта.

Указанная процедура невидима для вызывающей процедуры. Процедуры, которые объявлены как Private в одном модуле, невозможно вызвать из процедур вне модуля. Если выполняется Option Private Module, процедуры в модуле будут недоступны для других проектов. Выполните поиск процедуры.

Вы объявили процедуру библиотеки динамических связей (DLL) Windows или процедуру "код-ресурс" Macintosh, но процедуры нет в выбранной библиотеке или ресурсе кода.

Проверьте порядковый номер (если таковой использовался) или название процедуры. Убедитесь, что ваша версия процедуры DLL или "код-ресурс" Macintosh верная. Процедура может существовать только в последних версиях процедуры DLL или "код-ресурс" Macintosh. Если на вашем пути каталог с недействительными версиями расположен перед каталогом с правильными версиями, будет открыта неправильная процедура DLL или "код-ресурс" Macintosh. Вы указали правильное название процедуры DLL или "код-ресурс" Macintosh, но это не та версия, которая содержит указанную функцию.

Для получения дополнительной информации выберите необходимый элемент и нажмите клавишу F1 (для Windows) или HELP (для Macintosh).

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

What’s worse than getting a runtime error in Excel VBA? A compile error. That’s because the actual error is not always highlighted, rather the opening Sub or Function statement. “Sub or Function not Defined" indicates a compile error. VBA displays this message when it cannot find what is referenced by name. This article gives several examples of this compile error and how to correct them.

VBA is compiled (translated) into machine language before it is executed. Compile errors halt the compile process before procedures are executed.

Best practice: Frequently check for compile errors from VB Editor. From the Debug menu, choose Compile VBAProject. No news is good news when nothing seems to happen.

Issue 1: Typos

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.

Best practice: Follow Microsoft’s naming convention and always include at least one capital letter whenever you create a name (exception: counter variables like n). Always type the name in lower case. When you leave the statement, and the name stays in all lower case, you have found a typo.

Contrary to its message, “Sub or Function not Defined" is not limited to procedures. The statement below causes this message. Can you find the typo?

Worksheets is the required keyword. The “Summary" worksheet object is a member of the Worksheets collection. The Worksheets collection contains all the worksheet objects of a workbook. Excel VBA has several collections.

Tip: All VBA collections end with “s": Workbooks, Sheets, Cells, Charts, etc.

Issue 2: Worksheet Functions

VB Editor may be the backstage to the worksheets in front, but not all worksheet props have been brought backstage. These “props" are functions that don’t exist in VBA. Worksheet functions like CountA cause “Sub or Function not Defined":

The WorksheetFunction object is the “stage hand" that lets you call worksheet functions from VBA, like this:

Issue 3: Missing Procedure

Less frequently, the called procedure is truly missing. After you check for typos, and you’re sure you coded the called procedure, perhaps you are missing a library. Tools, References is the next place to look.

From VB Editor Tools menu, choose References. The References dialog box opens. If VBA has identified a missing library, the last library with a checkmark will start with MISSING, followed by its name. Most of the time, you can simply scroll down the alphabetical list of libraries and check the missing library, then choose OK.

Fortunately, a missing library happens infrequently, usually related to a recent change. Perhaps you upgraded to a newer version of Excel. You purchased a new computer. You received a workbook from someone with an older version of Excel. Or you created your first macro that calls Solver Add-In.

The Solver project is not added to VBA when you enable the Solver Add-In, as shown below. At Solver project is near the top of the list of references, so you don’t have to scroll down to find it.

Your own macro workbooks can behave like Solver. Every Excel workbook has a built-in VBAProject. See MyFunctions in the screenshot above? MyFunctions is simply VBAProject renamed in a macro workbook. The workbook is open, so the Subs in MyFunctions run from the Developer tab. Even so, “Sub or Function not Defined" occurs when MyFunctions is not checked, and a procedure is called from a different macro. Simply check its project as an available reference.

Best practice: Assign your VBA projects a meaningful name. From Project Explorer, right click the macro workbook. Choose VBAProperties, then type a Project Name with no spaces.

Issue 4: Unavailable Procedures

“Sub or Function not Defined" also occurs when the procedure is not available to the calling procedure in the same workbook. This error is related to Public vs. Private procedures. By default, Sub and Functions in standard modules of the Modules folder (seen in Project Explorer) are public. Standard procedures can be called by any procedure in the project. You can make a procedure private by adding the Private keyword, like this:

Private Sub Initialize()

Tip: All the macros shown in the Macros dialog box of the Developer tab are Public. The list excludes public functions.

Subs and Functions in worksheet modules are private. They can only be called from the worksheet (like clicking a button) or another procedure in that module. The same is true for user forms.

You can remedy “Sub or Function not Defined" by deleting the Private keyword from a procedure in a standard module. Sorry, you cannot remedy calling a procedure in a worksheet module from another module. Think of standard modules like public parks, and worksheet modules like private property. No trespassing allowed!

Issue 5: Declaring a Routine That Doesn’t Exist in the Specified DLL

The sub you’re trying to use could be a part of a DLL that needs to be referenced. So not declaring a DLL or declaring the wrong one will cause the compiler to not find the sub or function that you are trying to use.

A DLL is a dynamically linked library of a body of code that is compiled and is meant to provide some functionality or data to an executable application (like the VBA project we’re working with). A dynamic library is loaded by the VBA project when it needs it. DLLs are used in order to save developers time by utilizing built and tested bodies of code.

You will need to do some research to determine the library that your sub or function belongs to, then declare it in your code using the Declare keyword in its simplest form:

Declare Sub sub_name Lib “library_name"

Issue 6: Forgetting to Write It

Finally, it’s possible that it just hasn’t been written yet!

If you realize that the sub that has been highlighted for you by the VBA compiler doesn’t exist, then the solution is to create it. To know whether it exists or not, just search for it on the project level using the Find tool in the VBA IDE.

Selecting the ‘Current Project’ scope will allow you to search for the sub in the entire workbook. You can also do that for the other workbooks where the sub might reside.

Wrap Up

“Sub or Function not Defined" is a compile error that occurs when VBA cannot find a procedure or other reference by name. A typo is the most common cause of this message.

Читайте также: