| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-defined Type Issue
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 -- Cheers, Ryan |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User-defined Type Issue
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" wrote: 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 -- Cheers, Ryan |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User-defined Type Issue
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? -- Cheers, Ryan "Patrick Molloy" wrote: 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" wrote: 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 -- Cheers, Ryan |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User-defined Type Issue
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 "Ryan H" wrote: 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? -- Cheers, Ryan "Patrick Molloy" wrote: 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" wrote: 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 -- Cheers, Ryan |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User-defined Type Issue
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! -- Cheers, Ryan "Patrick Molloy" wrote: 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 "Ryan H" wrote: 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? -- Cheers, Ryan "Patrick Molloy" wrote: 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" wrote: 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 -- Cheers, Ryan |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User-defined Type Issue
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? -- Cheers, Ryan "Patrick Molloy" wrote: 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 "Ryan H" wrote: 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? -- Cheers, Ryan "Patrick Molloy" wrote: 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" wrote: 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 -- Cheers, Ryan |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User-defined Type Issue
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! -- Cheers, Ryan "Ryan H" wrote: 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? -- Cheers, Ryan "Patrick Molloy" wrote: 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 "Ryan H" wrote: 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? -- Cheers, Ryan "Patrick Molloy" wrote: 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" wrote: 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 -- Cheers, Ryan |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User-defined Type Issue
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? -- Rick (MVP - Excel) "Ryan H" wrote in message ... 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? -- Cheers, Ryan "Patrick Molloy" wrote: 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" wrote: 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 -- Cheers, Ryan |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User-defined Type Issue
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 wrote: 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 -- Cheers, Ryan -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compile error: User-defined type not defined | Excel Programming | |||
| Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
| Workspace faux user-defined type not defined | Excel Programming | |||
| User-defined data type; Error: Only User-defined types... | Excel Programming | |||
| Word.Document - user defined type not defined | Excel Programming | |||
Hybrid Mode
