User-defined Type Issue

[Ryan H]

I have a UDF below that is located in a Standard Module.

Type Totals
COGS As Double
Material As Double
Labor As Double
UnitPrice As Currency
QuotePrice As Currency
End Type

I declared a Public Variable in another Standard Module like this.

Public Total As Totals

For some reason when I use Total with the With...End With Statement in the
code below VBA is throwing an exception stating "With object must be
user-defined type, variant or object". Anybody know why?

Sub Test()

With Total

' determines labor effiency % and COGS %
Select Case Val(tbxQuantity)
Case Is = 1
.COGS = 1.1
Case Is = 2
.COGS = 1.08
Case Is >= 3
.COGS = 1.05
End Select

.Material = 300
.Labor = 1000
End With

End Sub

 

[Patrick Molloy]

to use totals type you'd need to be absolute

ie

some_material = total.material

an alternative would be to open a new CLASS MODULE, name it cTotals and give
it the variables that you gave to the type.

now you'd SET total = New cTotals

as total is an object of class cTotals, you can use the WITH method as you
showed.

 

[Ryan H]

I reed the with statement help and it said it could be used with any
user-defined type. Do you know why it wouldn't work in my application?

 

[Patrick Molloy]

Your code was fine - all I had to do was set some value in tbxQuantity

making sure
OPTION EXPLICIT
is at the top of each module would have highlighted this

 

[Rick Rothstein]

I just tried it out and your code worked fine in my test. When it fails,
what line does debugger stop on and what is the error message it shows you?

 

[Ryan H]

Yes I have OPTION EXPLICIT at the top of all my modules. But I'm really
scratching my head on why the compile error is happening saying "With object
must be user-defined type, variant or object". My Total is a UDF, so why am
I still getting this error?

Thanks in advance!

 

[Ryan H]

If I put Dim Total As Totals at the top of each module the code wants to
work. But for some reason VBA does not want to recognize Public Total As
Totals in another module, why?

 

[Dave Peterson]

Just a guess...

You didn't declare another variable (within that sub or at module level in that
sub's module) as Total, did you?

If you did, then your code will find that one before it sees the public
declaration in a different module.

 

[Ryan H]

Ok figured out why. The Module Name was Total as well. Excel must have
gotten the module Name Total and the Type Total confused. Everything works
fine now.

Thanks for the help!

 

[Jacob Skaria]

It is considered as a good practice to follow some sort of naming convention
for variables, modules, userforms, user controls etc; so as to avoid such
confusions ..

modTotal/gmdTotal to represent module/general module
typTotal to represent custom Type..
frmMain to represent userform
strMessage to represent string variable
cmdOK to represent command button
clsMain to represent Class module

If this post helps click Yes

 

[Patrick Molloy]

i wonder if the issue might have been picked up if you'd celected DEBUG /
COMPILE from the menu? I got "Expected variable or procedure, not module"