Hi,

I'm converting some VB6 forms and classes to run in VBA. Having some real
trouble with converting forms in general and the class modules behind the
forms. Below is a highly simplified example of the trouble - basically
a "Type Mismatch" situation.

I've been doing a ton of F8'ing and looking at both the Locals Window and
the Watches window to try to see why there is a mismatch. Also fooled
around with:

varname1 Is varname2 [tests if both point to same object]
IsObject(identifier) [simple true/false]
TypeName(varname) [returns the type name of the variable, "object" if
object.]

In VB you Dim as Form
In VBA you Dim as userForm or "the-actual-name-of-the-form-class"

Before I throw in the towel and just go with a generic "object" type take a
look at this and see if you can point the way out. (example below)

Any additional info on VBA vs. VB (Forms vs. UserForms) would be greatly
appreciated. I'm new to class modules and event processing and its been a
steep learning curve. Any other great references, books, tutorials very
much appreciated.

Yes, after I "down grade" the code from VB6 to VBA (Office 2000) I need to
upgrade it to VB.NET. Fun!

THANKS!!!

Rick


[create a new form with one ListBox1 on it]

Private i As Integer
Private m_listBox1 As ListBox '<=== why doesn't this work?
'Returns run time error 13 type mismatch???
'Private m_listBox1 As Object '<=== yet this does

Private Sub UserForm_Initialize()
i = 0
Set m_listBox1 = Me.ListBox1
End Sub

Private Sub UserForm_Click()
i = i + 1
m_listBox1.AddItem (i)
End Sub

Rick,

In Excel/VBA there are two distinct objects named ListBox. The
first is in the Excel typelib is the list box that is available
on the Forms command bar. The second is in the MSForms typelib,
and it the one you use on UserForms. Because the Excel typelib
has a higher priority in the compiler than the MSForms typelib,
the compiler uses Excel's ListBox. You need to qualify ListBox
with the name of the typelib in which it resides.

Change

Private m_listBox1 As ListBox
to
Private m_listBox1 As MSForms.ListBox

Using 'As Object' works because Object can hold any sort of
object, and the assignment is made at run time, not compile time.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Rick Labs" wrote in message
om...
Hi,

I'm converting some VB6 forms and classes to run in VBA. Having
some real
trouble with converting forms in general and the class modules
behind the
forms. Below is a highly simplified example of the trouble -
basically
a "Type Mismatch" situation.

I've been doing a ton of F8'ing and looking at both the Locals
Window and
the Watches window to try to see why there is a mismatch. Also
fooled
around with:

varname1 Is varname2 [tests if both point to same object]
IsObject(identifier) [simple true/false]
TypeName(varname) [returns the type name of the variable,
"object" if
object.]

In VB you Dim as Form
In VBA you Dim as userForm or
"the-actual-name-of-the-form-class"

Before I throw in the towel and just go with a generic "object"
type take a
look at this and see if you can point the way out. (example
below)

Any additional info on VBA vs. VB (Forms vs. UserForms) would
be greatly
appreciated. I'm new to class modules and event processing and
its been a
steep learning curve. Any other great references, books,
tutorials very
much appreciated.

Yes, after I "down grade" the code from VB6 to VBA (Office
2000) I need to
upgrade it to VB.NET. Fun!

THANKS!!!

Rick


[create a new form with one ListBox1 on it]

Private i As Integer
Private m_listBox1 As ListBox '<=== why doesn't this work?
'Returns run time error 13 type mismatch???
'Private m_listBox1 As Object '<=== yet this does

Private Sub UserForm_Initialize()
i = 0
Set m_listBox1 = Me.ListBox1
End Sub

Private Sub UserForm_Click()
i = i + 1
m_listBox1.AddItem (i)
End Sub

Chip,

Thanks very much for clearing up the difference between:

Private m_listBox1 As MSForms.ListBox 'newer activeX control
'vs.
Private m_listBox1 As ListBox 'old Excel control
'and the same for textbox

Was just wondering if there was any way I could have chased down your
solution myself with standard VBA tools? I only had the "type mismatch"
error clue and despite considerable effort I could not "discover" your
solution on my own.

A programmer friend of mind strongly suggested that I bag VBA entirely and
just write stuff in VB and control Excel from there. He insisted that the VB
development environment would be much better to chase down this type of
object referencing/scoping/visibility challenge. Is that true? If so, what
specific tool(s) in VB would you recommend looking at? Perhaps it is time to
break free of VBA.

Another C++ friend recommends that I get some software that will show me
inter window communications (messages) just to get a feel for that. Nothing
too deep, just a feel. I have a tiny exposure to these already. It's mind
boggling.

I assume most API calls from VB6/VBA will be different than from VB.net.
Other than the few VB6/VBA API calls I use currently I'll probably just stop
learning anything at the VB6/VBA level and move up to VB.Net/Foundation
Classes. Ultimately everything I do will have to go to VB.Net and Excel. Do
you see any point in doing things first in VB6 then moving up to VB.NET?

Thanks again.

Rick



"Chip Pearson" wrote in message
...
Rick,

In Excel/VBA there are two distinct objects named ListBox. The
first is in the Excel typelib is the list box that is available
on the Forms command bar. The second is in the MSForms typelib,
and it the one you use on UserForms. Because the Excel typelib
has a higher priority in the compiler than the MSForms typelib,
the compiler uses Excel's ListBox. You need to qualify ListBox
with the name of the typelib in which it resides.

Change

Private m_listBox1 As ListBox
to
Private m_listBox1 As MSForms.ListBox

Using 'As Object' works because Object can hold any sort of
object, and the assignment is made at run time, not compile time.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Rick Labs" wrote in message
om...
Hi,

I'm converting some VB6 forms and classes to run in VBA. Having
some real
trouble with converting forms in general and the class modules
behind the
forms. Below is a highly simplified example of the trouble -
basically
a "Type Mismatch" situation.

I've been doing a ton of F8'ing and looking at both the Locals
Window and
the Watches window to try to see why there is a mismatch. Also
fooled
around with:

varname1 Is varname2 [tests if both point to same object]
IsObject(identifier) [simple true/false]
TypeName(varname) [returns the type name of the variable,
"object" if
object.]

In VB you Dim as Form
In VBA you Dim as userForm or
"the-actual-name-of-the-form-class"

Before I throw in the towel and just go with a generic "object"
type take a
look at this and see if you can point the way out. (example
below)

Any additional info on VBA vs. VB (Forms vs. UserForms) would
be greatly
appreciated. I'm new to class modules and event processing and
its been a
steep learning curve. Any other great references, books,
tutorials very
much appreciated.

Yes, after I "down grade" the code from VB6 to VBA (Office
2000) I need to
upgrade it to VB.NET. Fun!

THANKS!!!

Rick


[create a new form with one ListBox1 on it]

Private i As Integer
Private m_listBox1 As ListBox '<=== why doesn't this work?
'Returns run time error 13 type mismatch???
'Private m_listBox1 As Object '<=== yet this does

Private Sub UserForm_Initialize()
i = 0
Set m_listBox1 = Me.ListBox1
End Sub

Private Sub UserForm_Click()
i = i + 1
m_listBox1.AddItem (i)
End Sub