Open In App

How to Insert and Run VBA Code in Excel?

Last Updated: 13 Nov, 2022

A

Ayush Dey
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

In Excel VBA stands for (Visual Basic for Application Code) where we can automate our task with help of codes and codes that will manipulate(like inserting, creating, or deleting a row, column, or graph) the data in a worksheet or workbook. With the help of VBA, we can also automate the task in excel to perform all these tasks we need to insert and run the VBA code properly which we will discuss in this article. 

Steps to Insert and Run VBA Code in Excel

To use the VBA code properly in Excel we need to change the default macro security settings of excel for that we need to follow further steps

Step 1: Click on the “File” menu at the left top of the excel tab. 

Clicking-file-menu

 

Step 2: Select “Options” to get the “Excel Options” window.

Selecting-options

 

Step 3: Select “Customized Ribbon” in the “Excel Options” Window and then select the “Developer” check box in the “Main Tabs”.

Go-to-customize-ribbon-option

 

Step 4: Then return to the main Excel window to select the “Developer” ribbon and then click on “Macro Security” in the “Code” group.

Selecting-developer-tab

 

Step 5: Click on “Macro Settings” to select “Disable all macros except digitally signed macros”.

Clicking-macro-settings

 

Now, to insert and run the VBA in Excel so that we can write codes we need to follow further steps:

Step 1: In the main Excel window press “Alt + F11” to open Visual Basic Editor

Opening-visual-basic-editor

 

Step 2: Select “Sheet1(Sheet1)” in the “Project – VBAProject” tab.

Selecting-project

 

Then we will get a terminal where we can write the following code,

Terminal-to-write-code

 

Step 4: After writing a piece of code according to the user’s need  we need to press “F5” to run the code and then click on “Run” in the “Macro” Tab

Running-code-in-macro-tab

 

Then, the output of the code will be seen in the Excel sheet.



Article Tags :

A

Ayush Dey
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

A

Ayush Dey

VBA Subroutine in Excel - How to Call Sub in VBA?

article_img
When a specified action is performed on a worksheet with the help of a collection of code known as a VBA Subroutine. It also helps to read an external file, also it can open other applications from Excel. A large piece of code can be broken into small parts so that we can manage it easily. Let's learn why to use submarines: Converts large piece of codes into small parts so that the computer ignores all kind of complexities that arises because of large codesReusability of code suppose we in a program have to access the database frequently so instead of writing the code again and again we can create a function to access the databaseSubroutines are self-documenting functions which means a coder can easily say what the program does by looking into the name of the function Naming Rules of SubroutinesIt can start with a letter or an underscore but it cannot start with a number or a special character.It cannot contain any space in the name.The name of the subroutine cannot be a keyword like Private, Sub, End, etc. Syntax Private Sub function_name( ByVal arg1 As String, ByVal arg2 As String) End Sub Syntax Explanation Code Action "Private Sub function_name(...)"Private is the keyword whic
Read More

G

Gautam goel

How to Run Code from a Module in Excel VBA

article_img
VBA Macro is for developers. In Excel, the macro is a piece of code written in VBA and VBA is Microsoft's programming language, it stands for Visual Basic for Applications. The module is a file with a .bcf extension that stores the code written in the Visual Basic for Applications editor. Let's learn, how to run a code from a module in Excel VBA. Run a code from the Module in Excel VBAStep 1: Go to the Developer Tab, Under the code section you will find Visual Basic. Now click on Visual Basic. Step 2: Microsoft Visual Basic for Applications(VBA) dialogue box appears. In the Menu bar, go to Insert Tab, and click on Module. Step 3: A Module named Module1 is created under the VBA project. Step 4: Write the code you want to run in Microsoft excel. For example, a message box will appear stating "Learning geeks for geeks". Step 5: In the Tools bar. Click on the run button. You can also press F4(Fn + f4) on your keyboard to run the module code. Step 6: The code pauses and the Microsoft VBA editor minimizes and a message box appears in the MS Excel worksheet. Click Ok. The code resumes and the Microsoft VBA editor maximizes again. Scope of Module in VBA The scope of the module in VBA
Read More

A

Ankur Pal

Debugging VBA Code in Excel

article_img
When we use VBA to write code in excel or when we any programming language to write code we may encounter errors that need to be analyzed, checked, and fixed. To overcome this problem most IDEs and programming languages support debugging. Debugging is the process of executing your programming codes step by step and checking which the steps currently compiler is executing, what are their values, and what is their output, we will use debugging. Alternatively, we can also execute each programming statement one by one and check the output, it is almost similar to debugging but it is not convenient and took a lot of time. To learn more in detail about debugging please refer to Software Engineering | Debugging. Debugging VBA Code In this example, we will use the following sub-procedure to understand the VBA debugging process. 'define a sub procedureSub fun() 'declare 3 variablesDim a, b, i As Integera = 0b = 0 'iteratingFor i = 1 To 5 a = a + 1b = b + 1 Next End Sub Now, we will run our VBA macro. For this Click On Run Button In Toolbar. Once, we run our VBA code, the entire program gets executed at once. Getting Started with Debugging To start with debugging process, we need to observe
Read More

A

Ankur Pal

Variables and Data Types in VBA Excel

article_img
In a computer system, variables and data types are almost used in every program to store and represent data. Similarly, Excel VBA also has variables and data types to store and represent data and its type. In this article, we will learn about VBA variables, their scope, data types, and much more. VBA Variables VBA(Visual Basic for Application) variables are similar to other programming languages variables, they act as a container that is used to store data(integer, string, floats, etc). We can use the variables in the code at multiple places and executer the programs. Defining Variables In VBA VBA gives permission to define variables in two ways: Implicitly - In VBA, we can implicitly declare variables using the assignment(=) operator. All the variables that are implicitly declared in VBA are of type "Variant". The variant type variables required more memory space than usual variables. Example: label="gfg"Explicitly - Explicitly we can declare variables using "Dim" keyword. Explicit variable also reduces the naming conflicts and spelling mistakes. Example: Num as password Syntax For VBA Variables // macro definition Sub VBA_Variable_Example () Dim <name> End Sub VBA V
Read More

R

RISHAB NIGAM

Function and Sub in Excel VBA

article_img
In Visual Basic, the functions and sub-procedures play similar roles but have different or unique characteristics. However, both perform a programmed task. They utilize a set or group of commands to deliver the required results. The key difference between the sub and the functions is that a sub-procedure generally does not return a result whereas functions tend to return a result. Hence if there is a need for having a value post execution of tasks, then place the VBA code under a function or otherwise place the code under a sub-procedure. In Excel, there is the availability of large numbers of VBA functions that could be utilized in the development of new VBA codes. Such functions are referred to as Built-in functions. With the increase in the size of a VBA program, both Functions and Sub-procedures play a crucial role in the management and performance of VBA code. Functions in VBA A function in VBA can be defined as a procedure that executes a piece of code or instructions and post-execution, it returns the value of the tasks performed. A function is hence invoked using a variable. Functions are directly called in the spreadsheets by using excel based formulas. An excel VBA functi
Read More

A

Ayush Dey

VBA Date and Time Functions in Excel

article_img
Date and Time Functions are the inbuilt functions that give us the opportunity to see the date or time according to the user's need. Suppose a user needs to see the month or the day or the year then it can be easily seen by different date functions. Similarly, for the time function, also we can manipulate it according to the need of the user. Date and Time functions are used to interconvert date and time in different formats. In this article, we will learn about the most commonly used date and time functions. VBA Date Functions There are fifteen-plus different date functions in VBA, but here we will talk about some of the most commonly used date functions. VBA Date Function The Date() function returns the current date. The Date() function does not require any arguments. For example, declare a variable name date_1 of Date data type, call the Date() function, and store the return value in date_1, then print the date_1 in the console. Syntax of the function: Date() VBA DateAdd Function The DateAdd() function is used to add an interval of date/time to the respective date or time. The function will return the resulting date or time. The function takes three arguments, Interval, Nu
Read More

A

Ayush Dey

How to Declare and Initialize String Array in Excel VBA?

article_img
A string array is an array where we can store only string values in the array, with the help of a string array, we can store more than one string value. We can declare the string array in many ways like declaring a static string array, declaring a variant size array of string using the Array function, and a string array using the split function which we will discuss in this article Declaring a Static String Array A static Array is an array whose size is fixed and it can be declared in two ways one is declared implicitly and another one is explicit. The following code is to declare a string array implicitly. The following code is to declare a string array explicitly. Declaring a Variant Size Array of String using Array Function In the following code an array is declared with variant size and string values are initialized using the array function: If we want to access the strings in the array then we have to write, Declaring a String Array using Split Function The following code is to declare an array without any fixed size and a split function is used to assign the string values. If we want to access the strings in the array then we have to write, By default, the lower bound
Read More

A

Asha Senthilkumar

How to Find the Last Used Row and Column in Excel VBA?

article_img
We use Range.SpecialCells() method in the below VBA Code to find and return details of last used row, column, and cell in a worksheet. Sample Data: Sample Data Syntax: expression.SpecialCells (Type, Value) Eg: To return the last used cell address in an activesheet. ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Address VBA Code: Declaring Variables: VariableData TypeCommentsLastRowLongFind and store last used rowLastColLongstore last used columnLastCellStringstore last used cell address'Variable Declaration Dim LastRow As Long, LastCol As Long, LastCell As String Use SpecialCells function to find last used row/column/cell 'Find Last Used Row LastRow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row 'Find Last Used Column LastCol = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column 'Find Last Used Cell LastCell = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Address Concatenate all three variables (LastRow/LastCol/LastCell), add a new line between variables use Chr(10). Show the final output in an Excel Message box. 'Display the last used row/column/cell MsgBox "Last Used Row : " & LastRow & Chr(10) & "Last Used Column :
Read More

S

SOUNETRA GHOSAL

How to Create Charts in Excel Using Worksheet Data and VBA?

article_img
Excel is an important software provided by Microsoft Corporation. This software belongs to one of the major software suites Office 365. In this software suite, there are other software are present like Word, PowerPoint, etc. They are called Office 365, as this software are mostly used for office purpose. But now the world has changed a lot. After the Corona Pandemic, the world knows the positivity of using digital tools. Office 365 was not different from that. As a part of the software suite, Excel software also gains some importance from the users. They are not only used for official purposes. But they can also be used for school purposes. Excel is software that can able to store data in an effective form. So, searching for the data becomes more manageable in this software. Excel has another great feature. It can be used to derive the charts from the provided data. The charts are helpful for analyzing any growth of the data. If there are thousands of data present, it is a difficult task to extract some analysis from that data. But if those data are converted to charts, then it will be easy to analyze those data. Excel sheet helps to do the same. Charts can be prepared whatever the
Read More

A

Ankur Pal

Workbook and Worksheet Object in Excel VBA

article_img
Excel VBA is an object-oriented programming language, which means in excel every component is an object. The superset of all the objects of excel is the excel application itself. The Excel Application Object is the root of all the other objects in excel. It contains Workbook Object. The Workbook Object contains another object such as Worksheet Object which includes all the worksheets present in a particular workbook. The Worksheet Object contains Cells Object. To perform any task or operation in excel we need to follow this excel object hierarchy. Workbooks Object In excel VBA, the workbooks object represents the collection of the workbook which are open in the excel application at the same time. This is the reason we write it as plural- Workbooks Object, which means it contains more than one workbook. In order to access a single workbook from the workbooks object, we can use the following VBA to access it from its name. How to Refer a Workbook in VBA? To refer a workbook in excel VBA, we can use the following different methods: By Name We can easily refer to a workbook using its name. We need to define a procedure in VBA and declare our excel workbook name. Make sure that the user
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox