VBA-Урок 9. Процедуры и функции
На данный момент, все процедуры, мы создавали, имеют тип Public , что означает, что они доступны из любого модуля.
Чтобы сделать процедуру доступной только в определенном модуле, используется ключевое слово Private:
Запуск процедуры с середины другой процедуры
Чтобы выполнить процедуру с середины другой процедуры, просто введите ее название.
Здесь есть очень простой пример:
Аргументы
Аргументы делают возможным использование значений из процедуры в под-процедуры (запомните, что по умолчанию, переменные являются доступны только по той процедуры, в которой они были объявлены).
К процедуре «warning» был добавлен аргумент, в данном случае это переменная «var_text» с типом «String» (строка):
Эта процедура требует аргумент, поэтому мы должны поставить значение после «warning», чтобы выполнить ее:
Когда мы хотим прописать несколько аргументов, тогда они должны быть отделены запятыми.
Необязательные аргументы
По умолчанию, если процедура имеет аргументы, то они должны быть обязательно проставлены, и если они не проставлены, тогда процедура не выполнится.
Необязательный аргумент может быть добавлен после обязательного, с помощью ключевого слова Optional . Например:
Теперь эта процедура может быть выполнена с или без опционального аргумента, как здесь:
Аргументы должны быть введены в правильном порядке.
Чтобы протестировать, присутствует ли опциональный аргумент в процедуре, мы используем функцию IsMissing . Эта функция совместима только с некоторыми типами функций (типа Variant) и это является решающим, так как тип необязательно аргументов не был указан в объявлении (необъявленный тип = Variant).
Здесь есть пример, который использует два фрагмента кода, которые рассматривались выше:
См. рисунок ниже (пример 1):
ByRef — ByVal
По умолчанию, аргументы имеют тип ByRef , что означает: если переменная передается как аргумент, ссылка на нее будет также передаваться. Иными словами, если переменная была изменена другой под-процедурой, то она также будет изменена во внешней процедуре, которая вызывает эту под-процедуру.
Чтобы стало понятнее, ниже есть пример того, что произойдет, если макрос будет запущен на выполнение:
Второй метод заключается в использовании ByVal .
В отличие от ByRef , который передает ссылки (ярлык), ByVal передает значение, которое означает, что значение передано как аргумент не было изменено.
Ниже вы можете увидеть как предыдущий код и ByVal работают:
Что вам нужно запомнить: используйте ByVal когда переменная не должна быть изменена .
Функции
Основным отличием между процедурой и функцией является то, что функция возвращает значение.
Вот простой пример:
Функция может быть использована на рабочем листе, подобно любой другой функции в Excel.
Например, чтобы получить квадрат значения, которое введенное в ячейку A1:
VBA Private vs Public Procedures (Subs & Functions)
Procedures (Sub and Functions) can be declared either Private or Public in VBA. If they are Public, it means that you would be able to see them from within the Excel Macro Window and they can be called from anywhere within your VBA Project. If they are Private, they cannot be seen in the Excel Macro Window and are only available to be used within the Module in which they are declared (using normal methods, see the bottom of this article for ways to access private procedures from other modules).
Public functions can be called like built-in Excel functions in the Excel worksheet.
Note: Variables and Constants can also be Public or Private.
Excel Macro Window
By default, Excel Macros (most VBA Procedures) are visible to workbook users in the Macro Window:
These are considered Public procedures. You can explicitly define procedures as public by adding “Public” before the Sub statement:
If you don’t define the procedure as Public, it will be assumed Public.
To declare a procedure as Private, simply add “Private” before the procedure sub statement:
The second procedure would not be visible in the Macro window to Excel users, but can still by used in your VBA code.
Procedures with Arguments
Sub procedures can have arguments. Arguments are inputs to the sub procedure:
If a sub procedure has arguments, it will never appear in the Macro Window regardless of if its declared Public because there is no way to declare the arguments.
Functions also will never appear in the Macro Window, regardless of if they are declared Public.
Public functions in Excel are able to be used directly in a worksheet as a ‘User Defined Function’ (UDF). This is basically a custom formula that can be called directly in a worksheet. They can be found in the ‘User Defined’ category in the ‘Insert Function window or can be typed directly into a cell.
Using Procedures between Modules in your VBA Project
Public procedures can be called from any module or form within your VBA Project.
Attempting to call a private procedure from a different module will result in an error (Note: see bottom of this article for a work around).
Note: Public procedures and variables in class modules behave slightly differently and are outside the scope of this article.
Different modules, can store procedures with the same name, provided they are both private.
If two or more procedures have the same name and are declared public you will get an ‘Ambiguous Name detected’ compile error when running code.
Private Modules
By default, modules are public.
To make a module private, you put the following keyword at the top of the module.
If you declare a module as private, then any procedures in the module will not be visible to Excel users. Function procedures will not appear in the Insert Function window but can still be used in the Excel sheet as long as the user knows the name of the function!
Sub procedures will not appear in the Macro Window but will still be available to be used within the VBA project.
Accessing a Private Procedure from a Different Module
As mentioned above, Private Procedures are inaccessible in other code modules by “normal” methods. However, you can access private procedures by using the Application.Run command available in VBA.
Consider the following 3 modules.
Module 2 is a Private Module with a Public Sub Procedure, whereas Module3 is Public module with a Private Sub Procedure.
In Module1, we can call Hello World – the Option Private Module at the top does not prevent us from calling the Sub Procedure – all it serves to do is hide the Sub Procedure in the Macro Window.
We also do not need the Call statement – it is there to make the code easier to read.
The code could also look like this below:
We can also run the HelloWorld Sub Procedure by using the VBA Application.Run command.
In Module3 however, the GoodMorningWorld procedure has been declared Private. You cannot call it from another module using ‘normal’ means ie the Call statement.
You have to use Application.RunCommand to run a Private Sub from another module.
Notice the when you use the Application.RunCommand command, you have to put the Sub Procedure name within inverted commas.
If we do try to use the Call statement to run the GoodMorningWorld Sub Procedure, an error would occur.
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! 
VBA Code Examples Add-in
Easily access all of the code examples found on our site.
Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.
Private vs Public Subs, Variables & Functions in VBA
When writing VBA macros, the concept of Private or Public is important. It defines how VBA code within one module can interact with VBA code in another module. This concept applies to both Private Subs and Private Functions.
As a simple analogy – on social media, you can set parts of your profile so that everybody can see it (Public), or only those you allow, such as friends or followers, to see it (Private). The Private vs. Public concept in VBA is similar, but since we’re talking about VBA here, it’s not quite as straightforward.
Before we launch into the difference between Public and Private, we first need to understand what Modules are and how they work.
Modules
Modules are the place where VBA code is written and stored. There are many different module types in Excel, and we use each module for a different purpose.
Worksheet Modules
Worksheet Modules are generally used to trigger code related to that specific worksheet. Each worksheet contains its own module, so if there are 6 worksheets, then we have 6 Worksheet Modules.
In the screenshot above, the VBA code is contained within the Worksheet Module of Sheet1. As we have used the Worksheet_Activate event, the code is triggered only when Sheet1 is activated. Any event-based code (such as worksheet activation) in a Worksheet Module only applies to the worksheet in which the code is stored.
Workbook Module
The Workbook Module is generally used to trigger code related to workbook-level events.
In the screenshot above, we have used the Workbook_Open event. Therefore, the VBA code will run when a workbook is opened. Every workbook has its own module.
UserForm Module
UserForm Modules generally contain code that relates to UserForm events. Each UserForm has its own module.
In the screenshot above, the VBA code will run when the user clicks on CommandButton1 in the UserForm.
Standard Modules
Standard Modules are not related to any specific objects and do not have any events related to them. Therefore, standard Modules are not triggered by user interaction. If we are relying on triggered events, we need the Workbook, Worksheet, or UserForm Modules to track the event. However, that event may then call a macro within a Standard Module.
TIP: Find out how to run a macro from another macro here: Run a macro from a macro (from another workbook)
The screenshot above shows a code that password protects the ActiveSheet, no matter which workbook or worksheet.
Other Module Types
The final type of VBA module available is a Class Module. These are for creating custom objects and operate very differently from the other module types. Class Modules are outside the scope of this post.
Public vs. Private
The terms Public and Private are used in relation to Modules. The basic concept is that Public variables, Subs, or Functions can be seen and used by all modules in the workbook, while Private variables, Subs, and Functions can only be used by code within the same module.
Declaring a Private Sub or Function
To treat a Sub or Function as Private, we use the Private keyword at the start of the name.
Declaring a Public Sub or Function
To treat a Sub or Function as Public, we can use the Public keyword. However, if the word Public or Private is excluded, VBA treats the sub/function as if it were public. As a result, the following are all Public, even though they do not all include the keyword.
Let’s look at Subs and Functions in a bit more detail
Sub procedures (Subs)
When thinking about the difference between a Public Sub and a Private Sub, the two primary considerations are:
- Do we want the macro to appear in the list of available macros within Excel’s Macro window?
- Do we want the macro to be run from another Macro?
Does it appear in the Macro window?
One of the most important features of Private subs is that they do not appear in Excel’s Macro window.
Let’s assume Module1 contains the following two macros:
The Macro dialog box only displays the Public sub.
I don’t want you to jump to the conclusion that all Public Subs will appear in the Macro window, as that is not true. Any Public sub which requires arguments, also does not appear in this window, but it can still be executed if we know how to reference it.
Can the code be run from another macro?
When we think about Private Subs, it is best to view them as VBA code that can only be called by other code within the same module. So, for example, if Module1 contains a Private Sub, it cannot be called by any code in another module.
Using a simple example, here is a Private Sub in Module1:
Now let’s try to call the ShowMessage macro from Module2.
Running CallAPrivateMacro generates an error, as the two macros are in different modules.
If the ShowMessage macro in Module1 were a Public Sub, it would execute correctly.
There are many ways to run a macro from another macro. One such method allows us to run a Private Sub from another Module. If we use the Application.Run command, it will happily run a Private sub. Let’s change the code in Module2 to include the Application.Run command:
Instead of an error, the code above will execute the ShowMessage macro.
Working with object-based module events
Excel creates the Worksheet, Workbook, and UserForm Module events as Private by default, but they don’t need to be. If they are changed to Public, they can be called from other modules. Let’s look at an example.
Enter the following code into the Workbook Module (notice that I have changed it to a Public sub).
We can call this from another macro by using the object’s name followed by the name of the Public sub.
This means that we can run the Workbook_Open event whenever we need to. If the sub in the Workbook Module is Private, we can still use the Application.Run method noted above.
Functions
VBA functions are used to return calculated values. They have two primary uses:
- To calculate a value within a cell on a worksheet (known as User Defined Functions)
- To calculate a value within the VBA code
Like Subs, Functions created without the Private or Public declaration are treated as Public.
Calculating values within the worksheet (User Defined Functions)
User Defined Functions are worksheet formulas that operate similarly to other Excel functions, such as SUMIFS or XLOOKUP.
The following code snippets are included within Module1:
If we look at the Insert Function dialog box, the IAmVisible function is available as a worksheet function.
Functions must be declared in a Standard Module to be used as User Defined Functions in an Excel worksheet.
Function within the VBA Code
Functions used within VBA code operate in the same way as subs; Private functions should only be visible from within the same module. Once again, we can revert to the Application.Run command to use a Private function from another module.
Let’s assume the following code were entered into Module2:
The code above will happily call the NotVisible private function from Module1.
Variables
Variables hold values or references to objects that change while the macro runs. Variables come in 3 varieties, Public, Private and Dim.
Public Variables
Public variables must be declared at the top of the code module, directly after the Option Explicit statement (if you have one), and before any Subs or Functions.
The following is incorrect and will create an error if we try to use the Public Variable.
The correct approach would be: (The Public variable is declared before any subs or functions):
As it is a Public variable, we can use and change the variable from any module (of any type) in the workbook. Look at this example code below, which could run from Module2:
Private Variables
Private Variables can only be accessed and changed by subs and functions within the same Module. They too, must also be declared at the top of the VBA code.
The following demonstrates an acceptable usage of a Private variable.
Dim Variables
Most of us learn to create variables by using the word Dim. However, Dim variables behave differently depending on how they are declared.
Dim variables declared within a Sub or Function can only be used within that Sub or Function. In the example below, the Dim has been declared inside a Sub called CreateDim, but used within a sub called UseDim. If we run the UseDim code, it cannot find the Dim variable and will error.
If a Dim variable is created at the top of the module, before all the Subs or Functions, it operates like a Private variable. The following code will run correctly.
Does this really matter?
You might think it sounds easier to create everything as Public; then it can be used anywhere. A logical, but dangerous conclusion. It is much better to control all sections of the code. Ask yourself, if somebody were to use your macro from Excel’s Macro window, should it work? Or if somebody ran your function as a User Defined Function, should it work? Answers to these questions are a good guiding principle to help decide between Public and Private.
It is always much better to limit the scope of your Subs, Functions, and variables initially, then expand them when required in specific circumstances.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn’t until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I’m guessing the examples in this post don’t exactly match your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Excel VBA — Intro to Sub Procedures
As you continue your development in Excel VBA, it’s crucial to understand how Sub Procedures work. They are the building blocks of your VBA code and knowing how to work with them efficiently will help you make much better VBA solutions. In today’s post, we dig into what Sub Procedures are, how to call them, passing parameters, and we’ll also cover best practices.
Let’s get started.
What is a Sub Procedure?
Think of a sub procedure as the container for your macro. Each sub procedure can be thought of as its own macro. They can also modify the workbook’s contents which is different than VBA function procedures (or simply, functions) where they can pretty much only pass back a value (more on functions in a future post).
You can give data to sub procedures to work on by passing in parameters when calling the it.
A sub procedure is also called a subroutine.
Does this article help you? If so, please consider supporting me with a coffee ☕️
Syntax for Creating Sub Procedures
The basic syntax for a sub procedure looks like this:
The examples above show different ways we can declare the subroutine. Typically, you will either use Public or Private. There are other types, but they are less-often used and we can dig into those in a future post.
Notice that the default visibility is Public. I always explicitly declare the sub procedure’s visibility so it’s very clear what’s going on. In my examples, you will probably always see me put Public or Private.
What Does Public and Private Mean?
The Public and Private accessibility modifiers are used to either make your sub procedures visible in all modules (Public) or only visible within the current module (Private).
Public Accessibility Modifier
The Public accessibility modifier allows the subroutine to be visible throughout all modules in the workbook. Here’s an example where we have 2 modules (Module1 and Module2) where the sub procedure in Module1 calls the sub procedure in Module2:
Here is the output of that code:
The Public accessibility modifier also allows your macro to be called from the workbook itself, as long as the module does not have the Option Private Module statement listed.
Here are two sub procedures in Module1. One of them is public and the other is private. When we go to the Developer tab and click on Macros, we only see the public one.
Private Accessibility Modifier
The Private accessibility modifier allows your sub procedure to be visible to all other sub procedures, but only with the module that it lives in.
Also, as you saw in the previous section, private subroutines don’t show up to the end user when they click on the Macros button in the workbook.
Why is this useful?
If you have a sub procedure that does a lot of work, it would be best to split that work up into smaller bits by creating other sub procedures. However, you wouldn’t want those smaller bits be able to be called from the end user because maybe you have to call all of these smaller sub procedures in a specific order, or maybe you don’t want to confuse the end user with lots of subroutines to choose from. The Private accessibility modifier allows you to hide those smaller bits.
Let’s take a quick look at calling a private sub procedure from within the same module.
Here, we have a public subroutine that we’ll display to the end user. This calls a private subroutine called DoSomeWork that outputs some text to the Immediate Window. Then it calls MyPrivateSub to display a message to the end user.
Also, if you try to call a private subroutine that lives in another module, you will get an error:
Calling a Sub Procedure From Another Sub Procedure
Calling a sub procedure from another one is pretty straightforward. There are two ways that you can call another subroutine from another:
At first glance, you might think that the Call keyword doesn’t really make a difference. However, there are 2 things to note:
- The Call keyword lets you know that another sub procedure is being called rather than a variable.
- The Call keyword allows you to use parenthesis when passing parameters to another subroutine.
Take a look at how to pass parameters to other sub procedures:
With the Call keyword, you can specify parenthesis. If you’re familiar with other programming languages (like C#) you might feel more comfortable with this.
Speaking of passing parameters to your subroutine, let’s go over that.
Passing Data to Your Sub Procedures
As you do more development with macros, you will definitely want your sub procedures work with data. The best way to do that is by passing in data to your subroutines. Yes, you can technically share global variables across subroutines, but honestly, it’s just not good practice in my opinion. Stick to passing parameters to your subroutines. You’ll find maintaining your code much easier later on.
So how can we pass parameters to our sub procedures? There are a few ways you can approach this, the main two being with ByVal and ByRef.
Passing Parameters with ByVal
When passing parameters with ByVal (short for By Value), this means that the argument that is passed in is a copy of the data that was passed to it. Any changes to the argument variable will be lost after the procedure finishes.
For example, let’s say we have this code:
Here, we call a sub procedure with an argument and change that argument within the called subroutine. However, notice that the variable that we passed in does not change after the private subroutine finishes.
PLEASE NOTE: even though we named our parameter name which is the same as the variable name in our public subroutine, they are not connected to each other by their variable name. We can accomplish the exact same thing with the following code:
Passing Parameters with ByRef
When passing parameters with ByRef (short for By Reference), this means that the variable that you pass to another subroutine will get a copy of the address in memory to that variable. What that means is that the argument that is passed into the sub procedure has the ability to modify the calling variable’s data.
Let’s demonstrate this with an example:
Here, we used the same code as in the ByVal section above, but we changed the parameter to use the ByRef keyword. Now when we change the data, it’s reflected back in the calling subroutine.
The ByRef keyword can save you a lot of memory. For example, if you have to pass a very large string to another sub procedure, using ByVal will cause your code to create a copy of the entire string. This can quickly consume a lot of memory if you’re not careful. However, you also need to be careful when handling variables passed in with ByRef because if you inadvertently change the data and you didn’t mean to, you can cause bugs in your own code.
Declaring Optional Parameters and Setting Defaults
If you would like to create an optional parameter, you can put the Optional keyword before the parameter name like so:
This shows that you do not have to specify a parameter if you do not want to.
You can also specify a default for the optional parameter like so:
I would recommend always setting a default if possible.
Also, if you need to check if that variable was passed in, you can use the IsMissing() function.
Note that this only works when you have an optional parameter of the type Variant .
Some Best Practices
Let’s close off this post with some best practices to remember:
- Always declare Public or Private on your sub procedures
- Always declare ByVal or ByRef for your parameters
- Use ByRef to help you save memory in your macros, but beware of modifying that parameter’s data
- Always specify a default value for optional parameters if possible
Wow, you read the whole article! You know, people who make it this far are true learners. And clearly, you value learning. Would you like to learn more about Excel? Please consider supporting me by buying me a coffee (it takes a lot of coffee to write these articles!).