Power BI is a data analytics and visualization tool that hooks into a number of sources, including Salesforce. Salesforce has its own reporting and querying language, but sometimes you need to use Power BI to leverage and depict data in ways Salesforce cannot. To efficiently build reports in Power BI, a key concept to learn is query merging. This article has a Salesforce focus, but you can use this technique to merge tables from any source.
What is Query Merging?
Imagine a Salesforce instance containing Accounts and Opportunities. In the Salesforce data model, Opportunities lookup to Accounts. This means that one Account can have many Opportunities related to it. Because a lookup field in Salesforce holds the key for the record it looks up to, Salesforce allows you to query objects that are related using the lookup field.
For example, an Opportunity query can also include fields from the Account:
SELECT Id, Name, Account.Name from Opportunity
The above SOQL query can pull in the Name of the Account related to the Opportunity because there is a lookup field on the Opportunity called AccountId. Any field from the Account can be pulled in, as long as the user running the query has permissions to that field.
In Power BI, we use query merging to model the same behavior. Query merging allows you to pull in data from a related table into a source table. This way, you can report on data that spans multiple records.
How to Query Merge
Using the above example, let’s pull in the Salesforce Opportunity and then pull in the Account’s Name. To start, get your Opportunity table by selecting Get Data and then selecting Salesforce Objects:
Provide your credentials for the org you want to connect to and do not check the Include relationship columns checkbox:
Select the Opportunity table and select Transform Data:
Now, you are in the Power Query Editor window. Because we want to pull in Account Names, we need to get the Account table as well. Click on New Source and select the Account table:
With both tables pulled in, we can now merge our queries. The first thing to do is right-click on the Account table and deselect Enable Load:
This is important because when we pull the data for our report, we do not want to pull in both tables in their entirety. There may be Accounts that do not have Opportunities, so we only want to pull in Account’s that are related to Opportunities.
Select your Opportunities table and select Merge Queries from the toolbar:
From here, select the AccountId column and then select the Id column from the Account table:
This is how Power BI is told what table is related to what other table. Because there is a match on Ids between these two columns, we can now pull in data from the Account table to the Opportunity. In this case, I selected a Left Outer join, which only pulls in data from the second table that matches data in the first table. There are other types of joins that you can explore, but Left Outer is one of the most common joins for reporting use cases.
Select OK and your Opportunity table will have an extra Table column appended to it:
Pulling in the entire table is technically possible, but it has performance considerations since it will pull in all the data. for this use case, we only want the Name of the Account. To select the columns from the other table we want, click on the icon in the upper-right corner of the column:
This will open a window that allows you to select what columns you want to pull in from the Account:
I have only selected the Name column. Select OK will now update the Opportunity table and pull in the Account Name field:
Select Click & Apply in the upper-left corner and your Opportunity table will now have Account data merged into it! Here is a sample Matrix report using the Opportunity and Account Name data, showing which Accounts have which Opportunities:
With query merging, you can control what data you want to have in your report without sacrificing depth of data. Here is another article I wrote about filtering data before it is pulled from the source, which can also help improve your performance and give you more control over what data you bring into your report.
Leave a comment with any questions or thoughts! Happy reporting!
Filtering at the source always makes more sense, even in nature!
Power BI is a powerful data visualizer and analytics tool that can connect to a number of sources. Most business will eventually move to using a tool like Power BI if they’re getting serious about data analytics.
One great feature about Power BI is its out of the box connectors to different platforms. Power BI has a Salesforce Connector that can pull in standard and custom objects, so Salesforce data can be added to reports, dashboards, etc. One disadvantage to the Salesforce Connector is that is does not provide query folding in the GUI:
There is no option here to fold the query, unlike if this were a typical SQL Query
Hold on, what is query folding?
Query folding is an important concept when using Power BI. Query folding is when Power BI adds a filter to the query it makes to the data table before loading that data into Power BI. This is extremely useful when dealing with large data sets.
Imagine you had 5 million Account records in Salesforce. Pulling them all into report on them would take hours for Power BI, and most of the data is probably unnecessary for the analytics you are performing. Using the normal GUI shown above, Power BI will pull in all the Accounts!
Let’s say you wanted to only pull in Accounts that were of a certain Record Type or Account that were created after a certain data. To filter the Accounts before pulling them in, you need to specify the criteria you want to filter by and then let Power BI know that criteria through the Advanced Editor.
Using the Advanced Editor to Query Fold
Though the sample size is small, take a long at the following Accounts in my Salesforce instance:
Let’s pull these into Power BI. Click on Get Data and choose Salesforce Objects, then select Connect:
Select Production or Custom (if your org using a custom domain) and do not select Include relationship columns:
Choose the Account object and then select Transform Data:
This is where we can apply our query folding. The Accounts have not actually been pulled into the dataset yet. The right-hand panel shows the actions Power BI will take when it pulls the data into our dataset. Currently, the steps are Source and Navigation, which were automatically configured when selecting the data source and the table in the previous steps.
To query fold, click on Advanced Editor in the toolbar:
From here, you can add the criteria you want to fold by. Here is the original query:
For this example, I am going to query fold on Account Type being Customer – Channel:
Applying this criteria will then limit the rows shown in the Preview:
If you then select Close & Apply in the upper-left corner of the menu bar, the criteria will be applied to your dataset:
You folded a Salesforce query!
Assuming you apply the query after the Navigation step, the syntax for query folding is as follows:
#"[Name of Returned Rows]" = Table.SelectRows([Name of Table from the step above this one], each [Field Name you want to filter against] = [filter value])
You can add multiple filters as separate steps. You can also filter by date values using the following syntax:
#"Filtered Rows" = Table.SelectRows(Account1, each [CreatedDate] >= #date(2021, 10, 1))
With query folding, you can pull in just the data you really care about modeling, saving Power BI doing a lot of unnecessary calculating and you from a lot of waiting!
Formatting text is one of the most powerful ways you can improve the adoption and usability of your Excel files. Excel has robust text formatting and font capability and these are available in VBA.
Writing a macro that can insert text with certain text attributes and fonts or manipulate existing cells formatting can come handy for your more complex spreadsheets. This post will explore how you set the most common text formatting options and how you set fonts.
VBA Text Formatting Attributes
First, let’s create a simple macro that inserts some text into the first cell of a worksheet:
Sub FormattingMacro()
Worksheets("Sheet1").Cells(1, "A").Value = "This is some text"
End Sub
To make your font bold, modify your macro to:
Sub FormattingMacro()
Worksheets("Sheet1").Cells(1, "A").Value = "This is some text"
Worksheets("Sheet1").Cells(1, "A").Font.Bold = True
End Sub
To make your text italic, modify your macro to:
Sub FormattingMacro()
Worksheets("Sheet1").Cells(1, "A").Value = "This is some text"
Worksheets("Sheet1").Cells(1, "A").Font.Italic = True
End Sub
To change the color of the text, you need to use the RGB function. It takes in 3 numbers and returns the associated color:
Sub FormattingMacro()
Worksheets("Sheet1").Cells(1, "A").Value = "This is some text"
Worksheets("Sheet1").Cells(1, "A").Font.Color = RGB(255, 0, 0)
End Sub
To change text size:
Sub FormattingMacro()
Worksheets("Sheet1").Cells(1, "A").Value = "This is some text"
Worksheets("Sheet1").Cells(1, "A").Font.Size = 36
End Sub
VBA Font Formatting
To set the font of the text, use the following code and specify the name of the font you want:
Sub FormattingMacro()
Worksheets("Sheet1").Cells(1, "A").Value = "This is some text"
Worksheets("Sheet1").Cells(1, "A").Font.Name = "Times New Roman"
End Sub
Try combining these techniques with other VBA tricks to make something unique!
I’m sure there are a million articles on the internet about how to build a website.
I was always curious about how to build my own website, especially after being a professional developer for a few years. My professional work always had me developing and iterating over existing pieces of work or working within an established framework.
So I set out to answer my own question using the expertise I gained (and a lot of persistence!).
This post will go through the things I learned and how I ultimately was able to get a website up and running that has some really cool functionality. The steps I took were:
Thinking about what I wanted the website to do
Picking a web site host and buying a domain
Building the website using the tools provided by the host
Iteration!!!
The website build I will talk about is a website built using HTML/CSS, Javascript/jQuery, PHP, and SQL. These are not the most modern technologies on the market today, but they work extremely well for building a website for single-user interaction.
The Idea
I can’t help you think of what you want on your website, but you are only limited by your imagination. You can use this post to build your own blog, resume page, portfolio, or even a database that you can use to keep track of what you want (this is what I ended up building).
One caveat. If you’re looking to build a product that you plan to have used by more than one user at a time, this blog won’t be enough!
The Host
Once you have an idea of what you want to build, you’ll need to buy a domain and get a website host. The domain is the URL you want the website to be linked to and the host provides you with the infrastructure to get up and running.
I ended up going with Hostinger, a web host that was well priced for the features they offered. I was able to buy a domain and the package I bought came with databases to use and a few other features that came in handy during the building process.
The First Step
After I bought the domain and setup my account with the host, I looked at their tools and started from scratch:
I just wanted to go to a URL and have it show a message I created.
I’ll go through how I accomplished this simple, but important step, below.
Iteration, Iteration, Iteration
After I got my message on the screen, I was able to work as fast as I could figure things out. I was able to add interactions, colors, data, and link to a database! A full-fledged website was born!
Sounds simple, right?
Breaking Down A Website
Any website that you are building will have 4 major components:
The visuals, or front-end (HTML/CSS)
User interactions and the results of those actions (Javascript and jQuery)
The logic of displaying information and handling data (PHP)
The database to save persistent information (SQL)
Your website idea will dictate if you need all of these, but any website idea, from a portfolio page, blog, or application would need something like the above to function.
HTML/CSS
So, let’s start with the basics: how do we get a message to show up on our website?
Your webhost tools should have some sort of file management system, which is what your code is. The code that makes up your website is simply files of text you wrote in a file system that then represents the website.
For my case, when I logged into my Hostinger account, I saw a File Manager that looked like this:
I went into the public_html folder and created my html file:
I called my file ‘about.html’ and I put the text shown above in the file. When I went to my url/about.html:
That’s what I saw in my browser!
It may not seem like much, but I honestly get blown away every time something like the above happens. It’s really amazing that you can build something publicly accessible (or not, your choice!) and see it on your browser.
Let’s say you want a little pizzazz on your text. This is where the styling, or CSS, comes in. I want the text to be bigger and I want the text to be blue. I added the following to my about.html file and:
Once I had some content on the website, the next thing I wanted was a simple user-interaction. Say I clicked on the text ‘My Website.’ It would be nice to see something happen. This is where Javascript and jQuery come into play.
Javascript is a language that allows you to run code on the browser. This code can do any number of things, including manipulating elements on your website. The HTML written above is translated into elements:
jQuery is a language that allows you to do the same, but it also allows you to query the elements on your page. Querying elements gives you the ability to pick specific elements and interact with them.
Say I wanted to click on ‘My Website’ and show a message acknowledging it was clicked. First, I have to embed jQuery into my page:
You can checkout what jQuery is all about here. For our purposes, just know that jQuery allows us to select elements and manipulate them. I wanted to have something happen when I clicked on ‘My Website.’ With jQuery, I can grab the element by searching for tags, styles, or attributes, but I can also do the simplest case: search by id.
I gave the ‘My Website’ <p> tag an id and then wrote a little jQuery for when I click on it:
Before clicking
After clicking!
Pretty cool! The code updated out content based on our action. You can write almost anything you can think of using this structure. jQuery also works for many events, not just ‘click.’ Take a look at some of the other actions you can trigger events off of and combine it with Javascript and jQuery to build dynamic, sophisticated apps!
Database Calls
Now that you have a way to get content to show on the page and a way to have events, let’s try saving some data. In the previous step, our event fired on click to change some text on our website. Now, let’s use a button to keep a running count of how many times we have pressed it, regardless of if we left and came back to the website.
Every time we click this button, I want to update a counter that saves the number of times it was clicked. In Hostinger, I have access to a Database panel:
I am able to created a database hosted in the same location as my webpage. I created a username and password for my database and then I went to phpMyAdmin. This is the tool I used to configure the specifics of the database itself.
When using a database, the key things to consider are what columns you want, what type of data the columns hold, and if there are any unique properties associated to any of those columns, like being non-nullable or unique. For my purposes, I will only need a simple integer column to keep track of a count:
Now, we need to use PHP to connect to the database and get information from it in our front-end HTML code. A great thing about PHP is html an be embedded in it, and the PHP code will with it, but it will not show up on the front-end (it will simply run the logic to control what is displayed on the front-end).
To use PHP, I change my ‘about.html’ file to ‘about.php’ and then made a connection to the database:
Note that PHP can be broken up and mixed in with the HTML to render content dynamically. Notice how the while loop is iterating over the result of the SQL query and then rendering values in the HTML. If this query returned more than one row, it would render the value in the <p> tag for each row returned by the query.
We are going to store the counter value in a row in the database with a known ObjectId. I already created the row in this table, but you would insert a new row if there was no row in the table. I am them using PHP to get the value of the row, and then specifically get the value of the counter column:
Making A Database Call Through AJAX
Finally, I need to have the button update the database and return the information I want to display, the value of Counter. To do this, we need to make an AJAX call when the button is clicked. AJAX is a framework for making calls to a database asynchronously, meaning the call will happen and return a value without needing to wait specificaly for that call.
AJAX calls have some config and will point to a PHP file on the server, which will then make a database operation and return the information needed to the front-end. First, I set the structure of my AJAX call:
I add the following code to my script tag and I gave the <p> tag that displayed the counter message an id. Now, when clicked, the button will call a file called updateCounter.php and pass it some data, which I have placed for thoroughness. I am not passing anything back that is needed, but if you were, you could follow this format to pass values to the backend.
Then, updateCounter.php will return a result or an error. If a result is returned, the HTML of the <p> tag is replaced with the value of result. I will build the HTML in the backend if successful. If there is an error, the console will log it for debugging.
Now let’s create updateCounter.php on the backend:
In PHP, you join string with ., which took me a bit to get used to. You can see this on the last line where the $html variable is returned.
This code is getting value of counter from the row I stored it in, incrementing it if it is not null, an then returning an HTML string for the front-end to display. When I click on it:
Amazing! I the clicked the button 10 more times and revisited the page and:
It remembered!
With these techniques, you should be able to get started making a fairly complicated website!
D3 is an amazing data visualization library that can be used to make any number of interactive data applications. I was recently working with the network graph, which is a visualization of nodes connected by lines to other nodes. The nodes can be dragged around and moved and you can specify parameters during the simulation configuration to get an outcome you want visually.
Using the standard network graph example offered by D3, the nodes are simply colored and linked. It is very helpful to add other properties to the nodes so they become more useful to an end-user. This post will cover how to add text, images, and a little collapsible functionality to the nodes.
Append Text To A Node
Looking at the D3 network graph example above, the nodes have a title if you hover over them:
Hi Brujon!
But it would be nice if we could just see the node names on each node! Take this code and replace the code on the network graph page, wholesale, and click button in the upper-right:
Using the same pattern, you can now adjust anything you want about the labels using normal CSS techniques.
Append Image To A Node
Appending an image is done in the same vein as appending text. Use the following code snippet in place, or in conjunction with, the text code above to add an image:
            nodeEnter.append("image")             .attr("x", -40)             .attr("y", -40)             .attr("alt","test")             .attr("height", 30)             .attr("width", 30)             .attr("xlink:href", "https://www.pngitem.com/pimgs/m/55-559996_heart-icon-png-image-hollow-heart-transparent-png.png");
The above link is pointing to a heart icon. When we press play:
Pretty cool, right!? With this structure, you can do whatever you want with the nodes that are available to you in a force network graph. Try putting your D3 graph in a Salesforce web component and play around with some real data!
One of the coolest things about Excel is the ability to add Form Controls. Form Controls are interactive UI elements you have probably seen many times. They can be:
Command Buttons
Combo boxes
Checkboxes
Form Controls are great because they are intuitive ways to interact with a piece of software that everyone can understand. Implementing them in an Excel sheet can really open up the complexity options in your project.
But, how do you use them? Form Controls can bring a lot to your application, but they are a little more complicated to configure and use in VBA. In this blog post, I am going to go through each Form Control and explain how to configure it, interact with it, and use it in a piece of code.
Let’s get started!
Adding Form Controls To A Spreadsheet
To add Form Controls to a spreadsheet, you will need to enable the Developer section of the toolbar:
If you do not see this tab on your toolbar, navigate to File –> Options:
And go to the ‘Customize Ribbon’ section:
Add the Developer option to your Main Tabs and click ‘OK.’ You should then be set to add Form Controls.
Command Button Form Controls
The first option you have for Form Controls is the command button. This is one of the most basic controls found in any application.
It is simply a button that you click on to trigger some other action. You can also format the label on the button and give it a few different colors and sizes. To add a command button to a sheet, go to your ‘Developer’ tab and under ‘Insert,’ select the first option:
Once selected, your cursor will change into a crosshair. You can now click and drag a rectangle onto your sheet, and the rectangle will be the size of your button:
When you let go of your mouse, Excel will automatically open a window that will ask you if you want to link a macro to this button:
In this case, thus button was the 8th Form Control I added to this sheet, so it shows up automatically as ‘Button8.’ When you go further into the macro by clicking ‘New,’ you will see the following:
Sub Button8_Click()
End Sub
With this, you can now create an event for your button to trigger when its clicked. Let’s create a button that puts a hard-coded value into a cell:
Sub Button8_Click()
ActiveSheet.Cells(1, 1).Value = "Button was clicked!"
End Sub
After the macro ran
Pretty easy right!? You can change the macro that gets fired off of this button by right-clicking on it and selecting ‘Assign Macro,’ and then choose the macro you want.
Command Button in VBA
Having a button and a macro are great, but what if you wanted to access your button in a macro? Well, paste the following code below and you can paste the button text in a cell:
Sub Button8_Click()
ActiveSheet.Cells(1, 1).Value = ActiveSheet.Shapes("Button 8").OLEFormat.Object.Caption
End Sub
It’s important to note that there is a space between ‘Button’ and ‘8’ in the above macro. To access Form Controls on your sheet in VBA, you need to use the syntax:
The above syntax will then let you access a Form Controls properties and methods. For the command button, its properties and methods are listed here.
In this example, I am using the ‘Caption’ property to get the text on the button and paste it in a cell:
After the macro ran
Ta-da! You can build some really advanced logic on your spreadsheets with controls once you can access them in VBA.
Combo Box Form Controls
The combo box, also known as a drop-down list, is a box with an arrow that shows you options when clicked. Clicking one of the options select it as the controls value:
To add a combo box to your spreadsheet, click on the ‘Insert’ option under the ‘Developer’ ribbon and select the second option:
Now, when you select this option, your cursor will, again, turn into a crosshair. Draw the size of the rectangle you want the combo box to be and it will render on the page.
You’ll notice you are not prompted to assign the combo box to a macro once it’s rendered. Normally, the combo box is not designed to trigger a macro, but it can be. Before we get to that, let’s configure our combo box. Right-click on it and select ‘Format Control’ and navigate to the ‘Control’ tab:
The tabs before control allow allow you to configure the visuals of the combo box, but the Control tab is where you can configure its values and what cell you can see the selected value index in.
In some other cells on the sheet, type in the values you want in the combo box, each in a separate cell, and link them to your input:
Options for the combo box
Select a single cell for the ‘Cell link’ option, and that cell will get the index of the combo box value selected, and it will update every time to combo box is updated:
Cell to see the index of the value
Now, try out your combo box!
Selecting an optionOption selected
Pretty cool! You can also assign a macro to a combo box in the same way you can to a Command Button, and it will fire every time the value of the combo box is updated.
Combo Box In VBA
Having a combo box is great and being able to assign a macro to it is also helpful. But what if you wanted to put some more complex logic in the macro? Say you only wanted the macro to do something when a certain combo box value was selected. To get the value of a combo box in VBA:
Sub DropDown11_Change()
Dim index As Long
index = ActiveSheet.Shapes("Drop Down 11").OLEFormat.Object.ListIndex
If index = 2 Then
ActiveSheet.Cells(1, 4).Value = ActiveSheet.Shapes("Drop Down 11").OLEFormat.Object.List(index)
Else
ActiveSheet.Cells(1, 4).Value = ""
End If
End Sub
The above code will get the index of the combo box value when it changes. If that index is 2, we write the value of the list item in our cell. If not, it clears the cell out:
Not the option we want
When we select the second option:
The option we want!
And picking something else again:
Not the right option again….
As you can see, you can do a lot with VBA and Form Controls once you get the hang of it. The other methods and properties available for a combo box can be seen here.
Checkbox Form Controls
Everyone has used a checkbox when interacting with a web app. A checkbox give users a simple way to specify a ‘True’ or ‘False’ value:
To insert a check box on an Excel sheet, select the ‘Insert’ option under the ‘Developer’ tab and select the third option:
When you select it, your cursor will turn into a crosshair. Click on the sheet where you want your checkbox to render and it will appear:
For a checkbox, the most important configuration option is the ‘Cell link.’ You can specify a cell to hold the value of the checkbox as it changes:
Selecting our cell link to show the value
Specify the cell you want to hold the checkbox value and:
The cell value!
Pretty cool! You can also assign a macro to checkbox and it will fire upon change.
Checkbox In VBA
A checkbox is very handy, but in a macro, the most important part of it is getting the checkbox value. Say you wanted to fire a macro and check that checkbox value to build some interesting logic:
Sub CheckBox12_Click()
Dim checkboxvalue As Integer
checkboxvalue = ActiveSheet.Shapes("Check Box 12").OLEFormat.Object.Value
Debug.Print (checkboxvalue)
End Sub
If you run this macro, you can see in the VBA window that a value of True = 1 and a value of False = -4146. Why? I’m not sure, but you can use it for whatever logic you need!
The true and false values as I was checking and unchecking the box
You can see other properties and methods of the checkbox Form Control here.
Iterating Over Multiple Form Controls
Once you start using Form Controls in VBA, you can use the Shapes() method to get all the controls on a page and start iterating over them to see what;s on your sheet and how you can get their properties:
Sub ShapeNames()
For Each obj In ActiveSheet.Shapes()
Debug.Print (obj.OLEFormat.Object.Name)
Next
End Sub
Running the above code for my sheet, I get:
All my form controls
The above code can be very helpful in figuring out what’s on your sheet and how you can reference it. Try combining From Controls with some of the other VBAcode to make a dynamic, living application. Happy macro’ing!
Summing values is pretty simple in Excel with a spreadsheet and the SUM() function. You just set a cell to have a formula, ‘=SUM([Range]),’ and you can sum the values in any Range. But how can you the same in VBA?
It took me a bit of digging to figure this one one, surprisingly. I wanted to know if VBA had the same method, where you could specify a range and get the entire sum for the cell’s in that Range.
I was not having any luck at first, so I thought I was going to have to iterate over the Range, cell by cell, and sum all the values manually. But then I thought, “How can normal do this better than VBA?” Turns out, I finally found my answer in the form of Worksheet functions!
Worksheet functions are extremely powerful, and anyone familiar with Excel spreadsheets will know a lot o these functions once they see them. Let’s take a look at the SUM() function!
VBA Sum
Say you had a few numbers in some cells:
Before running the macro
Create a macro and paste the following code:
Sub VBASum()
Worksheets("Sheet1").Cells(1, 4).Value = WorksheetFunction.Sum(Range("A1:C1"))
End Sub
This will write the sum of the cells in the 4th cell of the first row. Run the macro and:
After running the macro
Voila! Just like the SUM() function in normal Excel. Simply pass in a Range as the parameter, and you can get your sum in one line.
Conditional statements are one of the most fundamental building blocks in any coding language, and Excel VBA conditionals are no different. VBA supports If, Else-If, and Case conditions so you can achieve whatever your application needs elegantly. Excel VBA If statements are a great place to start learning about coding principles, and this post will help you get started.
VBA If Condition
To implement an If condition, create a macro and paste in the following code:
Sub Conditionals()
If Worksheets("Sheet1").Cells(1, 1).Value = "" Then
Worksheets("Sheet1").Cells(1, 1).Value = "Not Null!"
End If
End Sub
Before running the macro
After running the macro
Pretty simple! The syntax for an If statement in Excel VBA is
If [condition] Then
[run the code if the condition is true]
End If
To check if a cell is blank, use two double quotes, to represent an empty string. If the cell value was a number, you would simply use the number value, without quotes, in your condition:
If Worksheets("Sheet1").Cells(1, 1).Value = 1 Then
Worksheets("Sheet1").Cells(1, 1).Value = "Not Null!"
End If
VBA Else-If Condition
Usually you are not checking only one condition. This case requires the use of the Else-If condition:
Sub Conditionals()
If Worksheets("Sheet1").Cells(1, 1).Value = 1 Then
Worksheets("Sheet1").Cells(1, 1).Value = "This was a 1"
ElseIf Worksheets("Sheet1").Cells(1, 1).Value = 2 Then
Worksheets("Sheet1").Cells(1, 1).Value = "This was a 2"
Else
Worksheets("Sheet1").Cells(1, 1).Value = "This was something else!"
End If
End Sub
When you have multiple conditions, and ‘ElseIf’ conditions need to be written as:
ElseIf [condition] Then
[code to run if condition is true]
Anything that is left as an ‘Else,’ the outcome when no other conditions are met, are written by:
Else
[code to run if other outcomes are not true]
EndIf
If an ‘ElseIf’ was your last statement in an ‘If’ condition, you would need to write the ‘End If’ after that line. ‘End If’ is used to denote the end of the ‘If.’
VBA Case Statements
A case statement is a slightly more elegant, robust way to do an Else-If statement. For a case statement, you can specify a number of conditions and the code will evaluate what case you current condition falls and under. It will then run the associated code with that condition:
Dim Number
Number = Worksheets("Sheet1").Cells(1,1).Value
Select Case Number
Case 1 To 2
Worksheets("Sheet1").Cells(1,1).Value = "This is between 1 and 2"
Case 3, 4
Worksheets("Sheet1").Cells(1,1).Value = "This is between 3 and 4"
Case > 4
Worksheets("Sheet1").Cells(1,1).Value = "This is greater than 4"
Case Else
Worksheets("Sheet1").Cells(1,1).Value = "N/A"
End Select
You can use Cases to mix and match different conditions. Cases are a bit easier to read then a large block of Else-If statements, so use a Case if you have a complicated case of conditional logic.
With conditionals, you can begin creating complex and sophisticated macros and applications.
Using VBA to loop through rows, cells and columns can transform the way you handle your spreadsheets. In this post, I’ll cover a few ways you can achieve this with some helpful examples.
The key to looping through any of these objects is the Range object. The Range object represents a cell, or a selection of cells. With this object and some VBA, we can loop through cells in any imaginable fashion
Loop Through Rows
To loop through rows, open your spreadsheet and create a macro. Then, paste in the following code:
Sub LoopThrough()
For Each Row In Range("A1:G5")
Row.RowHeight = 8
Next
End Sub
Running this does:
Before running macro
After running macro
As you can see, the macro went through each row specified in the range value A1:G5 and then adjusted the Row height for each of the rows.
This is done using the For Each… method in VBA. For Each allows you to specify a collection of objects and iterate over each of them. There are a few nuances to how it works and how you can iterate over Rows versus Columns. In the example above, Rows are simply the default behavior of iterating over a range, so no extra considerations is needed.
Loop Through Columns
To lop through columns, you will have to use the Column property of the Range you are iterating over and the Cell property of the Column:
Sub LoopThrough()
For Each Column In Range("A1:G5").Columns
For Each Cell In Column.Cells
Cell.Value = 1
Next
Next
End Sub
After running macro
Iterating over Columns is a bit trickier, but can be done with a few tweaks to our existing code.
Loop Through Cells
Looping through rows and columns can be useful, but you may ultimately be looking to loop through cells withing those structures. To loop through cells, you can use the same code structure as for Row and Columns, but within each Row and Column, you can iterate over the Cells in each of them:
Sub LoopThrough()
Dim CellValue As Integer
CellValue = 0
For Each Row In Range("A1:G5")
For Each Cell In Row
Cell.Value = CellValue
CellValue = CellValue + 1
Next
Next
End Sub
Using this code, we have the same Range, and we are going to go through each Row and then each Cell. We have defined the variable CellValue and each cell will get an incremental value of CellValue from the previous value:
After running the macro on Rows
If we switch this to go through Columns, instead of Rows, we get this:
Sub LoopThrough()
Dim CellValue As Integer
CellValue = 0
For Each Column In Range("A1:G5").Columns
For Each Cell In Column.Cells
Cell.Value = CellValue
CellValue = CellValue + 1
Next
Next
End Sub
After running the macro on Columns
Pretty cool! With these techniques, you can iterate over any structure you have in Excel.
Dynamic Ranges
One other interesting note about looping over Ranges is how you can set a dynamic value for the range. Say you wanted to have a macro that made cells fill in with values based on the value of two other cells (where you can specify the Range you want):
By entering values in ‘B1’ and ‘B2,’ you can make a table appear with values pre-filled. To do this, make a macro with this code and run it:
Sub LoopThrough()
Dim StringRange As String
StringRange = "B4:" + Worksheets("Sheet1").Cells(2, 2).Value + CStr(3 + Worksheets("Sheet1").Cells(1, 2).Value)
For Each Row In Range(StringRange)
For Each Cell In Row
Cell.Value = "Cell"
Next
Next
End Sub
By specifying a number of Rows and a letter for the columns to end and putting in a dummy value for the Cells, you can then have something like this:
Finding a value in a sheet can be very handy when using Excel VBA, within macros or sheet code. Luckily, VBA has a Find function that you can call on a Range, which can find values in the sheet. It also allows you to configure how the function will search for whatever you’re looking for. Let’s take a look at how Excel VBA Find works!
Say you had some values in a sheet and you wanted to write a macro that did something when it found a specific value. In the above sheet, I have a grid of numbers of different values. I am going to write a macro that looks for the number 9 and, if it is found, writes the word ‘found’ in a different cell:
Sub find()
Set c = Range("A1:E9").find(9)
If Not c Is Nothing Then:
ActiveSheet.Cells(10, 10).Value = "found"
End Sub
And when I ran this:
Pretty cool! A few things to note here:
You have to call find on a range, by writing Range.Find(). A range can be passed in or triggered off another event.
In this case, we only specified the value we wanted to find, but the Find function has many optional properties.
Find returns a range, so to check if it worked or not, you check if the return value is not Nothing. If so, you can then do whatever you wanted if the Find was successful
After allows you to specify the cell in the range you want the search to start after. This is helpful if you are looking to find and preform actions on items in a certain order or only within a certain subrange.
LookIn and LookAt take in Excel enumerated values. They allow you to specify whether to look in cells with comments, formulas, values, and whether to match against the whole cell value or a partial cell value.
SearchOrder and SearchDirection also take in enumerated values, and they allow you to specify the direction of the search and whether to search by rows or columns.
MatchCase, MatchByte and SearchFormat allow you to fine tune the search so cases and byte values match.
But why would any of this matter? Find is often used to programmatically go through a sheet and find values so things can be edited or highlighted. Let’s take a look at the same sheet and use the Find function again, with a tweak:
Sub find()
Set c = Range("A1:E9").find(9)
If Not c Is Nothing Then:
Debug.Print c
c.Value = "found"
End Sub
Run again two more times and:
Now, if we wanted to only have the cell be replaced with ‘found’ in a certain order, the other params would make more sense! Take this sheet:
Say I wanted find to start after cell D2, so A3 was found first:
Sub find()
Set c = Range("A1:E9").find(9, [D2])
If Not c Is Nothing Then:
Debug.Print c
c.Value = "found"
End Sub
Pretty cool right? The other parameters would work much in the same way, and you can fine-tune your Find function for whatever purpose you need. Now that you now how to use the Excel VBA Find method, see if you can learn more about Excel VBA!