Early binding problem

[Walter Briscoe]

I have become convinced that early binding is "a good thing".
My technique for applying it is to declare a variable with type Object,
use typename to find the type and then redeclare the variable with that
type. However, that sometimes fails for no reason I can understand.

This is a cut-down example:

Public Sub TypeChange()
Const URL As String = "journeyplanner.tfl.gov.uk/user/" & _
"XSLT_TRIP_REQUEST2?language=en&sessionID=0" & _
"&type_origin=stop&name_origin=ANGEL" & _
"&type_destination=stop&name_destination=BANK"

Dim Tables As Object ' Array of tables in ie.document
' DispHTMLElementCollection gets assignment error

TxURL URL ' Connect to application.internetexplorer and set Doc
Set Tables = Doc.getElementsByTagName("Table")
Debug.Print TypeName(Tables)
End Sub

This shows that Tables is set as a DispHTMLElementCollection
If I redeclare Tables As DispHTMLElementCollection,
the set statement fails with a Run-time error '13':
Type mismatch.

Why?

How do I get types of methods?
How do I get the definition of DispHTMLElementCollection?
(I can easily get member types with View\Locals Window.)

My hypothesis is that getElementsByTagName is declared to return a more
general type than DispHTMLElementCollection and that type mismatches.

This smaller example does not show the problem.;(
Dim Origin As DispHTMLElementCollection
Dim Destination As DispHTMLElementCollection

Set Destination = Origin


Light, please?

 

[Jim Cone]

To use early binding you must have a reference set to the object library (Tools | References in the
VBE)
One library I found is the "Microsoft HTML Object Library".
It permits you to set a references to the "HTMLElementCollection"
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Data Options Excel add-in: Color, Delete, Insert: rows/dates/random data )




"Walter Briscoe" <[email protected]>
wrote in message
news:[email protected]...

 

[Walter Briscoe]

In message <[email protected]> of Tue, 20 Dec 2011 08:50:47 in
microsoft.public.excel.programming, Jim Cone

To use early binding you must have a reference set to the object
library (Tools | References in the
VBE)
One library I found is the "Microsoft HTML Object Library".
It permits you to set a references to the "HTMLElementCollection"

Jim,
I understand that and already have a reference set to "Microsoft HTML
Object Library" as you have found.

That does not explain the Run-time error '13': Type mismatch in:
Dim Tables As DispHTMLElementCollection

....
Set Tables = Doc.getElementsByTagName("Table") ' gets Type mismatch.

 

[Ron Rosenfeld]

I have become convinced that early binding is "a good thing".
My technique for applying it is to declare a variable with type Object,
use typename to find the type and then redeclare the variable with that
type. However, that sometimes fails for no reason I can understand.

This is a cut-down example:

Public Sub TypeChange()
Const URL As String = "journeyplanner.tfl.gov.uk/user/" & _
"XSLT_TRIP_REQUEST2?language=en&sessionID=0" & _
"&type_origin=stop&name_origin=ANGEL" & _
"&type_destination=stop&name_destination=BANK"

Dim Tables As Object ' Array of tables in ie.document
' DispHTMLElementCollection gets assignment error

TxURL URL ' Connect to application.internetexplorer and set Doc
Set Tables = Doc.getElementsByTagName("Table")
Debug.Print TypeName(Tables)
End Sub

This shows that Tables is set as a DispHTMLElementCollection
If I redeclare Tables As DispHTMLElementCollection,
the set statement fails with a Run-time error '13':
Type mismatch.

Why?

How do I get types of methods?
How do I get the definition of DispHTMLElementCollection?
(I can easily get member types with View\Locals Window.)

My hypothesis is that getElementsByTagName is declared to return a more
general type than DispHTMLElementCollection and that type mismatches.

This smaller example does not show the problem.;(
Dim Origin As DispHTMLElementCollection
Dim Destination As DispHTMLElementCollection

Set Destination = Origin


Light, please?

Googling for the various properties/methods will usually detect a page in the MSDN library for the various definitions.

In your specific example,

Dim Tables As Object ' Array of tables in ie.document
' DispHTMLElementCollection gets assignment error

did you also try: Dim Tables As HTMLElementCollection

I don't even see DispHTMLElementCollection as a valid object in the drop down list.

 

[Walter Briscoe]

In message <[email protected]> of Wed, 21 Dec
2011 08:12:15 in microsoft.public.excel.programming, Ron Rosenfeld

Googling for the various properties/methods will usually detect a page
in the MSDN library for the various definitions.

In your specific example,


did you also try: Dim Tables As HTMLElementCollection

I did not. What would have led me to do so? I have since tried and also
got a type mismatch.

I don't even see DispHTMLElementCollection as a valid object in the
drop down list.

What drop down list?
Do you mean View/Object Browser also accessible via F2.
I found nothing relevant there under <All Libraries>.
After I stepped into my sub, MSHTML, among other libraries, was added to
<All Libraries> and HTMLElementCollection was viewable.

I have
Dim Doc As HTMLDocument ' IE.Document
' Needs Tools/References/HTML Object Library

Ah! Now I begin to see. HTMLDocument has getElementsByTagName as a
member which is specified as
Function getElementsByTagName(v As String) as IHTMLElementCollection.

When I do
Dim Tables As IHTMLElementCollection
Set Tables = Doc.getElementsByTagName("Table") runs without an error.

Curiously, typename(tables) is also "DispHTMLElementCollection".
Where does that come from?

Thank you for sending me in good directions.

I quote myself and give some answers:View/Object Browser also accessible via F2.
I don't know but found IHTMLElementCollection serves my need.
It seems DispHTMLElementCollection is undocumented.

I can look up the specification of a type if I know where it is
specified. How do I look it up without such knowledge?

Thanks again. I am happier with early binding than I was.

 

[Ron Rosenfeld]

What drop down list?

If I have set the reference appropriately (Tools/References), when I type, for example

Sub foo()
"Dim Table as " (without the quotes but WITH the trailing space)
end sub

I am presented with a drop down list containing all of the legitimate choices for type assignment.

Also, most of the time, after typing a dot:

Dim rg as Range
Set rg = Range("A1"). After typing that terminal Dot, I will be presented with a drop-down list of possible Range properties

I don't recall what that is call, but to me, that help facility is the major incentive for early binding.

 

[GS]

Ron Rosenfeld presented the following explanation :

If I have set the reference appropriately (Tools/References), when I type,
for example

Sub foo()
"Dim Table as " (without the quotes but WITH the trailing space)
end sub

I am presented with a drop down list containing all of the legitimate choices
for type assignment.

Also, most of the time, after typing a dot:

Dim rg as Range
Set rg = Range("A1"). After typing that terminal Dot, I will be
presented with a drop-down list of possible Range properties

I don't recall what that is call, but to me, that help facility is the major
incentive for early binding.

I believe the term for this is "context sensitive", where the VBE
implements "auto-sense" for entering valid properties/methods/constants
etcedera!

 

[Ron Rosenfeld]

I believe the term for this is "context sensitive", where the VBE
implements "auto-sense" for entering valid properties/methods/constants
etcedera!

Bingo.

Do you have any idea why it sometimes works and sometimes doesn't?

eg:

Sub foo()
Dim w As Worksheet
Set w = ThisWorkbook.Worksheets(1)

'does NOT show context sensitive prompts
Worksheets(1).Range("A1").Value = 2

'does show context sensitive prompts
w.Range("a2").Value = 3

End Sub

 

[GS]

Ron Rosenfeld laid this down on his screen :

Bingo.

Do you have any idea why it sometimes works and sometimes doesn't?

eg:

Sub foo()
Dim w As Worksheet
Set w = ThisWorkbook.Worksheets(1)

'does NOT show context sensitive prompts
Worksheets(1).Range("A1").Value = 2

'does show context sensitive prompts
w.Range("a2").Value = 3

End Sub

Hi Ron,
I have no idea why some of the objects don't display intellisense. Same
goes for ActiveSheet and I can't recall the few others I've
encountered. I also see similar behavior if we Enum and try using
enumname.enumitem syntax, but using a variable declared as enumname
works when you type the dot.<???>

 

[Martin Brown]

Bingo.

Do you have any idea why it sometimes works and sometimes doesn't?

Someone forgot that there are implicitly available objects like
Worksheets(), Cells() etc and AFAIK none of them intellisense.
Nor do they appear in the quick debug window when in context.

eg:

Sub foo()
Dim w As Worksheet
Set w = ThisWorkbook.Worksheets(1)

'does NOT show context sensitive prompts
Worksheets(1).Range("A1").Value = 2

'does show context sensitive prompts
w.Range("a2").Value = 3

End Sub

Regards,
Martin Brown

 

[Ron Rosenfeld]

Someone forgot that there are implicitly available objects like
Worksheets(), Cells() etc and AFAIK none of them intellisense.
Nor do they appear in the quick debug window when in context.

It'd be nice if they could fix that. But it seems unlikely.