Sorting an Excel object in Visio using a macro (VBA)

Question

Currently trying to use the following function to sort some data in a Visio Excel OLE Object. I have confirmed that it can Select the Range (the line before sort), and it is correctly selecting all the data I an trying to sort. As soon as the next line runs (xlSheet.Range.Sort) I get a "Error 1004: Sort method of Range class failed".

I have tried:

Checking with Copilot
Checking Stack (I have spelt Ascending correctly heh)
Opening the excel object from Visio, which opens Excel, then adding a macro to sort the data, which works fine
Tried sorting just column B (B2:B17) with the same error
Tried including headers, didn't make a diff.

But I can't get this error to resolve

Dim xlApp As Object
Dim xlSheet As Object
Dim visPage As Visio.Page
Dim CList As Object

' Get the currently selected Visio page
Set visPage = Visio.ActiveWindow.Page

' Get the Excel object from the Visio page
Set CList = ActivePage.OLEObjects("Cable List").Object

' Get the Excel application and sheet
Set xlApp = CList.Application
Set xlSheet = CList.Sheets(1)

' Sort the range B2:E17 by column B
xlSheet.Range("B2:E17").Select
xlSheet.Range("B2:E17").Sort Key1:=xlSheet.Range("B2"), Order1:=xlAscending, Header:=xlNo

' Do something with the Visio page (e.g., refresh or update)
visPage.Refresh

' Clean up
Set xlSheet = Nothing
Set xlApp = Nothing
Set CList = Nothing

I'm confused about your chosen order of checking. Irrespective to that, what happens when you put Option Explicit on top? — GSerg
– GSerg, Commented Apr 4 at 0:15
Great tip, it says xlAscending is not defined. same with xlNo Thank you <3 <3 <3 — user2705985
– user2705985, Commented Apr 4 at 1:01
To use constants in the EXCEL object model like xlAscending, xlNo, you should reference Microsoft EXCEL Object Model library (Tools->References) — tinazmu
– tinazmu, Commented Apr 4 at 3:40

FunThomas · Accepted Answer · 2025-04-04 08:27:25Z

Common issue when accessing a "foreign" Office application in VBA, in your case Excel inside Visio VBA: Constant definitions (and many other things) of that "foreign" Office app are not known.

In your case, xlAscending is unknown to the VBA compiler because it is defined inside the Excel object model, but not in Visio.

You can solve that issue by figuring out the value of xlAscending (same thing for xlNo). You can do so by searching the internet (see for example the official documentation), or switch to the application (in your case Excel), open the VBA editor, display the immediate window (Ctrl+G) and enter

? xlAscending
 1
? xlNo
 2

Now, you can use

xlSheet.Range("B2:E17").Sort Key1:=xlSheet.Range("B2"), Order1:=1, Header:=2

Or, if you don't like this magic numbers, define the constants by yourself:

Const xlAscending = 1
Const xlNo = 2
xlSheet.Range("B2:E17").Sort Key1:=xlSheet.Range("B2"), Order1:=xlAscending, Header:=xlNo

To be sure that you don't forget any definition, use Option Explicit (you should use that always when programming VBA)

An alternative that can make your life much easier is to add a reference to the Excel object model. Use Tools->References and set a check on Microsoft Excel 16.0 Object Library. With that, the VBA compiler in Visio "learns" all about the Excel Application model, including all the object types and the constant definitions. You can now write

Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet

The sort command will work without modification because the compiler now knows what xlAscending is. Also, you get the help of intellisense in the VBA.

Collectives™ on Stack Overflow

Sorting an Excel object in Visio using a macro (VBA)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related