Power BI and Salesforce Query Merging

power bi query merging

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:

power bi get data

Provide your credentials for the org you want to connect to and do not check the Include relationship columns checkbox:

power bi auth

Select the Opportunity table and select Transform Data:

power bi get table

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:

power bi get table2
power bi power query editor

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:

power bi disable 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:

power bi merge queries

From here, select the AccountId column and then select the Id column from the Account table:

power bi merge queries options

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:

power bi merged column

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:

power bi select merged columns

This will open a window that allows you to select what columns you want to pull in from the Account:

power bi select merged columns2

I have only selected the Name column. Select OK will now update the Opportunity table and pull in the Account Name field:

power bi merged column in source table

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:

power bi sample matrix report

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!

Power BI and Salesforce Query Folding (Filtering)

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!

VBA Text Formatting And Font Formatting

Text Formatting VBA
Text by any other format is not as nice

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
VBA Macro

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
VBA Macro Bold Text

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
VBA Macro Italic Text

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
VBA Macro Colored Text

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 Macro Text Size

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
VBA Macro Text Font

Try combining these techniques with other VBA tricks to make something unique!

How To Build A Website

Website Is Built!
Website done 🙂

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:

  1. Thinking about what I wanted the website to do
  2. Picking a web site host and buying a domain
  3. Building the website using the tools provided by the host
  4. 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:

  1. The visuals, or front-end (HTML/CSS)
  2. User interactions and the results of those actions (Javascript and jQuery)
  3. The logic of displaying information and handling data (PHP)
  4. 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.


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:

Hostinger File Manager

I went into the public_html folder and created my html file:

Website HTML

I called my file ‘about.html’ and I put the text shown above in the file. When I went to my url/about.html:

Website Displaying HMTL

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:

Website HTML With CSS
Website HTML and CSS

My website was on its way!!!

I added a CSS class to my HTML, in between the head tags and style tags. Then, I gave my text some <p> tags and gave them the class I created, ‘styling.’ You can read thousands and thousands of articles on CSS functions, features, and techniques, but now you can leverage them on your own website, in any way you see fit!


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:

Website In Chrome Inspector

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:

      color: blue;
      font-size: 72px;
  <script src="https://code.jquery.com/jquery-latest.min.js"></script>

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.

    <p id="mywebsite" class="styling">
        My Website
        $('#mywebsite').on('click', function(){
            $('body').html('I clicked here!')

I gave the ‘My Website’ <p> tag an id and then wrote a little jQuery for when I click on it:

jQuery On Click Before
Before clicking
jQuery On Click After
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.

First, I will place a button on the page:

    <p id="mywebsite" class="styling">
        My Website
    <button id="counter">
        Click Me!
        $('#mywebsite').on('click', function(){
            $('body').html('I clicked here!')
Website With Button

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:

Hostinger Database

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:

phpMyAdmn New Column

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:

        $servername = "localhost";
        $database = [database_name];
        $username = [database_username];
        $password = [database_password]; 

        $conn = mysqli_connect($servername, $username, $password, $database);

    <p id="mywebsite" class="styling">
        My Website
    <button id="counter">
        Click Me!
        $('#mywebsite').on('click', function(){
            $('body').html('I clicked here!')

PHP is written inline with your HTML and it is always enclosed in ‘<?php ?>’ tags.

Now that the connection is made, we ca also get the value of counter and render it:

        $servername = "localhost";
        $database = [database_name];
        $username = [database_username];
        $password = [database_password];  

        $conn = mysqli_connect($servername, $username, $password,  $database);

    <p id="mywebsite" class="styling">
        My Website

    <button id="counter">
        Click Me!
        $getCounter = "SELECT * FROM [database_name] WHERE ObjectId = 13 LIMIT 1";
        $counterResult = mysqli_query($conn, $getCounter);
        while($row = mysqli_fetch_array($counterResult)){ 
    <p>You have clicked this button<?php echo $row['Counter']?> times.</p>
    <?php } ?>
        $('#mywebsite').on('click', function(){
            $('body').html('I clicked here!')

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:

Website With PHP and SQL

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:

        $('#mywebsite').on('click', function(){
            $('body').html('I clicked here!')
        $('#counter').on('click', function(){
            var request = $.ajax({
                type: 'post',
                url: 'updateCounter.php',
                data: {
                    dataSentBack: 'I am data'
                success: function(result){
                    if(result!= ''){$('#counterstatus').html(result);}
                error: function (error){

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:

Hostinger Create New PHP File
if (isset($_POST)) {

 $servername = "localhost"; 
 $database = [database_name]; 
 $databaseusername = [databaseuser_name]; 
 $password = [database_password];
 $conn = mysqli_connect($servername, $databaseusername, $password, $database);
 $data =  $_POST['dataSentBack']; 

 $sqlrow = "SELECT * FROM [database_name] WHERE ObjectId = 13 LIMIT 1";
 $resultrow = mysqli_query($conn, $sqlrow); 
 $resultrow = mysqli_query($conn, $sqlrow); 
 while($rowcurrent = $resultrow->fetch_assoc()) {     
     $counter = $rowcurrent['Counter']; 

 if($counter == null){     
     $counter = 1; 
     $counter = $counter + 1; 

 $sqlrow = "UPDATE * [database_name] SET Counter = $counter";      
 $resultrow = mysqli_query($conn, $sqlrow); 
 $sqlrow = "UPDATE [database_name] SET Counter = $counter";      
 $resultrow = mysqli_query($conn, $sqlrow); 
 $html = 'You have clicked this button '.$counter.' times.'; echo $html;

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:

AJAX Before Click
AJAX After Click

Amazing! I the clicked the button 10 more times and revisited the page and:

Website With Database Persistence

It remembered!

With these techniques, you should be able to get started making a fairly complicated website!

D3 Network Graph Node Labels and Images

Many paths to take…

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:

D3 network graph
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:

chart = {
const links = data.links.map(d => Object.create(d));
const nodes = data.nodes.map(d => Object.create(d));
const simulation = d3.forceSimulation(nodes)
.force("link", d3.forceLink(links).id(d => d.id))
.force("charge", d3.forceManyBody())
.force("center", d3.forceCenter(width / 2, height / 2));
const svg = d3.create("svg")
.attr("viewBox", [0, 0, width, height]);
const link = svg.append("g")
.attr("stroke", "#999")
.attr("stroke-opacity", 0.6)
.attr("stroke-width", d => Math.sqrt(d.value));
const node = svg.append("g")
.attr("stroke", "#fff")
.attr("stroke-width", 1.5)
.attr("stroke", "white")
.attr("stroke-width", 1.5)
.attr("r", 5)
.attr("fill", color);
.attr("x", 8)
.attr("y", "0.31em")
.attr("stroke", "green")
.text(d => d.id);
simulation.on("tick", () => {
.attr("x1", d => d.source.x)
.attr("y1", d => d.source.y)
.attr("x2", d => d.target.x)
.attr("y2", d => d.target.y);
node.attr("transform", d => `translate(${d.x},${d.y})`);
invalidation.then(() => simulation.stop());
return svg.node();
You can run D3 simulations with tweaks with this framework. Very handy!

Now, when you press play:

D3 network graph nodes with text
A node by any other name…

Not bad! Using the attributes on the node.append(‘text’) section of our code, we can move the text placing around and change the color and size:

.attr("x", 8)
.attr("y", 0)
.attr("stroke", "blue")
.attr("font-weight", 100)
.attr("font-size", '9px')
.text(d => d.id);
D3 network graph nodes with text 2

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:

            .attr("x", -40)
            .attr("y", -40)
            .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:

D3 network graph odes with text and images

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!

Form Controls: How To Use Them In Excel And VBA

Excel Form Controls In VBA Banner
I wouldn’t touch those controls….

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:

Excel Developer Tab

If you do not see this tab on your toolbar, navigate to File –> Options:

Excel Options

And go to the ‘Customize Ribbon’ section:

Excel Customize Ribbon

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.

Excel Form Control Command Button

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:

Insert Excel Form Control Command Button

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:

Insert Excel Form Control Command Button 1

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:

Excel Form Control Command Button Macro

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
Excel Form Control Command Button On Sheet
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:

Activesheet.Shapes([Optional Object Name]).OLEFormat.Object

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:

Excel Form Control Command Button In VBA
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:

Excel Form Control Combo Box

To add a combo box to your spreadsheet, click on the ‘Insert’ option under the ‘Developer’ ribbon and select the second option:

Insert Excel Form Control Combo Box

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:

Excel Form Control Combo Box Configuration

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:

Excel Form Control Combo Box Options
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:

Excel Form Control Combo Box Cell Link
Cell to see the index of the value

Now, try out your combo box!

Excel Form Control Combo Box Selecting An Option
Selecting an option
Excel Form Control Combo Box Option Selected
Option 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)
        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:

Excel Form Control Combo Box In VBA
Not the option we want

When we select the second option:

Excel Form Control Combo Box In VBA Right Selection
The option we want!

And picking something else again:

Excel Form Control Combo Box In VBA Wrong Option
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:

Excel Form Control Checkbox

To insert a check box on an Excel sheet, select the ‘Insert’ option under the ‘Developer’ tab and select the third option:

Insert Excel Form Control Checkbox

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:

Excel Form Control Checkbox On Sheet

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:

Excel Form Control Checkbox Configuration
Selecting our cell link to show the value

Specify the cell you want to hold the checkbox value and:

Excel Form Control Checkbox Cell Link
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!

Excel Form Control Checkbox In VBA
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)
End Sub

Running the above code for my sheet, I get:

Excel Form Controls Iteration
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 VBA code to make a dynamic, living application. Happy macro’ing!

Excel VBA Sum Range Of Cells

Excel VBA Sum of Range Banner
The math checks out here…

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!


Say you had a few numbers in some cells:

Excel VBA Sum of Range 1
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:

Excel VBA Sum of Range 2
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.

Excel VBA If, Else-If, and Case Conditions

Excel VBA If Banner
Choices, choices….

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
Excel VBA If 1
Before running the macro
Excel VBA If 2
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"
        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:

    [code to run if other outcomes are not true]

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.

Excel VBA Loop Through Rows, Cells, or Columns

It’s kind of like looping through records!

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
End Sub

Running this does:

Excel VBA Rows Loop 1
Before running macro
Excel VBA Rows Loop 2
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
End Sub
Excel VBA Columns Loop 1
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
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:

Excel VBA Cells Loop 1
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
End Sub
Excel VBA Cells Loop 2
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):

Excel VBA Dynamic Range 1

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"
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:

Excel VBA Dynamic Range 2

How To Use Excel VBA Find, With Example!

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!

Excel sheet to use Find on

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:

Excel sheet Find example 1

Pretty cool! A few things to note here:

  1. You have to call find on a range, by writing Range.Find(). A range can be passed in or triggered off another event.
  2. In this case, we only specified the value we wanted to find, but the Find function has many optional properties.
  3. 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

What else can Find do?

The official Excel help docs show the syntax of Find to be the following:

expression.Find (WhatAfterLookInLookAtSearchOrderSearchDirectionMatchCaseMatchByteSearchFormat)

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.

MatchCaseMatchByte 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
Excel Find example 2

Run again two more times and:

Excel find example 3

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:

Excel find example 4

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
Excel find example 5

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!