Return to VBA Code Examples

VBA TypeName

This article will demonstrate the use of the VBA TypeName Function.

The VBA TypeName Function is used in determining the type of data stored in a cell, or the type of a selected object – for example a worksheet, range or cell, or a control on a form.

Determining the Data Type in a Cell

To determine was datatype in in a cell we can use the TypeName function with the Cells Property.

If we run this code above with the worksheet below, the message box will tell us what type of data is in the cell.

VBA Text TypeName Double

Determining the type of Object Selected

We can also use TypeName to determine what type of Object has been selected in a worksheet – a Range or a Chart for example.

VBA Text TypeName Range


Or, if we select a chart:

We can drill down even further and select the objects within the chart, and the macro will return what we have selected.

VBA Text TypeName Series

All of this can be most useful in building our VBA project to either control the flow of the code, or to prevent errors from occurring by testing to ensure the correct type of object is selected, or the correct type of data is entered into a cell.

Using TypeName on Form Controls

VBA enables us to create interactive forms that the user can fill in and return data to the code to be used in various ways.  We can use the TypeName operator to determine the type of controls that are being used on a form.

In the example below, I have created a user form with a variety of controls on it – a couple of Text Boxes, a Combo Box, 2 option buttons, 2 check boxes and 3 command buttons.

VBA Text TypeOf Form Intro

Using the code below, I can determine what type of controls are on the form by looping through all the controls on the form. I have used the TypeName function to return a message with the type of the control with a VBA IF Statement to check what type of control is selected.

This type of code can be very useful if we wish to enable or disable controls. In the code below, when the form is first opened, the option buttons and check boxes are disabled.

VBA Text TypeOf_Form

To enable the option buttons and checkboxes, I have written some further code behind the Enable Controls button.

The functionality in this code can also be created using the VBA TypeOf Operator.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as

Learn More!