Office tutorials

Basic VBA operations in Excel worksheet

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 discuss in detail the possibilities offered by the VBA in the Excel worksheet. If you are wondering how to write a program that will do certain 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 for you on the 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 things. In this chapter you will find out which ones we will use frequently. The code that we will write in this chapter should be entered into the module and included between the phrases Sub Name_program() and End Sub phrase.

 

Basic VBA activities with worksheet cells – cell addressing

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

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) 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 multiple cells.

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

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

You can also refer to a specific cell in a particular worksheet immediately. You can do this by combining the codes listed above:

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

In this way, you can download data to your VBA code and perform any actions on it. Then, you can insert the calculation results into any cell.

 

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

If you want to perform basic operations on Excel cells, you should first mark a range. In the second place you refer to the marked range with the Selection command and choose one of the commands copying, cutting, pasting or deleting data from your range, step by step.

Excel VBA course - Basic activities in Excel VBA worksheet

Excel VBA course – Basic activities in Excel VBA worksheet

 

How to save Excel file with VBA

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

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

Code to save the file in xlsx format, i.e. Excel folder:

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

 

Excercises (you can write the solution in the comment)

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

4.2 Improve the program listed above by copying and pasting the data, you entered into Sheet 2.

4.3 Improve your program by adding a 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.

2 comments

    • take down on June 17, 2018 at 1:57 am

    Reply

    Good post. I learn something new and challenging on sites I look everyday.
    It’s always helpful to read articles from other
    authors and use a little something from their websites.

    • 8be on June 17, 2018 at 5:20 am

    Reply

    There is certainly a lot to find out about this topic.
    I really like all of the points you have made.

Leave a Reply

Your email address will not be published.