Basic VBA operations in Excel worksheet

Last Updated on January 11, 2023 by token

1. How to use VBA in Excel worksheet

It’s great that you’ve already reached this part of the VBA Excel training. In this chapter we will learn in detail the possibilities offered by the VBA in the Excel worksheet. If you are wondering how to write a program that will do some actions for you in the worksheet, the easiest way to do it is to register a macro program. After you have registered macro, you can see the code that will do exactly the same steps for you in the Excel sheet. In this article you will learn how to do VBA operations in Excel worksheet.

The VBA in Excel contains thousands of different possible ways of doing same things. In this chapter you will find out which one you will use frequently. The code that you will write in this chapter should be entered into the module and included between the phrases Sub Program_name() and End Sub phrase.

2. Basic VBA activities with worksheet cells – cell addressing

If you want to do actions on a data sheet other than the one you are currently on, activate sheet with the following code, indicating the name in quotation marks – in this case Sheet 1:

'OfficeInside.Org
Sheets("Sheet1").Select

If you want to enter any value into cell of your worksheet, you should first address a cell. You can address cell using the Cells(cell_numbers) or Range(range) command. With the Cells command you can address any cell. With the Range command you can address a single cell or a range of cells.

Ok, you are already on a specific sheet. To do the simplest operation on a single cell, use the example code written below:

'OfficeInside.Org
Range("A1") = "Welcome to Excel VBA"

You can also address a single cell in your worksheet by using the Cells command to specify a row and column using a numeric index. For example, Cells(2, 2) will refer to cell “B2”.

'OfficeInside.Org
Cells(2,2) = "Welcome to Excel VBA"

You can also refer to a specific cell in a worksheet immediately. You can do this by combining codes listed above. Use dot between sheet address and cell address:

'OfficeInside.Org
Sheets("Sheet 1").Range("D23") = "This is VBA in Excel"

'OfficeInside.Org
Sheets("Sheet 1").Cells(2,2) = "This is also VBA in Excel"

The example listed above fills cells. But what if you want to get data from cells and assign it to a variable? If you already have a defined variable, you  can use this code to insert value into variable.

'OfficeInside.Org
variable_name = Sheets("Sheet1").Range("D23")

'OfficeInside.Org
variable_name = Sheets("Sheet1").Cells(2,2)

In this way, you can put data into your VBA program and do any actions on it. Then, you can insert the calculation results into any cell.

3. VBA – Manipulating data in cells – copy, paste, cut, delete

If you want to do basic operations in Excel cells, you should first select a range. In the second you refer to the range using Selection command and choose one of the commands:

• copy,
• cut,
• paste
• or delete data from selected range

'OfficeInside.Org
Range("A1:B10").Select
Selection.Copy 'copy command

Range ('A1:B10').Select
Selection.Cut 'Cut command

Range ('A1:B10').Select
ActiveSheet.Paste 'Paste command

Range ('A1:B10').Select
Selection.ClearContents 'Delete

4. How to save Excel file using VBA

If you want to save your Excel file using VBA, the easiest way to do it is refer to the active workbook and save it using the Save command, ie.:

'OfficeInside.Org
ActiveWorkbook.Save

However, if you want to save your file to specific location, use the SaveAs command and enter the path for saving file.

Code to save file in xlsx format. This is simple MsExcel file:

'OfficeInside.Org
ActiveWorkbook.SaveAs Filename:="[path e.g. C:\FILE_PATH_AND_NAME].xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Code to save the file in xlsm format, i.e. Excel folder with macro support:

'OfficeInside.Org 
ActiveWorkbook.SaveAs Filename:="[path e.g. C:\FILE_PATH_AND_NAME].xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

5. Excercises (you can write the solution in the comment)

5.1 Write a simple program which you can use to enter your name and city of residence into any cell of Sheet1.

5.2 Improve the program written above by copying and pasting the data, you entered into Sheet2.

5.3 Improve your program by adding code to save the Excel file to your local drive.

More examples of Excel VBA basics can be found in the Excel VBA Course section and  Excel VBA Examples section. If you have any questions about this chapter, you can post them on the forum without logging in.