help: how do I 'translate' a vba command to vb? 1

[patrickdrd]

Hi guys! I am using the following command in vba to sort some numeric data in an excel sheet (this was the macro recorder's output).
However this command does not work in Visual Basic 6.0, how could I make it work? (It is a VBA Command!)

Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Thanks in advance!

 

[percent]

What control are you using in VB6 and how would you like it sorted - Or do you wanting it to sort the excel data using vb6 and if so why if it is working in excel -

When you post a code question try to include to most information that has to do with the question

%, 2004

 

[patrickdrd]

After visiting this link:

http://www.xtremevbtalk.com/t135815.html

It worked with early binding, but I couldn't make it work with late binding.
Even if my code didn't produce any errors, I had no sorting at all!

 

[fredericofonseca]

Please post your code as Percent mentioned.
withouth it it is impossible to tell you what you have done wrong!!



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[tsuji]

Hello patrickdrd,

It works like this.

Selection.Sort activesheet.columns("B2"),xlDescending, _
    xlGuess, 1, False, xlTopToBottom

Just make it positional.

regards - tsuji

 

[dilettante]

The early/late binding debate continues to rage on I see, after many years. ;-)

Here's Microsoft's most recent (that I can find) take on the topic in regard to Automating Office applications:

INFO: Use DISPID Binding to Automate Office Applications Whenever Possible


tsuji's astute observation should help some. Without setting a reference to an Excel object library you haven't provided VB with the "templates" it needs to handle named parameters in calls. Positional is the way to go with late binding, just as it is in VBScript where lack of strong typing leaves no option but late binding.

Another thing to be aware of though is that Excel's VBA environment has a number of objects' members inserted into the global namespace. In Excel VBA this:

[tt]Selection.Sort ...[/tt]

Is really:

[tt]Application.Selection.Sort ...[/tt]

In VB6 this "assumption" doesn't apply, and you must name the parent object explicitly, via a [tt]With[/tt] block or inline. After all, your VB6 code might be automating several applications at the same time.

In VB6 [tt]Application[/tt] isn't there (though VB6 has its own [tt]App[/tt] object of course, referring to the VB6 application itself). So just to clarify:

Dim xlApp As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.Selection.Sort ...

 

[strongm]

The amusing thing about that link is that it cross-references [link

http://support.microsoft.com/default.aspx?kbid=24511}

KB245115 INFO: Using Early Binding and Late Binding in Automation[/url], which advises early binding for Office applications using type libraries from the earliest version you want to use...

Given that I'm not sure how to cache DISPIDs in VB, and given that I hardly ever seem to have more than the type library from the version of Office installed on my PC, I have to say that I'm a supporter of the standard late binding technique

 

[patrickdrd]

Thanks dilettante!

Here is my code (that finally worked with late binding):

wsXL.Range("A2:L" & UBound(arrStats, 2) + 1).Select
objXL.Selection.Sort Key1:=wsXL.Range("B2"), _ Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

I am fun of late binding as you guys understood, because it works with all versions of Excel.