Excel VBA Tutorial – #7 Properties of Objects

[IMAGE] Color-palette-Excel-VBA-propertiesSummary:  In the previous tutorial (#6 Collections) we got familiar with the idea of Excel VBA objects that are like parts of a house – but how can we use them to automate our processes?

What if we want to get the address of the active cell? Or change the value to 1000? Or change the cell background color to red or green or blue? To do this we need properties, and I will give you tips on how to use them to make your life easier. Read on!

Difficulty: Beginner

«« Previous … | … Next (coming soon) »»

Download the Sample Workbook

[Image] Download Workbook Download the sample file with VBA code
Excel-VBA-Properties.xlsm (123 Kb)

#1 – What is a property?

» Back to contents

You can think of properties as adjectives in the English language that describe a noun.

Analogy: “Properties are like Adjectives

Properties describe an object in Excel. They can tell you the value of a cell, or what color the cell background has, as well as many other things.

Example: “This cell has a RED background”
Example: “This text is BOLD and BLUE

Getting more technical, properties are named attributes of an object. As you already know, VBA is an object-oriented programming language.

Excel Objects have multiple attributes – some can be modified, some can only be read.

The main feature of the properties we use in VBA is that they can be written, not just read. I will show you the most valuable commands, which can get you the most out of knowing VBA object properties!

You can identify properties because they are written after objects following a dot:

Example: “ActiveCell.Address
Here, “ActiveCell” is the object, followed by a “dot”, followed by the property “Address”

#2 – Cell Address, Row and Column

» Back to contents

Let’s start with a simple property – the cell’s address. This is a read-only property, so it is only used to get the required information out of it. As its name suggests, it shows us the address of the specified cell object:

[IMAGE] 2-activecell-addressSub CellAddress()
    MsgBox ActiveCell.Address(0, 0)
End Sub

As you can see, the code displays the address of the active cell in a message box.

Why is there (0, 0) at the end of the address property? Because it makes the address more readable. If you delete it, you get the absolute row and column values with “$” signs:

[IMAGE] 3-activecell-address-absolute

So, the zeros in quotes are disabling the absolute reference.

Sometimes you don’t need the whole address of a cell, maybe you just want to use its row number:

[IMAGE] 4-row-numberSub CellRow()
    MsgBox ActiveCell.Row
End Sub

This is important! We used this property to determine the last row of the dataset in article #5 as well. The macro we used there is based on this property of the cells.

Reading out the column of the active cell is not so often needed, but who knows? Maybe this is what will help you with a special task! So, here is the command for it in another minimalist macro:

[IMAGE] 5-column-numberSub CellColumn()
    MsgBox Split(ActiveCell.Column, "$")(1)
End Sub

As you can see, the result is the number of the column starting from col. A. This can be confusing, when you would need a letter instead.

In this case, you can of course get the letter of it as well, but the code needs then a little fine-tuning.

Here I created a User Defined Function called “Col_Letter” that gives us the letter corresponding to any column:

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

This is the output when we use the User Defined Function (column numbers are in cells J2:J7, formulas are in cells K2:K7)

[IMAGE] 6-udf-col-letter

Be aware, that this is no longer a beginner macro 🙂…

Functions are another type of program in VBA. They are very similar to subroutines – the difference is that functions always return a value, while subroutines cannot do that.

I’ll just leave this here for fun, and to make sure that you are learning more about the VBA language – on the road from beginner to professional coder.

You can play with the function “Col_Letter” in the downloadable Excel file.

#3 – Cells.Value

» Back to contents

A cell’s value is one of the most often used property in Excel programming. The reason is simple, because we work with values which are contained in cells of the sheet.

Cells.Value is a readable and writeable property. Let’s start with reading it!

Reading Cells.Value

My macro is as follows… it displays the active cell’s value in a Message Box:

[IMAGE] 7-activecell-value
Sub ReadActiveCellsValue()
    MsgBox ActiveCell.Value
End Sub

Of course, you can read out any cells value, if you specify its address. The following code gives the value in Cell C10 – we specified it as the 10th row, 3rd column.

[IMAGE] 8-cell-valueSub ReadAddressedCellsValue()
    MsgBox Cells(10, 3).Value
End Sub

 

When we get there in due course, you’ll be able to define addresses with variables instead of just fixed numbers like 10 and 3. That is one big milestone in learning programming! 🙂

 

Writing in Cells.Value

Reading a cell’s value is useful for searching, comparing and checking values. But when you want to write something in cells, you can’t settle with that.

Let’s find out how to write values to cells. So you can store information, make your own dataset, and other tasks that require you to change the value property of the cell!

The writing itself is easy – you just tell the cell that it has a value. If the cell had a value already, then it will be overwritten, and the previous value will be lost:

[IMAGE] 9-write-cell-valueSub WriteCellsValue()
    Cells(1, 1).Value = 3
End Sub

This macro changes cell A1 value to “3”.

The sheet has an exponentiation where Cells(A1) is the base, this macro affects the exponentiation results as well. The previous value of “2” got overwritten, and we lost the previous results.

So, while giving a value to a cells property, always make sure that the cell is either empty, or the existing value isn’t needed anymore, and it can be overwritten.

You can give strings to a cell’s value too, just put the information in between double quotes:

[IMAGE] 10-write-cell-stringSub WriteCellsValueString()
    Cells(2, 1).Value = "base"
End Sub

Now you know how to read a cell’s value property, and how to write into it. This is one of the most, if not the most useful property of the cells in Excel VBA, because we work this almost all the time.

⚡ BIG IDEA… notice that we changed the value property of cells without selecting the cells at all! We are getting more professional. With this method your code becomes cleaner and faster – because you don’t need to select cells to read or write to them. Make this a habit when you write VBA code!

#4 – Format Properties

» Back to contents

Cells have other properties, including the interior color and font formats. These might not be as essential for programming as the value property, but they can be useful to highlight cells and improve the look of your spreadsheet. Let’s look at three format properties you can use for cells.

 

Cells.Interior.ColorIndex

This is what you would call “Fill” in everyday use. This changes the background color to your desired color. Let’s highlight Cell A2 in red:

[IMAGE] 11-change-cell-color-redSub CellsInteriorColor()
    Cells(2, 1).Interior.ColorIndex = 3
End Sub

As you can see, I typed in the number 3 to set Interior.Colorindex, and it resulted in changing the background color of the cell to red.

This happened because Excel has a built-in list of colors and color indexes, which we can use in our code.

Another way to apply colors is using the VB color name (but the color choice is limited to 8 colors). So we could write “Cells(1, 1).Interior.Color = vbRed” to set the background to red.

Here’s a picture that shows the available VB colors.

[IMAGE] 12-vb-colors-index

And here is VBA code to generate that color list (don’t worry about the bits you don’t understand… you’ll learn more in other tutorials):

 Sub Change_Cell_Colors()
  
  Dim Start_Rng As Range
  
  'Get Start Cell address
  Set Start_Rng = Application.InputBox(prompt:="Select starting cell", Type:=8)
  
  With Start_Rng
    .Resize(10, 2).Clear        'Clear range
    .Value = "VB colors list"   'Write header
    .Font.Bold = True           'Make bold
  End With
  
  'Write VB color values
  With Start_Rng
    .Offset(2, 0).Value = "vbBlack"
    .Offset(3, 0).Value = "vbBlue"
    .Offset(4, 0).Value = "vbCyan"
    .Offset(5, 0).Value = "vbGreen"
    .Offset(6, 0).Value = "vbMagenta"
    .Offset(7, 0).Value = "vbRed"
    .Offset(8, 0).Value = "vbWhite"
    .Offset(9, 0).Value = "vbYellow"
  End With
  
  Start_Rng.Columns.AutoFit
  
  'Apply VB colors to cells
  With Start_Rng
    .Offset(2, 1).Interior.Color = vbBlack
    .Offset(3, 1).Interior.Color = vbBlue
    .Offset(4, 1).Interior.Color = vbCyan
    .Offset(5, 1).Interior.Color = vbGreen
    .Offset(6, 1).Interior.Color = vbMagenta
    .Offset(7, 1).Interior.Color = vbRed
    .Offset(8, 1).Interior.Color = vbWhite
    .Offset(9, 1).Interior.Color = vbYellow
  End With

End Sub

If you want to know more about different ways to apply color, you can visit this page:

http://access-excel.tips/excel-vba-color-code-list/

Here you’ll find three color lists: color indexes, RGB colors, and VB colors. (If you visit that link, remember to come back here to keep learning more about properties!)

 

Cells.Font.ColorIndex

If you want to change the cells font color, and not the background, you should try this macro:

[IMAGE] 13-cell-font-colorSub CellsFontColor()
    Cells(2, 1).Font.ColorIndex = 2
End Sub

This results in changing the Font’s ColorIndex property to “2”, which is white.

 

Cells.Font formatting

You can also change font formatting in a macro, just like you can do using the Excel toolbar – for example change the font to bold, and change its size:

[IMAGE] 14-cell-bold-size-12Sub CellsFontBoldSize()
    Cells(1, 1).Font.Bold = True
    Cells(1, 1).Font.Size = 12
End Sub

The result is self-explanatory – you get cell A1 font in bold and size 12.

💡 Idea: You can see that font type and size are properties of a cell, which can be modified by your programmed macro. You can play around with the Excel toolbar and record your actions with the Macro Recorder to find out more properties.

⏩ For instance try changing font to Calibri or Arial, try underlining text, try changing the number format to accounting or general. This is a quick and handy way to explore the properties of Excel Objects.

#5 – Sheets Properties

» Back to contents

Now you know a lot more about cells properties. But that isn’t enough if you want to automate your spreadsheet. Let’s find out about some sheets properties, because they are important too!

Sheets.Name

The easiest example is sheet name. Yes, the name of the sheet is a property all on its own. It can be read and written, just like a cell’s value!

[IMAGE] 15-sheet-renamedSheetsRename()
    Sheets("Sheet1").Name = "Changed"
End Sub

With the above code we changed the name of “Sheet1” to “Changed”.

You don’t always have to write the name of the sheet you want to change in double quotes – you can also write the index number of the sheet as well (Sheets(1).Name)

But in this case, if there are more sheets in the workbook, and the order of the sheets get changed, the macro would rename the wrong sheet.

To avoid this error I always define the sheet with its full name.

💎Bonus: for more info on worksheet names, index numbers and secret VBA code names check out this article “Unlock the Secrets of Worksheet Code Names”)

 

Sheets.Count

Sheets can also be counted. The count of the sheets is also a property, which Excel knows, and you can ask for it in VBA code:

[IMAGE] 16-sheet-countSub SheetsCount()
    MsgBox Sheets.Count
End Sub

The Sheets.Count property will come in handy later, when we learn about loops to do programmed actions on multiple sheets in turn.

For example if you want to run the same command on every sheet – say you have 152 sheets (so many you don’t want to count them manually) – you can just write code to start from sheet 1 and keep going until the last sheet. The last sheet has an index number equal to Sheets.Count.

#6 – Summary

» Back to contents

We looked at several important and often used properties of VBA objects (cell address, cell value, row, column, color, font formatting, sheet name and sheet count). If you want to automate Excel tasks, you cannot do without properties – and of course, there are many more properties we did not look at.

💡 Idea: You can play around with recording your actions with the Macro Recorder to find out more properties … for instance try changing font to Calibri or Arial, try underlining text, try changing the number format to accounting or general. This is a quick and handy way to explore the properties of Excel Objects.

#7 – About the Author

» Back to contents
[IMAGE] Daniel Lajosbanyai

Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)

To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.

Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!

Victor Chan
Victor runs Launch Excel to help you master Excel and VBA. He has used Excel a lot since 2002. He's a Chartered Accountant (Fellow of the ICAEW) with a Masters in Manufacturing Engineering from the University of Cambridge... and has a passion for teaching others. Enjoy his tutorials!
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recent Posts

Leave a Comment

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

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.