VBA & Macro Problems

How to Display a Cell's Font Size in Excel Using VBA

Steve K Oct 20, 2026 868 views

Question details

The user wants to find out how to extract and display the font size of a specific cell in Excel using a custom VBA formula.

How to Display a Cell's Font Size in Excel Using VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a custom formula to read formatting properties from a target cell so the font size can be displayed as a value in another cell.
Observed behavior: A custom VBA User-Defined Function (UDF) must be written to retrieve the font size property and output the numeric value into the spreadsheet.

Before you start

Ensure the Developer tab is enabled in your Excel ribbon, as you will need it to access the Visual Basic Editor. Remember that workbooks containing VBA code must be saved as Excel Macro-Enabled Workbooks (.xlsm).

Solution 1Recommended

Create a Custom VBA Function to Read Font Size

Write a User-Defined Function (UDF) in the Visual Basic Editor to extract the font size from a selected cell, enabling you to use it like a regular formula.

Excel does not have a built-in formula to read a cell's font size directly. By writing a simple User-Defined Function (UDF) using VBA, you can access the cell's font size property and output it into any other cell dynamically.

Open the Visual Basic Editor

Press 'Alt + F11' on your keyboard, or navigate to the Developer tab on the ribbon and click 'Visual Basic' to open the editor.

Insert a New Module

In the Visual Basic Editor, click 'Insert' from the top menu bar and select 'Module'. This will create a blank script window where you can write your custom function.

Add the VBA Code

Copy and paste the following code into the blank module window: Function GetFontSize(rng As Range) GetFontSize = rng.Font.Size End Function

Use the Function in Your Worksheet

Close the Visual Basic Editor to return to your spreadsheet. Select an empty cell where you want the font size to appear and type '=GetFontSize(D3)' (replace D3 with your target cell reference). Press Enter to view the font size.

Create a Custom VBA Function to Read Font Size

Saving Your Workbook: When saving your file, you must select 'Excel Macro-Enabled Workbook (*.xlsm)' from the 'Save as type' dropdown to ensure your custom VBA function continues to work in the future.

Powerful VBA Support

Use VBA and Macros Effortlessly in WPS Office

WPS Office Spreadsheet provides excellent support for VBA and macros, allowing you to create custom functions like extracting font sizes just as easily as you would in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your existing Excel workbook or create a new blank spreadsheet.
2. Access the VBA Editor: Go to the 'Tools' or 'Developer' tab and click on 'Visual Basic Editor' to launch the coding environment.
3. Create the Custom Function: Insert a new module and paste your GetFontSize VBA code into the window, exactly as you would in Excel.
4. Apply in Cells: Type '=GetFontSize(A1)' in any cell to instantly read and display the font size of cell A1.

Full support for Visual Basic for Applications (VBA)Seamless compatibility with Microsoft Excel macro files (.xlsm)Lightweight footprint and fast loading times for heavy datasetsFree to download with an intuitive tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Does the custom formula update automatically when I change the font size?

No, changing a cell's formatting (like font size or color) does not trigger Excel to recalculate formulas automatically. To update the displayed font size, you will need to force a recalculation by pressing F9 or double-clicking the cell with the formula and pressing Enter.

Can I use this custom VBA function across multiple sheets?

Yes, you can reference cells on entirely different sheets by including the sheet name in the formula syntax. For example, you can use '=GetFontSize(Sheet2!A1)' to read the font size of cell A1 on Sheet2.

Why is my VBA function returning a #NAME? error?

The #NAME? error usually occurs if macros are disabled in your Trust Center settings, or if the VBA code was placed in a specific Sheet module (e.g., Sheet1) instead of a standard Module. Ensure your code is in a standard Module and that you have enabled macro execution.

Excel & Data Problems