We have already covered how to format Excel Tables extensively in a previous tutorial. We are now going to look at using Table Nomenclature or structured Table references in formulas. Using structured Table References can simplify formulas, as well as make formulas more descriptive.
Additionally, we are also going to look at how to refer to Tables in VBA code.
So, let’s get started with a simple example to illustrate how to use Structured Table references.
Table of Contents
- Creating the Table, giving the Table a Descriptive name and using Structured References in an SUMIF Formula
- How to access the columns in a Table for use in a formula
- Creating a Calculated Table Column using Structured References
- Referring to Tables in VBA Code
- Clearing the fill of the Table using VBA
- Crossover Tip
- Download Working Files
- Read More…
The term beauty vloggers, refers to people who give makeup, hair and other beauty-related video tutorials on YouTube and other social media platforms. Many of these beauty vloggers have devoted fan bases and hundreds of thousands of subscribers. These video tutorials usually vary in length and can be up to 45 minutes or longer in length.
A hypothetical beauty vlogger who has just started a YouTube Channel has decided to evaluate her videos she has uploaded thus far – she uploaded all of the following on the same day. The source data is shown below.
Creating the Table, giving the Table a Descriptive name and using Structured References in an SUMIF Formula
1) First, things first let’s create a Table by clicking one cell in the range and going to Insert>Tables>Table or pressing CTRL-T.
2) The Create Table Dialog Box should appear and detect the data automatically, if it is setup correctly, select Ok and the Table is created.
3) Excel assigns a default name to the Table, in order to give the newly created table a more descriptive name, with a cell in the Table selected, go to Table Tools>Design>Properties and name the Table BeautyVlogT.
4) We want to add the length in minutes, of all the videos that are greater than 35 minutes long in the Table, in order to do this, we can use the SUMIF formula in combination with structured Table references.
5) So in cell H5, we input the following formula:
6) Upon pressing CTRL-ENTER we get a total of 334 as shown below.
How to access the columns in a Table for use in a formula
1) We can reference any column name of the Table in any formula we are creating, by doing the following.
2)We want to count only the Lips in the Part of Face column, so in cell H7, type =COUNTIF, as you are typing the name of the Table, Excel should pick it up automatically and list it as shown below.
3) With the Table name selected, press Tab and then square bracket, [ in order to access the Table column names.
4) Choose the Part of Face column from the options provided and then add a closing square bracket and then add the criteria, which in this case is “Lips” and close parenthesis.
5) Press CTRL-ENTER and the value of 3 is returned as shown below.
Creating a Calculated Table Column using Structured References
1) One can also create a new column and use structured references to populate the newly created column.
2) First things, first add a new column by selecting a cell, in the Likes Column and then right-clicking this cell and clicking on Insert Table Columns to the Right.
3) A new column is thus created.
4) Rename the column to Likes as a Proportion of Views.
5) We are now going to use structured references in order to create a calculated column.
6) So in cell G5, enter the following formula:
7) Upon pressing CTRL-ENTER, the whole column is populated, with the needed proportions as shown below.
Referring to Tables in VBA Code
1) One can also refer to the Table one created and named in the workbook, in your VBA Code.
2) We saved the initial workbook as a macro-enabled workbook. We are still using the same Table we created in the previous example called BeautyVlogT.
3) In order to see the full effect of the VBA code, we will need to clear the style from the Table first. With a cell in the Table selected, we do this by going to Table Tools>Design>Table Styles and clicking on the drop-down arrow next to Table Styles and choosing Clear as shown below.
4) Go to Developer>Controls>Insert and choose ActiveX Control>Button as shown below.
5) Draw a button on the worksheet as shown below.
6) With both Design Mode and the button selected, choose Properties.
7) Using the Properties Window change the name of the button to cmdFilltheTable, change the caption to Fill the Entire Table with Light Blue, change the font to Calibri and the font size to 8, and increase the size of the button by setting the width to 141.75 and the height to 32.25.
8) Close the Properties Window and right-click the newly created button and choose View Code.
9) For the button click event enter the following code.
Private Sub cmdFilltheTable_Click()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets(“VideoAnalysis”).Range(“BeautyVlogT”)
.Interior.Color = RGB(177, 245, 243)
.Font.Name = “Cambria”
.Font.Size = 10
In this case, we are using a Range object and referring to our Table when setting the range. One can use named ranges in the Range object, Table Names, cell references, and variables. When using named ranges already named in the workbook, Table names already named in the Workbook and cell references be sure to use quotation marks as shown in the case above where we are referring to our Table in the Range Object. We are then selecting the range specified which in this case is the Table, and using the With and End With statement filling it with light blue, setting the font to Cambria and the font size to 10.
10) Return back to the worksheet and click on the button (make sure Design Mode is not selected). The entire table is now filled with light blue fill, the font style is changed to Cambria and the size of the font is 10.
Clearing the fill of the Table using VBA
1) In order to clear the Table of the fill, and set the font back to Arial 10, programmatically, insert another button on the worksheet and using the Properties Window change the name of the button to cmdClearFill, change the caption to Clear the Blue Fill, change the font to Calibri and the font size to 8, and increase the size of the button by setting the width to 141.75 and the height to 32.25.
2) Close the Properties Window and right-click the button and select View Code and for the button click event insert the following code.
Private Sub cmdClearFill_Click()
.Interior.ColorIndex = 0
.Font.Name = “Arial”
.Font.Size = 10
In this case, we are using the ListObjects Collection, and referring to the Table in the Collection, we are again using the Table Name we created in the Worksheet itself, and in quotation marks. We are then selecting the range specified and using the With and End With statement filling the Selection with no fill, setting the font to Arial and the font size to 10.
3) Return back to the worksheet and click on the button (make sure Design Mode is not selected). The entire table is now not filled anymore, the font is changed to Arial and the size of the font is 10.
And there you have it.
One can also manipulate a Table in Word using VBA
1) Create a Table in Word, by going to Insert>Tables and click on the drop-down arrow underneath Tables and create a 5 column by 8 row table as shown below.
2) Insert a button by going to Developer>Controls and click on the drop-down next to Legacy Tools>ActiveX Controls and choose Command Button as shown below.
3) Place the button on your document and using the Properties Window name the button cmdChangeStyle, the caption to Change the Style of the Table, the height to 27, and the width to 191.95 as shown below.
4) Close the Properties Window.
5) Right-click the button and choose View Code.
6) For the button click event, enter the following code.
Private Sub cmdChangeStyle_Click()
ActiveDocument.Tables(1).Style = wdStyleTableMediumGrid3
In Word, a Table Object is a member of the Tables collection, and one uses the index number in order to refer to the Table. In this case this is the first (and only) Table in our document and therefore the Index number is 1.
7) Return to the document and making sure Design Mode is not selected, click the button and the style is changed.
8) In order to return the Table back to its default style programmatically, create another button below the first one and using the Properties Window, name the button cmdreturntoDefault, the caption to Change the Style Back to Default, the height to 27, and the width to 191.95 as shown below.
9) Right-click the button and select View Code and for the button click event, enter the following code.
Private Sub cmdreturntoDefault_Click()
ActiveDocument.Tables(1).Style = “Table Grid”
10) Return to the document and making sure Design Mode is not selected, click the button and the style is returned back to the default.
11) In order to prevent the controls from moving as you click on the different buttons, make sure Design Mode is selected and holding down the CTRL key, select both buttons.
12) Right-click and select Format Control.
13) In the Format Control Dialog Box, With the Layout Tab Selected, click on the Advanced…. Button.
14) In the Layout Dialog Box, with the Position Tab selected, uncheck Move object with text, in order to ensure that there is no movement as one clicks the buttons.
15) Click Ok and then Ok again and make sure Design Mode is not selected.
And there you have it.
Download Working Files
Structured Table references allow one to refer to columns in an Excel Table with ease. One can use these references in formulas. One can also refer to one’s named Table in VBA and manipulate certain properties using VBA. Word also allows one to refer to a Table in VBA code, however, the Word methodology for referring to a table is different in that it only allows one to reference the table using a collection object (Excel also allows one to reference tables through a collection object), and Word Tables are not specifically named.
Please feel free to comment and tell us if you use structured references in your worksheets or refer to Table Names in your VBA code.