How to use Cells, Ranges & Other Objects in Excel VBA
In part 3 of our VBA Crash Course, we are going to learn how to speak with various Excel objects like Cells, Ranges etc. and deal with them.
Objects – what are they?
Any thing and everything is an object. Your dog, your bed, your neighbors cat, their car, your bike, your computer, the shiny new Excel workbook you just created, my website, your email account – every thing is an object.
From our “We are nuts” example yesterday, you can already see these objects:
One awesome owner (that is you)
24 store manager objects
24 store objects
Understanding Variables, Conditions & Loops in VBA [Part 2 of 5]
In part 2 of our VBA Crash Course, we are going to learn what Variables, Conditions & Loops are and how to use them in Excel VBA.
What are Variables, Conditions & Loops?
If you are new to computer programming, you might think I am speaking legalese. So, to make it easy to understand, lets assume you run a bunch of stores across the town. To make it colorful, lets call your stores “We are nuts” – a dry fruit and nuts store chain. At the end of every day, you call each of the 24 store managers and ask them how much sales they have made in that day.
Now, you are not the kind of boss who micro-manages & nitpicks. So you don’t really note down sale for every store. Instead, as you call the store manager, you just mentally update the total. So first store says “$2,300” your total is 2300. Second manger says “$4,000”, the total now will be 6300. So on.
The value 6300 here is nothing but a variable.
We will use the “We are nuts” example thru out this lesson & towards the end we will build a sales logging application using Excel VBA.
What is VBA & Writing your First VBA Macro in Excel [VBA Crash Course Part 1 of 5]
This article is part of our VBA Crash Course. Please read the rest of the articles in this series by clicking below links.
- What is VBA & Writing your First VBA Macro in Excel
- Understanding Variables, Conditions & Loops in VBA
- Using Cells, Ranges & Other Objects in your Macros
- Putting it all together – Your First VBA Application using Excel
- My Top 10 Tips for Mastering VBA & Excel Macros
Excel School Prices Going up from 29th August, Join Now to save!
Hello lovelies,
We break the regular awesomeness on Excel & related beauty to bring you an important notice.
You may know that I run an online Excel & Dashboard training program, called as Excel School. We have been running this course since Jan 2009 and trained more than 1,500 students thru this so far. And starting next Monday (29 August), the course prices are going up.
So, if you are waiting to become awesome in Excel, now is your chance.
Simple KPI Dashboard using Excel
Any Tom, Dick and Sally can make things complex. It takes guts and clarity to simplify things. That is why I was pleasantly surprised to see this dashboard prepared by Michigan State. You can see it in the detailed article.
Linda, one of My Excel School students shared this dashboard link with me and asked if I can show how to construct something like this. Here is my version of the dashboard.
Read this post to learn how to construct a similar dashboard on your own. Also, you can download the excel workbook and play with it.
Split Text on New Line using Excel & VBA [Macros]
Hafiz, One of our avid readers, writes in. My problem is when I convert data from text to column using dash “-“, conversion is easy. but when the gap provided in text is with “alt+enter”, I can’t convert the data.
Well, I tried to use text to columns feature (from Data ribbon) and it would not work.
Although you can use formulas to do the splitting, they might become tedious. So the next logical option is to use macros.
In this article, learn how to use VBA to split text on New Line characters
Custom Chart Axis Formating – Part 2.
Last week I introduced the technique of using custom Number Formats for Chart Axis labels.
Today I present a few more alternative layouts including Chart Series Data Label Formats.