Excel VBA Tutorial – #7 Properties of Objects
Last updated on December 10, 2018 By Victor Chan
Summary: 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
Download the Sample Workbook
Download the sample file with VBA code
Excel-VBA-Properties.xlsm (123 Kb)
#1 – What is a property?
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 – Using Constants
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:
Sub 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:
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:
Sub 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:
Sub 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)
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
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:
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.
Sub 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:
Sub 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:
Sub 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
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:
Sub 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.
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:
Sub 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:
Sub 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
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!
SheetsRename()
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:
Sub 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
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
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!
Related Posts:
Hey, I'm Victor Chan
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.