VBA & Macro Problems

How to Detect Right-to-Left Worksheets Using Excel VBA

Steve K Sep 28, 2026 869 views

Question details

The user needs to display conditional content based on whether an Excel worksheet is configured for a right-to-left or left-to-right text display.

How to Detect Right-to-Left Worksheets Using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Automating tasks or displaying specific worksheet content depending on the current worksheet's text direction settings.
Observed behavior: Requires a functional VBA script or User Defined Function (UDF) to programmatically detect the worksheet's display layout.

Before you start

Ensure that the Developer tab is enabled in your spreadsheet program and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to allow VBA scripts to run.

Solution 1Recommended

Use a Custom VBA Function with the DisplayRightToLeft Property

Create a User Defined Function (UDF) in the VBA Editor that checks the DisplayRightToLeft property to detect the worksheet's orientation.

Excel provides a built-in property called DisplayRightToLeft for worksheet objects. By creating a custom VBA function, you can evaluate this property and return a TRUE or FALSE value.

Once defined, this function can be used directly inside spreadsheet cells like a standard formula, or called by other subroutines to execute conditional code based on the layout.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) Editor. Alternatively, navigate to the Developer tab and click on 'Visual Basic'.

Insert a New Module

In the VBA Editor, click on 'Insert' in the top menu bar, and then select 'Module' from the dropdown list. This creates a standard module where your function can be accessed globally.

Paste the VBA Code

Copy and paste the following code into the blank module window: Function IsSheetRTL(Optional wsh As Worksheet) As Boolean If wsh Is Nothing Then Set wsh = ActiveSheet IsSheetRTL = wsh.DisplayRightToLeft End Function

Use the Function in Excel

Close the VBA Editor and return to your worksheet. You can now type '=IsSheetRTL()' into any cell. It will return TRUE if the sheet is right-to-left, and FALSE if it is left-to-right.

Use a Custom VBA Function with the DisplayRightToLeft Property

Function Flexibility: Because the 'wsh' argument is optional, calling '=IsSheetRTL()' without arguments automatically checks the currently active sheet. You can also specify a sheet in VBA by calling 'IsSheetRTL(Sheets("Sheet2"))'.

Create VBA Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible Developer environment, allowing you to create, edit, and run VBA macros to automate worksheet tasks just like in Microsoft Excel.

1. Enable the Developer Tab: Open WPS Spreadsheet, click on Menu, go to Options, and enable the Developer tab in the custom ribbon settings.
2. Open the VBA Editor: Navigate to the Developer tab and click 'Visual Basic' or simply press Alt + F11 on your keyboard.
3. Insert and Run Macros: Right-click the VBA project tree, select Insert > Module, and paste your desired VBA scripts to automate your tasks.

Fully compatible with Microsoft Excel macro-enabled files (.xlsm)Features a familiar Developer tab and VBA Editor interfaceLightweight application that runs complex VBA code smoothlyEasily configure text directions and conditional layouts

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA function return a #NAME? error in the worksheet?

This error typically occurs if macros are disabled in your workbook, or if the VBA code was placed inside a specific Sheet or ThisWorkbook module instead of a standard Module. Ensure you use Insert > Module in the VBA editor and that macro execution is allowed in your Trust Center settings.

Can I use VBA to change a worksheet to Right-to-Left?

Yes. You can alter the layout programmatically by setting the property directly. Using 'ActiveSheet.DisplayRightToLeft = True' in a VBA Sub will immediately switch the active worksheet to a right-to-left layout.

Will this function update automatically if I change the sheet direction?

Standard custom VBA functions do not always automatically recalculate when a worksheet's layout property changes. To force the formula to update, you can add 'Application.Volatile' to the top of your VBA function, though it may slightly impact spreadsheet performance.

Excel & Data Problems