Excel Macro Tutorial : Basics of VBA (Basic Syntaxes required while coding)

.

Let’s take a brief recap of all things you have studied so far in Excel Macro Tutorial.

So far you have learnt so many basic stuffs about Excel Macros under Excel Macro Tutorials. You have learned about all the Controls available in Excel Macros and also how to use them. You have also learned How to Record and Run a Macro in Excel. You also have learnt some basic Programming Lessons as well like If Else Statements, For Loop, While and DO While Loops etc.

So far we have studied all the basic stuff about Excel Macro, which will be required for you to start developing a tool in Excel Macro.
Here I have collected a Set of questions or doubts, which are asked as soon as you start coding your own.


1. How to read or Write some value in an Excel Cell?
2. How to read or write something in an Excel Cell of a Specified Sheet of the same Workbook.
3. How to Select a Worksheet from the Same Workbook?
4. How to Activate a Worksheet from the same Workbook?

1. How to put some value in an Excel Cell ?

This is the most frequently used stuff in excel macro. Reading and writing the data in excel cells. Here are the simple syntax to do so.

Cells(<Row Index> , <Column Index>).Value = <Your Value>
OR
Range(<Column Name><Row Index>).Value = <Your Value>
 

Steps to Do:

1. Open a New Workbook
2. Add a Command Button to WorkSheet (How to Add Command Button in Excel)
3. Double Click on the Command Button
4. Copy and Paste the Below Code in the Command Button Click Event


Private Sub CommandButton1_Click()
    Range("C3").Value = "Range C3"
    Cells(4, 3).Value = "Row No: 4  Column No: 3"
End Sub

 
Note: In the above you can see that no where Sheet Name is not mentioned. But as you know one Excel Workbook can have many Worksheets and in each worksheets the above Cell Address will exist. Since there is no Sheet Name mentioned, Code will put the value in the Same Sheet where your code or Your Command Button is there.
On Clicking on Button, Cell C3 and C4 will be populated with the Values. Refer the below diagram.

Enter Value in Cell

Enter Value in Cell

3. How to read or write some thing in a Excel Cell of a Specified Sheet of the same Workbook.

In the above example as you saw that Command button will put the value in specified cell in the same worksheet where Command button is located.
 
Let’s take an example, you want to put this vale in Same Cell Address but in a Sheet named “MyData”. It means when you click on Command Button, it should Sheet named “My Data” and put these values in that particular cell of that Sheet.

Follow the below Steps:

Under the Command Button Click Event Paste the below Code:

Private Sub CommandButton1_Click()
    Worksheets("My Data").Range("C3").Value = "Range C3"
    Worksheets("My Data").Cells(4, 3).Value = "Row No: 4  Column No: 3"
End Sub

 
Note: Above code will through error if the Sheet Name does not exist in that workbook.

3. How to Select a Worksheet from the Same Workbook ?

Below is the Syntax to select a Particular Worksheet from the Workbook.

Worksheets("Sheet Name").Select

4. How to Activate a Worksheet from the same Workbook ?

Below is the Syntax to Activate a Particular Worksheet from the Workbook.

Worksheets("Sheet Name").Activate
To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial
To Check out more Excel Tips and Tricks, visit Excel Tips and Tricks

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest