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.

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:

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!

Javascript/jQuery

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:

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

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.

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

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:

<body>
    <p id="mywebsite" class="styling">
        My Website
    </p>
    <button id="counter">
        Click Me!
    </button>
    <script>
        $('#mywebsite').on('click', function(){
            $('body').html('I clicked here!')
        });
    </script>        
</body>
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:

<body>
    <?php 
        $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
    </p>
    <button id="counter">
        Click Me!
    </button>
    <script>
        $('#mywebsite').on('click', function(){
            $('body').html('I clicked here!')
        });
    </script>        
</body>

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:

<body>
    <?php 
        $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
    </p>

    <button id="counter">
        Click Me!
    </button>
    <?php
        $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 } ?>
    <script>
        $('#mywebsite').on('click', function(){
            $('body').html('I clicked here!')
        });
    </script>        
</body>

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:


    <script>
        $('#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){
                    console.log(error);
                }              
            });                
        })
    </script>        

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
<?php
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; 
 } 
 else{     
     $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)
.selectAll("line")
.data(links)
.join("line")
.attr("stroke-width", d => Math.sqrt(d.value));
const node = svg.append("g")
.attr("stroke", "#fff")
.attr("stroke-width", 1.5)
.selectAll("g")
.data(nodes)
.join("g")
.call(drag(simulation));
node.append("circle")
.attr("stroke", "white")
.attr("stroke-width", 1.5)
.attr("r", 5)
.attr("fill", color);
node.append("text")
.attr("x", 8)
.attr("y", "0.31em")
.attr("stroke", "green")
.text(d => d.id);
simulation.on("tick", () => {
link
.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:

node.append("text")
.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:

            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:

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

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)
Next
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!

VBA Sum

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

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

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

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!

Create an X (Close icon) – CSS Solution

Creating a Close icon, or creating an X, is trickier than you’d think. This post will show you how to create an X from scratch using only CSS. This way, you can use it for any project you have, irrespective of icon libraries, images, etc.

The icon we are going to recreate is the one you see on Google Chrome:

This icon is really useful and looks great! But sometimes you need it in a different size or color. With this CSS build, you can edit the icon to your liking (most of the attributes here are just giving the icon some spacing so it sits on the page):

.close-icon {
  position: sticky;
  right: 32px;
  top: 32px;
  width: 32px;
  height: 32px;
  margin-top: 10px;
}
.close-icon:before, .close-icon:after {
  position: absolute;
  left: 20px;
  content: ' ';
  height: 25px;
  width: 2px;
  background-color: black;
}
.close-icon:before {
  transform: rotate(45deg);
}
.close-icon:after {
  transform: rotate(-45deg);
}

You can see this in action here. With this CSS solution, you can edit anythig you want about the icon!

Say you want it to be larger. Adjust the ‘height’ of the before and after attributes:

big-close-icon

The above icon is with ‘height’ set to 100px;

If you want a thicker icon, adjust the ‘width’ of the before and after attributes:

thick-close-icon

The above icon is made with ‘width’ set to 5px.

You can also adjust the color of the icon by changing the ‘background-color’ of the before and after attributes:

red-close-icon

You can even change the rotation angles, so you can skew your X a bit if you change the angle of the transforms:

skewed-close-icon

Explore the other CSS properties and see what you can do with your icon. Creating an X icon this way really let’s you adjust whatever you need for the visual style you are trying to create.

LWC Table Pagination – With Example!

So far, we have covered how to make a table, sort a table, and search a table. One more feature we can implement to improve navigation is LWC table pagination. Pagination is when the table only displays a subset of records. Users navigate through the table using numbers, representing pages in the table, or arrows. Pagination is also used when too much data is being returned and needs to be split up, usually for performance reasons.

Implementing pagination can be done in two ways, which we will cover in this article:

  1. Returning all records and paginating the result.
  2. Calling events when the table is navigated through to get records in real-time, which are then displayed in paginated fashion.

Method 1 is an easy to implement, simple method that will make your data much more manageable to interact with. Method 2 is harder to implement, but necessary for really large sets of data. You can’t return and process hundreds of thousands of records to make a table!

LWC Table Pagination On All Returned Records

Let’s create a table of Accounts and render them on a page:

HTML:

<template>
    <div>
        <table class='slds-table'>
            <thead>
                <tr>                     
                    <th>
                        <a data-id="Id">
                            <div>Id</div>
                        </a>
                    </th>
                    <th>
                        <a data-id="Name">
                            <div>Name</div>
                        </a>
                    </th>                   
                </tr>
            </thead>
            <tbody>
                <template for:each={searchable} for:item="account">
                    <tr key={account.Id}>                
                        <td key={account.Id}>                                
                            {account.Id}
                        </td>
                        <td key={account.Name}>                                
                            {account.Name}
                        </td>                  
                    </tr>                                                                     
                </template>
            </tbody>
        </table>
    </div>
</template>

Javascript:

import { LightningElement, api, track } from 'lwc';
import getAccountApex from "@salesforce/apex/GetPeople.GetAccount";
export default class SortedTable extends LightningElement {

    @track Accounts;
    @track searchable;

    constructor(){
        super();

        getAccountApex().then(result => {        
            this.Accounts = result;
            this.searchable = this.Accounts;
        });
    }
}
lwc-no-pagination

This is a pretty simple table that is displaying 100 Account records. You can see pretty quickly that it is not the best way to show this much data. Ideally, you would want there to be pages, showing a set number of records at a time.

To implement pagination, lets put some buttons under the bottom-right side of the table (this code is pasted under the closing table tag):

        <div style="float: right;">
            <button style="margin-right: 10px;">
                <lightning-icon icon-name="utility:chevronleft"></lightning-icon>
            </button>
            <button>
                <lightning-icon icon-name="utility:chevronright"></lightning-icon>
            </button>
        </div>

The buttons won’t do anything yet, but let’s start by limiting the records to 15 per page:

    @track Accounts;
    @track searchable = [];
    pageLength = 15;
    page = 1;

    constructor(){
        super();
        getAccountApex().then(result => {        
            this.Accounts = result;
            for(let i = 0; i < this.pageLength; i++){
                this.searchable.push(this.Accounts[i]);
            }
        });
    }
pagination-on-all-records

Now, when you click on either of the buttons, we want the table to go to that page of results. Let’s put an onclick event on each button that displays the next or previous set of results:

HMTL

<div style="float: right;">
    <button style="margin-right: 10px;" onclick={prevpage}>
        <lightning-icon icon-name="utility:chevronleft" onclick={prevpage}></lightning-icon>
    </button>
    <button>
        <lightning-icon icon-name="utility:chevronright" onclick={nextpage}></lightning-icon>
    </button>
</div>

JS

    nextpage(){
        let results = [];
        if(this.page <= (Math.floor(this.Accounts.length/this.pageLength))){
            this.page = this.page + 1;
            for(let i = 0; i < this.pageLength; i++){
                if((i + (this.page * this.pageLength)) < this.Accounts.length){
                    results.push(this.Accounts[i + (this.page * this.pageLength)]);
                }
            }
            this.searchable = results;
        }
    }   

    prevpage(){
        let results = [];
        if(this.page >= 1){
            this.page = this.page - 1;
            for(let i = 0; i < this.pageLength; i++){
                if((i + (this.page * this.pageLength)) < this.Accounts.length){
                    results.push(this.Accounts[i + (this.page * this.pageLength)]);
                }            
            }
            this.searchable = results;
        }
    }

And you can see we can now navigate through the table! With this logic in place, we navigate to any page in the table regardless if it has the same amount of records as the pageLength.

pagination-all-records-end-of-table

By using a combination of page and pageLength, we can always figure out what index we need to start grabbing data from to render the next or previous page. With a few logic checks, the code can iterate over the necessary amount of records and display them. Also, by assigning pageLength to a variable, you can easily change the amount of records being displayed by simply changing the value of pageLength.

LWC Table Pagination By Making Server Calls

The other method of LWC table pagination is done by making a server call to get the data needed to display in the table when the next and previous buttons are clicked. This method is implemented when working with large sets of data that make querying the entire data set at once impractical.

We can use the above code and make a few changes to the nextpage() and prevpage() methods. These will need to be server calls instead that fire SOQL queries back to the database and return the necessary records only, which will then be rendered on the page:

    nextpage(){
            this.page = this.page + 1;
            getNextPageApex({pageLength: this.pageLength, page: this.page}).then(result => {  
                this.searchable = result;      
            });
    }   

    prevpage(){
        if(this.page >= 1){
            this.page = this.page - 1;
            getPrevPageApex({pageLength: this.pageLength, page: this.page}).then(result => {  
                this.searchable = result;      
            }); 
        }
    }

To make server-side calls, the methods need to be created on an Apex controller and imported into the Javascript file of your component:

import getNextPageApex from "@salesforce/apex/GetPeople.GetNextPage";
import getPrevPageApex from "@salesforce/apex/GetPeople.GetPrevPage";

The methods themselves are defined on the controller as:

    @AuraEnabled
    public static List<Account> GetNextPage(Integer pageLength, Integer page) {
        Integer offset = pageLength * page;
        List<Account> accounts = [Select Id, Name FROM Account LIMIT :pageLength OFFSET :offset];
        return accounts;
    }       

    @AuraEnabled
    public static List<Account> GetPrevPage(Integer pageLength, Integer page) {
        Integer offset = pageLength * page;
        List<Account> accounts = [Select Id, Name FROM Account LIMIT :pageLength OFFSET :offset];
        return accounts;
    }   

Now, when you click on the buttons, the table will cycle through the results!

pagination-on-server-call

Looking at the code, there’s a few things to note:

  1. The JS calls to the server pass parameters into the Apex methods using the syntax importedFuntionName({param1: value, param2: value})
  2. The name of the parameters is whatever the name of the value is on the server side
  3. The return of the call is handled using Promise syntax (then(result =>{})
  4. The server-side call uses the LIMIT and OFFSET properties of SOQL queries to get the necessary data, simplifying the data handling. LIMIT imposes a limit on the amount of records returned up to the value and OFFSET specifies an offset for the results returned from the query

Using either method, you can see the advantage of implementing LWC table pagination. It makes your data easier to handle for you and your end user.