Automatic procedures and events in Excel VBA (2023)

In this chapter

Know the types of events that can trigger execution. Determine where to put the event-handling VBA code. Run a macro when a workbook is opened or closed. Run a macro when a workbook or worksheet is activated. One way is to let Sub run automatically. In this chapter, I'll go over the ins and outs of this potentially useful feature and explain how to set things up so that a macro runs automatically when a certain event occurs. (No, this chapter is not about the death penalty.)

Preparation for the big event

What events am I talking about here? Good question. An event is basically something that happens in Excel. The following are some examples of the types of events Excel can handle:
A book opens or closes.
A window will be activated.
A table is activated or deactivated.
Data is entered into a cell or the cell is edited.
A workbook is saved.
A worksheet is calculated.
On an object, e.g. B. a button is clicked.
A specific key or key combination is pressed.
A cell is double-clicked.
It happens at a certain time of day.
An error occurs.
Most Excel programmers don't need to worry about most of the events on this list. However, you should at least know that these events exist because they might be useful one day. In this chapter, I discuss the most commonly used events. For the sake of simplicity, I'm talking about two types of events: workbook and worksheet.

Table 11-1 lists most events related to the workbook. You can access the full list by following these instructions:

1. Select the This Workbook object in the project pane.
2. View the code window.
To do this, choose Display code or press F7.
3. Select the workbook object from the object drop-down list (upper left of the code window).
4. Expand the Procedure drop-down list (upper right of the code window).

Tabla 11-1Workbook Events
incidentwhen activated
activateThe workbook is activated.
additional installationA plugin is installed (only relevant for plugins).
Uninstall addonThe plugin is uninstalled (only relevant for plugins).
AntesCerrarThe book is closed.
Before printingThe workbook is printed.
beforeSaveThe workbook is saved.
Disable somethingThe book is deactivated.
NovaFolhaA new worksheet is added to the workbook.
OpenThe book opens.
WorksheetActivateA worksheet is activated in the workbook.
Table before DoubleClickDouble-click a cell in the workbook.
Worksheet before right-clickRight-click a cell in the workbook.
incidentwhen activated
SpreadsheetA worksheet in the workbook is recalculated.
blade changeA change is made to a cell in the workbook.
Disable SpreadsheetA worksheet in the workbook is disabled.
WorksheetFollowHyperlinkA hyperlink is clicked in a table.
SheetSelectionChangeThe selection is changed.
WindowActivateThe book window is activated.
disable windowThe book window is disabled.
RedimensionarVentanaThe workbook window is resized.

Table 11-2 lists most worksheet events. These events can be accessed by following these instructions:

1. Select a sheet object in the project window.
2. View the code window.
3. Select the worksheet object from the object list (at the top of the code window).
4. Expand the Procedure drop-down list.

Tabla 11-2Worksheet Events
incidentwhen activated
activateThe worksheet is activated.
AntesDoubleClickDouble-click a cell in the table.
Before right clickRight-click a cell in the table.
calculationThe table is recalculated.
changeA change is made to a cell on the worksheet.
Disable somethingThe table is disabled.
follow hyperlinksA hyperlink will be activated.
selectionchangeThe selection is changed.

Are the events useful?

At this point, you might be wondering how useful these events can be. Here's a quick example.
Suppose you have a workbook that other people use to enter data. Any value entered must be greater than 1000. You can write a simple macro that Excel will run every time someone enters data in a cell. (The data entry is an event). If the user enters a value less than 1000, the macro displays a dialog box berating the user.
The Data Validation command in Excel provides another way to perform this type of data entry validation, even without using VBA. However, as you will see later in this chapter (see “An Example of Data Validation”), there are some distinct benefits to using VBA for data validation.
This is just one example of how you can use an event. Read on to see more examples.

Scheduling event handler procedures

A VBA procedure that runs in response to an event is called an event handler procedure. They are always Sub procedures (as opposed to Function procedures). Writing these event handlers is relatively easy once you understand how the process works. It all boils down to a few steps that I will explain later:
1. Identify the event for which you want to trigger the procedure.
2. Press Alt+F11 to activate the Visual Basic Editor.
3. In the VBE project window, double-click the appropriate object listed under Microsoft Excel Objects.
For workbook-related events, the object is This Workbook. For a workbook-related event, the object is a worksheet object (e.g., Sheet1).
4. In the object code window, enter the event handler procedure that will be executed when the event occurs.
This procedure has a special name that identifies it as an event handler procedure.
These steps will become clearer as the chapter progresses. Trust me.

(Video) Excel VBA Introduction Part 20.1 - Event Procedures

Where does the VBA code go?

It is very important to understand where the event handler procedures go. You must be in the code window of an object module. They just don't work if you put them in a standard VBA module.
Figure 11-1 shows the VBE window with a project displayed in the project pane. (For information about VBE, see Chapter 3.) Note that the project consists of several objects:
An object for each sheet in the book (three Sheet objects in this case)
An object named This Workbook
A VBA module that I manually inserted using the Insert Module command
Automatic procedures and events in Excel VBA (1)
Figure 11-1:
The VBE window displays items for a single project.
Double-clicking on one of these objects will display the code associated with the item, if any.
Event handler procedures that you write go to the This Workbook item (for workbook-related events) or to one of the Sheet objects (for worksheet-related events) in the code window.

Write an event handler procedure

The VBE helps you when you're ready to write an event-handling procedure; displays a list of all events that Excel can recognize.
Figure 11-2 shows a code window for the This Workbook object (the code window is maximized to fill the entire area of ​​the code window). To display this blank code window, double-click the This Workbook object in the project pane. This code window has two drop down lists at the top.
Automatic procedures and events in Excel VBA (2)
Figure 11-2:
An empty code window for the This Book object.
By default, the Object (left) drop-down list in the code window displays General. To write an event handler procedure, you must select Workbook from the Object drop-down list. (The workbook is the only other item in the list.) If the event handler is for a sheet, double-click the appropriate sheet item in the project pane before selecting Sheet from the Object drop-down list.
Figure 11-3 shows the drop-down list on the right, which consists of all workbook-related events that Excel recognizes. When you select an event from the list, the VBE automatically starts creating an event handler procedure for you. (When you first selected the workbook from the list of objects, the VBE assumed that you wanted to create an event handler procedure for the Open event and created it. You can see this in Figure 11-3 see.)
Automatic procedures and events in Excel VBA (3)
Figure 11-3:
The drop-down list shows all events related to the workbook.
However, VBE's help only goes so far. Write the Sub statement and the End Sub statement. It's up to you to write the VBA code between these two statements.
Some event-handling procedures accept one or more arguments in the Sub statement. For example, when you select Activate Sheet from the event list for a workbook object, the VBE writes the following sub-statement:
Automatic procedures and events in Excel VBA (4)
In this case, Sh is the argument passed to the procedure and is a variable that represents the worksheet in the activated workbook. The examples in this chapter make this point clear.

introductory examples

In this section, I'll give some examples to help you understand this event handling business.

Das Open for a Book-Event

One of the most commonly used events is the Open Workbook event. Suppose you have a workbook that you use every day. The Workbook_Open procedure in this example runs each time the workbook is opened. EITHER
the procedure checks the day of the week; If it's Friday, the code will display a reminder message for you.
To create the handler that runs each time the Open Workbook event occurs, complete the following steps:
1. Open the workbook.
Any workbook will do.
2. Press Alt+F11 to activate the VBE.
3. Locate the workbook in the project window.
4. Double-click the project name to view its items, if necessary.
5. Double-click the This Book item.
The VBE displays an empty code window for the This Book object.
6. In the code window, select Workbook from the Object (left) drop-down list.
The VBE inserts start and end statements for a Workbook_Open procedure.
7. Enter the following statements:
Automatic procedures and events in Excel VBA (5)
The code window should now resemble Figure 11-4.
Automatic procedures and events in Excel VBA (6)
Figure 11-4:
The event handler procedure runs when the workbook is opened.
Workbook_Open runs automatically every time the workbook is opened. Use the VBA function WeekDay to determine the day of the week. If it is Friday (Day 6), a message box will remind the user to back up files weekly. If it's not Friday, nothing happens.
If today is not a Friday, you may have trouble trying this procedure. Here you have the opportunity to test your own knowledge of VBA. You can change this procedure as you like. For example, the following version shows a message each time the book is opened. It gets boring after a while, trust me.
Automatic procedures and events in Excel VBA (7)

A Workbook_Open procedure can do almost anything. These event handlers are commonly used to:

Show welcome messages (like Frank's) Open other workbooks
Activate a specific sheet in the workbook. Configure custom menus. Show or hide toolbars
Note that if the user disables macros when the workbook is opened, the event handler procedures will not run. In other words, you can't rely on your event-handling procedures to work every time.

The Before Close event for a workbook

This is an example of the Workbook_Before Close event handler procedure that runs automatically just before the workbook is closed. This procedure is in the code window of a This Book object:
Automatic procedures and events in Excel VBA (8)
This routine uses a message box to ask the user if they want to save the workbook. If so, the code uses the Save a Copy As method to save a backup copy of the file to drive F. If you adapt this procedure for your own use, you may need to change the drive and path.
Excel programmers often use a Workbook_Before Close procedure to clean up after itself. For example, if you use a Workbook_Open procedure to change some settings when you open a workbook (e.g. hide the status bar), it is appropriate to reset the settings to their original state when you close the workbook. You can perform this electronic cleanup with a Workbook_Before Close procedure.

(Video) Free Excel VBA Course #23 - Event Procedures in VBA

The Before Save event for a workbook

The Before Save event, as the name suggests, is raised before a workbook is saved. This event occurs when you use the Save File or Save File As command.
The following procedure, pasted into the code window for a This Book object, demonstrates the Before Save event. The routine updates the value in a cell (cell A1 on Sheet1) each time the workbook is saved. In other words, cell A1 acts as a counter to keep track of the number of times the file has been saved.
Automatic procedures and events in Excel VBA (9)
Note that the Workbook_Before Save procedure has two arguments, Save As UI and Cancel. To illustrate how these arguments work, consider the following macro, which runs before the workbook is saved. This procedure prevents the user from saving the workbook with a different name. When the user selects the Save File As command, the UI Save As argument is true.
When the code runs, it checks the Save As UI value. If this variable is True, the procedure displays a message and sets Cancel to True, which aborts the save operation.
Automatic procedures and events in Excel VBA (10)

Examples of activation events

Another category of events is turning objects on and off, especially sheets and windows.

Enable and disable events in a table

Excel can detect when a particular worksheet is activated or deactivated and run a macro when one of these events occurs. These event handler procedures go to the code window of a leaf object.
The following example shows a simple procedure that runs each time a specific worksheet is activated. This code simply displays a message box with the name of the active worksheet:
Automatic procedures and events in Excel VBA (11)
Here's another example that activates cell A1 every time the table activates:
Automatic procedures and events in Excel VBA (12)
While the code for these two procedures is as simple as possible, the event handler procedures can be as complex as you like.
The following procedure (stored in the Sheet1 object's code window) uses the Deactivate event to prevent a user from activating other sheets in the workbook. If sheet1 is disabled (ie another sheet is enabled), the user will receive a message and sheet1 will be enabled.
Automatic procedures and events in Excel VBA (13)

Enabling and disabling events in a workbook

The previous examples use events associated with a spreadsheet. The This Workbook object also handles events that handle worksheet activation and deactivation. The following procedure stored in the code window
for the This Workbook object runs when any worksheet in the workbook is activated. The code displays a message with the name of the enabled table.
Automatic procedures and events in Excel VBA (14)
The Workbook_Sheet Activate procedure takes the sh argument. Sh is a variable that represents the active sheet object. The message box displays the Sheet object's Name property.
The following example is contained in a Code This Book window. It consists of two event handler procedures. Workbook_Sheet Deactivate is executed when a sheet is deactivated. Stores the disabled worksheet in an object variable. (The Set keyword creates an object variable.) The Workbook_Sheet Activate code checks the type of activated sheet (using the TypeName function). If the sheet is a chart sheet, the user will receive a message and the previous sheet (stored in the Old Sheet variable) will be reactivated. The effect is that users cannot activate a chart sheet (and always go back to the previous sheet if they try).
A workbook with this code is available on the dedicated website.
Automatic procedures and events in Excel VBA (15)

Book activation events

Excel also recognizes the event that occurs when you activate or deactivate a specific workbook. The following code, contained in the code window of this book object, runs every time the book is activated. The procedure simply maximizes the book window.
Automatic procedures and events in Excel VBA (16)
The Workbook_Deactivate code shown below runs when a workbook is deactivated. This procedure minimizes the workbook window:
Automatic procedures and events in Excel VBA (17)
Note that I didn't use Active Window in this code. This is because the workbook is no longer the active window when it is deactivated. So I used This Workbook which refers to the workbook that contains the code.

Other sheet-related events

In the previous section, I presented examples of worksheet activation and deactivation events. In this section, I discuss three additional events that occur in worksheets: double-clicking a cell, right-clicking a cell, and changing a cell.

(Video) Excel VBA Objects: Events and Event Procedures

The Before Double Click event

You can set up a VBA procedure to run when the user double-clicks a cell. In the following example (saved in a Sheet object's code window), double-clicking a cell makes the cell bold (if it's not bold) or non-bold (if it's bold):
Automatic procedures and events in Excel VBA (18)
The procedure Worksheet_Before Double Click has two arguments: target and cancel. Target represents the cell (a Range object) that was double-clicked. When Cancel is set to True, the default double-click action is not performed.
Notice that I've set the Cancel argument to True. This prevents the default action from taking place. In other words, double-clicking the cell does not put Excel into cell-editing mode.

The Before Right Click event

The Before Right Click event is similar to the Before Double Click event, except that it involves a right click on a cell. The following procedure checks whether the cell you right-clicked contains a numeric value. If yes, the
The code displays the Number Format dialog and sets the Cancel argument to True (which prevents the context menu from appearing normally). If the cell does not contain a numeric value, nothing special happens: the context menu is displayed normally.
Automatic procedures and events in Excel VBA (19)
Note that the code available on the website for this topic performs an additional check to see if the cell is empty. This is because VBA considers empty cells as numeric.

the change event

The change event occurs whenever any cell in the worksheet is changed. In the example below, the Worksheet_Change procedure effectively prevents a user from entering a non-numeric value in cell A1. This code is stored in the code window of a Sheet object.
Automatic procedures and events in Excel VBA (20)
The single argument of the Worksheet_Change procedure represents the range that was changed. The first statement checks if the cell address is $A$1. In this case, the code uses the Is Numeric function to determine if the cell contains a numeric value. Otherwise, a message is displayed and the cell value is cleared. Cell A1 is then activated, useful if the cell pointer has moved to another cell after typing. If the change occurs in any cell except A1, nothing happens.

Why not use Excel's data validation command?

You may be familiar with the Data Validation command. This is a useful feature that makes it easy to ensure that only the right type of data is being entered into a given cell or range. While the data validation command is useful, it's definitely not foolproof. To demonstrate this, start with a blank worksheet and do the following:
1. Select the range A1:C12.
2. Choose Data Validation.
3. Set your validation criteria to only accept integers between 1 and 12, as shown in Figure 11-5.
Automatic procedures and events in Excel VBA (21)
Figure 11-5:
These settings only allow integers between 1 and 12.
Now enter some values ​​in the range A1:C12. Data validation works as it should. But to see it fall apart, try this:
1. Type -1 in any cell outside the validation range (any cell outside of A1:C12).
2. Choose Edit Copy to copy the negative number to the clipboard.
3. Select any cell in the validation area.
4. Choose Edit Paste.
It finds out that the insert operation is allowed. However, if you look a little closer, you'll see that the cell you pasted the negative value into no longer has any validation criteria. Pasting will clear the data validation criteria! The severity of this error depends on your application. In the next section, I describe how to use the Change event to provide better validation.
Pasting will clear the data validation as Excel sees validation as a format for a cell. This means that it ranks in the same order by font size, color, or other similar attributes. When you paste a cell, replace the formats of the destination cell with those of the source cell. Unfortunately, these formats also contain their validation rules.

An example of data validation.

The following procedure shows a better alternative to Excel's data validation command. Ensures that only positive values ​​are entered in the range A1:C12.

A workbook with this code is available on the website for this topic:

Automatic procedures and events in Excel VBA (22)
The procedure begins by creating an object variable (ValRange) that represents the range to be validated. DataOK is a Boolean variable that is initially set to True. The for-next loop examines each cell in Target (which is the cell or range that changed). I use the Union function to determine if the cell is in ValRange. In this case, an if statement determines whether the cell's value is less than 0. In this case, the content is deleted and DataOK is set to False.
When all cells have been checked, another If statement checks the value of DataOK. When set to False, one or more cells in the changed range are negative. Therefore, the user receives a message. This routine also works when data is copied and pasted into the validation area.

(Video) Microsoft Excel Video 27 VBA and Event Procedures

Events not associated with objects

The events discussed earlier in this chapter are associated with either a workbook object or a worksheet object. In this section, I discuss two types of events that aren't associated with objects: time and keystrokes.
Because times and keystrokes are not associated with a specific object, such as a workbook or worksheet, these events are scheduled in a regular VBA module (unlike the other events discussed in this chapter).

the event on time

The OnTime event occurs when a specific time of day occurs. The following example shows how to program Excel to beep and display a message at 3:00 p.m. m.:
Automatic procedures and events in Excel VBA (23)
In this example, I use the Application object's OnTime method. This method takes two arguments: the time (0.625 or 15:00) and the code to run when the time comes (display an alarm).
This technique is very useful when you tend to get so absorbed in your work that you forget about meetings and appointments. Just set an OnTime event as a reminder.
Most people (including this author) find it difficult to visualize time in terms of Excel's number system. Therefore, you may want to use VBA's time value function to represent time. TimeValue converts a string that looks like a time into a value that Excel can process. The following statement shows an easier way to schedule an event for 3:00 p.m. m.:
Automatic procedures and events in Excel VBA (24)
If you want to schedule an event relative to the current time, e.g. in 20 minutes, you can use a statement like this:
Automatic procedures and events in Excel VBA (25)
You can also use the OnTime method to run a VBA procedure on a specific day. You need to make sure your computer is still running and the workbook containing the procedure is open. The following statement runs the Show Alarm at 5:00 PM procedure. m. December 31, 2005:
Automatic procedures and events in Excel VBA (26)
That particular line of code can be useful for letting you know that you need to go home and get ready for the New Year's Eve celebrations.
The OnTime method has two additional arguments. If you want to use this method, see the online help for complete information.

Keypress Events

As you work, Excel constantly monitors what you type. Because of this, you can configure a keystroke or key combination to perform a procedure.

Here's an example that remaps the PgDn and PgUp keys:

Automatic procedures and events in Excel VBA (27)
After configuring the OnKey events by running the Setup_OnKey procedure, pressing PgDn scrolls down one line. Pressing Page Up moves it up one line.
Note that keycodes are enclosed in square brackets, not parentheses. See the help system for a complete list of keyboard codes. Search for OnKey.
In this example I use On Error Resume Next to ignore generated errors. For example, if the active cell is in the first row, trying to move up one row will generate an error that can safely be ignored. Also note that the procedures check which worksheet type is active. The routine remaps the PgUp and PgDn keys only when a leaf is the active leaf.

Running the following routine will cancel the OnKey events:

Automatic procedures and events in Excel VBA (28)
Using an empty string as the second argument to the OnKey method does not cancel the OnKey event. Instead, it causes Excel to simply ignore the keystroke. For example, the following statement tells Excel to ignore Alt+F4. The percent sign represents the Alt key:
Automatic procedures and events in Excel VBA (29)
Although you can use the OnKey method to assign a key combination to run a macro, you must use the Macro Options dialog box for this task. See Chapter 5 for more details.


Which event runs macro automatically in Excel VBA? ›

Excel Event code makes this possible and it is easier than you think. Event code can run a macro based on a drop-down list, event code is VBA code stored in a worksheet module or workbook module.

What are event Procedures VBA? ›

An Event Procedure is a VBA Sub procedure that is executed automatically by Excel when the event is raised. It is important to remember that in addition to user input, events may run as the results of actions taken by other VBA code.

How do I make Excel VBA run automatically? ›

Using Auto open method to run a macro automatically:
  1. Open an excel workbook.
  2. Press Alt+F11 to open VBA Editor.
  3. Insert a New Module from Insert Menu.
  4. Copy the above code and Paste in the code window.
  5. Save the file as macro enabled workbook.
  6. Open the workbook to test it, it will Run a Macro Automatically.

What are the two principle kinds of procedure in VBA? ›

Visual Basic uses several types of procedures: Sub Procedures perform actions but do not return a value to the calling code. Event-handling procedures are Sub procedures that execute in response to an event raised by user action or by an occurrence in a program.

How do I make a macro run automatically daily? ›

Excel Macro to Automatically Run a Macro at a Certain Time
  1. Select and copy the text from within the grey box above.
  2. Open the Microsoft Excel file in which you would like the Macro to function.
  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.

Can you set a macro to run automatically? ›

Steps to run macro automatically when the workbook opens:

Step 2: Go to ThisWorkbook Tab. Step 3: Write down Private Sub Workbook_open() and hit enter. You will then see the below screen. You can write your code or basically whatever you want between this and it will automatically run every time the workbook is opened.

What are the three types of VBA procedures you can create? ›

You can create a Sub, Function, or Property procedure. Type Sub, Function, or Property.

What is difference between procedure and Function in VBA? ›

A procedure can be used to read and modify data. 7. The return statement of a function returns the control and function's result value to the calling program. While the return statement of the procedure returns control to the calling program, it can not return the result value.

What is event & procedure? ›

In this context, an event procedure is an internal procedure that executes in response to a PROCEDURE-COMPLETE event. To define an event procedure you must: Specify the name of the event procedure using the EVENT-PROCEDURE option on the RUN... ASYNCHRONOUS statement that executes the request.

What can be automated using VBA? ›

With VBA you can create macros to automate repetitive word- and data-processing functions, and generate custom forms, graphs, and reports. VBA functions within MS Office applications; it is not a stand-alone product.

What is automatic macro? ›

Automatic macro variables are created by the macro processor and they supply a variety of information. They are useful in programs to check the status of a condition before executing code. You reference automatic macro variables such as &SYSLAST or &SYSJOBID the same way you do macro variables that you create.

How do I automate an Excel spreadsheet using Macros? ›

To create a macro, go to View > Macros > Record Macro. Assign the macro a name (no spaces) and click OK. Once this is done, all of your actions are recorded – every cell change, scroll action, window resize, you name it. There are a couple of places which indicate Excel is record mode.

What are the four types of procedure? ›

Return more than one row.
Types of Procedures
  • Transform procedures.
  • Source procedures.
  • Target procedures.

What is the difference between procedure and macros? ›

The main difference between procedures and macros is that a procedure may contain logical statements which determine the flow and execution of the commands, while a macro has less logic capability.

What are the 3 different types of error handling techniques in VBA? ›

AutoCAD to Excel - VBA Programming Hands-On!

There are three types of errors in programming: (a) Syntax Errors, (b) Runtime Errors, and (c) Logical Errors.

How do I schedule a script to run automatically? ›

Double-click on the Task Scheduler, and then choose the option to 'Create Basic Task…' Type a name for your task (you can also type a description if needed), and then press Next. For instance, let's name the task as: Run Hello World. Choose to start the task 'Daily' since we wish to run the Python script daily at 6am.

Can a macro run automatically without opening Excel? ›

You can't run a Excel VBA Macro without opening the File that contains the macro. If you want you can launch the excel application in hidden mode and then run the macro after opening the file in hidden mode from a VBS file.

How do I make programs run automatically at a certain time? ›

To schedule an automatic task on your Windows computer, you'll first have to open the Task Scheduler. Here's what you've to do: Go to the Start menu search bar, type in 'task scheduler,' and select the best match. In the Task Scheduler menu, right-click on the Task Scheduler Library, and select New Folder

Can power automate run a VBA macro? ›

I created a video that demos how to use Power Automate Cloud and Desktop to trigger a VBA macro on a schedule in unattended mode on an Excel file resident on SharePoint Online (SPO). You can use VBA to refresh an external data query.

How do I automatically run a macro when a cell changes? ›

Go to the VBA Editor (Alt + F11) and double-click the name of the spreadsheet that contains the cell that will change or just right-click the worksheet tab and click View Code. In the window that opens, select Worksheet from the left drop-down menu and Change from the right drop-down menu.

How do I automatically run a macro based on a cell value? ›

How to run macro based on cell value in Excel?
  1. A1 is the cell which contains the specific value you want to run the macro based on;
  2. Case 10 To 50: Macro1: it means if the value is between 10 and 50, run Macro1;
  3. Case Is > 50: Macro2: it means if the value is greater than 50, run Macro2.

How many types of procedures are there in Visual Basic? ›

As we further explore the concept of procedures, there are two distinct but related types of procedures in VB: Sub Procedures. Function Procedures.

Where VBA procedures are stored? ›

Answer: VBA code is stored in the SQL VBACODE table of the database, and is unique to each database; i.e. Code written in the sample database will not be available when running the live database and vice versa. To access the VBA code from the front end, go to Tools > Visual Basic for Applications.

How many types of VBA are there? ›

VBA data types
Data TypeStoredRange of Values
Byte1 Byte0 to 255
Integer2 Bytes-32,768 to 32,767
Single4 Bytes-3.402823E38 to -1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values
Long4 Bytes-2,147,483,648 to 2,147,483,648
10 more rows

Which is faster procedure or function? ›

As you can see, the scalar functions are slower than stored procedures. In average, the execution time of the scalar function was 57 seconds and the stored procedure 36 seconds.

Which is better procedure or function? ›

The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values. Functions can have only input parameters for it whereas Procedures can have input or output parameters. Functions can be called from Procedure whereas Procedures cannot be called from a Function.

Why is a function better than a procedure? ›

A function is more than a procedure because return values can also be specified as the "output" in the body. Function calls are more or less same to procedure calls, except that you can also use the result of the function call, syntactically (usually as a subexpression of some other expression).

What are the 3 types of events? ›

What are the classifications of event types? Event types can be separated into corporate, private, or charity. Corporate events focus on businesses and customers, whereas private events are more recreational and charity events are for philanthropy.

What are 8 types of events? ›

8 types of events to get you started
  • Conferences.
  • Trade shows.
  • Networking events.
  • Workshops.
  • Team building events.
  • Product launch events.
  • Charity events.
  • Internal corporate events.
19 Oct 2022

What is event procedure with example? ›

What is event procedure with example? Event procedure names have the form objectName_event, where objectName is the name of the object on which the event occurred and event is the type of the event. For example, an event procedure named cmdClear_Click will be executed when the user clicks on the button named cmdClear.

What tasks can be automated in Excel? ›

Table of contents
  • Log forms and survey submissions.
  • Send notifications for important updates.
  • Update data across multiple spreadsheets.
  • Create tasks and projects.
  • Use webhooks to connect Microsoft Excel to almost any app.
25 Aug 2022

Are VBA skills in demand? ›

Are VBA skills in-demand? Yes, knowledge and skills in VBA are still highly sought after. According to the TIOBE index, Visual Basic for Applications ranks number six in their popular programming language list. The TIOBE index tracks the most popular programming languages through search engine queries.

Is VBA still worth learning? ›

Yes, absolutely! VBA is certainly not the most modern programming language, but due to the huge prevalence of Microsoft Office it is still a very relevant language in the year 2022.

What is the difference between macros and macro processors? ›

Macro represents a group of commonly used statements in the source programming language. Macro Processor replaces each macro instruction with the corresponding group of source language statements. This is known as the expansion of macros.

How do I automate iteration in Excel? ›

Change the number of times Excel iterates a formula
  1. Click the File tab, click Options, and then click the Formulas category. ...
  2. In the Calculation options section, select the Enable iterative calculation check box.

Does Excel have automations? ›

Excel automation uses robotic programming to automate Microsoft Excel processes and functions. Since 1982, Microsoft Excel has been supporting businesses with its amazing ability to calculate data across any number of Excel spreadsheets (AKA workbooks) and tabs (AKA worksheets).

What is VBA macros and automation? ›

What are VBA Macros in Excel? VBA Macros use the Visual Basic Application in Excel to create custom user-generated functions and speed up manual tasks by creating automated processes. Additionally, VBA can be used to access the Windows Application Programming Interface (API).

What is the difference between procedure and function? ›

A function would return the returning value/control to the code or calling function. The procedures perform certain tasks in a particular order on the basis of the given inputs. A procedure, on the other hand, would return the control, but would not return any value to the calling function or the code.

What are examples of procedures? ›

Installing a car battery is a simple procedure. What is the procedure for applying for a loan? New employees are taught the proper safety procedures. We must follow proper court procedure.

What is methods of procedures? ›

A method of procedure (MOP) is a step-by-step guideline for completing a project. Think of it as a recipe for accomplishing a business task. Businesses use MOPs to remove the guesswork and reduce human error.

What are the advantages of procedure over macro? ›

Procedures are also like macro, but they are used for large set of instruction when macro is useful for small set of instructions. It contains a set of instructions which performs a specific task.

Is procedure and stored procedure same? ›

Procedure is a block of PL/SQL code , it is named and stored within the database. Stored procedure is block of PL/SQL code it is named and stored within the database.

What is the difference between subroutine and procedure? ›

A procedure is a subroutine that performs a specific task. When the task is complete, the subroutine ends and the main program continues from where it left off. For example, a procedure may be written to reset all the values of an array to zero, or to clear a screen.

What is the best method for error-handling? ›

Error Handling Best Practices
  • Send error logs to an external service. ...
  • Use error objects in rules. ...
  • Use meaningful error code descriptions. ...
  • Exception handling. ...
  • Avoid uninitialized objects in rules. ...
  • Learn more.

What are the three main categories of the VBA statements? ›

Each statement belongs to one of the following three categories: Declaration statements, which name a variable, constant, or procedure and can also specify a data type. Assignment statements, which assign a value or expression to a variable or constant. Executable statements, which initiate actions.

What are VBA methods? ›

A VBA method is a piece of code attached to a VBA object, variable, or data reference that tells Excel what action to perform in relation to that object. Copying, pasting, and selecting are just some examples of VBA methods that can be performed. ( VBA stands for “Visual Basic for Applications”)

How do I trigger a macro in VBA? ›

Click on the macro button from the “Developer Tab” and open the list of macros. In this list of MACROS, you will have all the macro you have in the open workbooks, including the Personal Macro Workbook. Just select the macro you want to run and click on the “RUN” button.

What event is used to begin the execution of the macros? ›

A workbook event is defined as an action that triggers the execution of a specific macro in Excel. VBA automatically executes an event once a user specifies the code for an event that has already occurred.

Which event handling routine runs automatically when a workbook opens? ›

The Workbook_Open Event is automatically added to the module. This macro will run when the workbook is opened and macros are enabled. You can delete this event's code if you do not want to use it. Select another event from the Procedure's drop-down.

Which control is used to execute any macro VBA procedure? ›

Pressing F8 will let you step through the macro code one line at a time. Edit - This will open the Visual Basic Editor and let you edit the macro code as needed. Once you've made changes, you can press F5 to run the macro from the editor.

How do I run a macro on a schedule? ›

With your schedule open in Excel, select the DEVELOPER tab, then click the Macros button. 2C. In the Macro dialog box, select the macro you want to run on the schedule. Click Run to run the macro.

How do you run a macro step by step? ›

To begin single stepping while a macro is running, press CTRL+BREAK. To begin single stepping at a specific point in your macro, you can add the SingleStep macro action to your macro at the point where you want single stepping to begin.

How do I run macros automatically while opening workbook in Excel VBA? ›

Click Developer > Visual Basic. In the VBA Project Explorer on the left hand side, expand the VBA Project folder for your workbook, then double-click the ThisWorkbook module. Paste your recorded code in the Sub procedure between the Sub and End Sub lines. Close the Visual Basic Editor (you don't have to save anything).

Is VBA event driven? ›

Visual Basic for Applications (VBA) is an implementation of Microsoft's event-driven programming language Visual Basic 6.0 built into most desktop Microsoft Office applications.

What is enable events in VBA? ›

EnableEvents is a property in Excel VBA where you can specify whether you want events to take place when the VBA code is running or not. An event is usually attached to an object. VBA Events could include things such as activating a worksheet or saving a workbook or double-clicking on a cell.

How do I run a macro every 5 seconds? ›

Simply add an Application. OnTime method to the end of a macro that reruns itself in 5 seconds. Assign bKEEPWORKING a value of True and run the Deja_vu sub procedure. It will keep running until you set bKEEPWORKING to False.

How do I run a macro in immediate window? ›

To run a macro, all you need to do is enter the name of the macro in the immediate window and hit the enter key (the cursor should be at the end of the macro name for this to work).

How do I assign a macro to a cell in Excel VBA? ›

Click the worksheet location where you want the upper-left corner of the button to appear. The Assign Macro popup window appears. Note: If you have already inserted a button, you can right-click on it, and select Assign Macro. Assign a macro to the button and click OK.


1. Event Procedure Excel VBA-Workbook_Open
(Excel & VBA)
2. Event Procedure Excel VBA
(Excel & VBA)
3. Worksheet Change Event, Event Procedures in Excel VBA
(GlobaliConnect - Excel & VBA)
4. Learn Excel Reporting Automation For Beginners with Excel VBA (Code Included)
(Jie Jenn)
5. Worksheet change event Excel VBA
(Excel & VBA)
6. Excel VBA Introduction Part 20.2 - Application Events


Top Articles
Latest Posts
Article information

Author: Virgilio Hermann JD

Last Updated: 03/09/2023

Views: 5750

Rating: 4 / 5 (61 voted)

Reviews: 92% of readers found this page helpful

Author information

Name: Virgilio Hermann JD

Birthday: 1997-12-21

Address: 6946 Schoen Cove, Sipesshire, MO 55944

Phone: +3763365785260

Job: Accounting Engineer

Hobby: Web surfing, Rafting, Dowsing, Stand-up comedy, Ghost hunting, Swimming, Amateur radio

Introduction: My name is Virgilio Hermann JD, I am a fine, gifted, beautiful, encouraging, kind, talented, zealous person who loves writing and wants to share my knowledge and understanding with you.