PDA

View Full Version : public object module error



KMoney
04-21-2010, 03:40 PM
Hello,
I have the following code ...

Option Explicit
Type LineInfo
Position As Integer
Month As String * 1
Year As Integer
Strike As Integer
CPF As String * 1
End Type

Sub ProduceReport()
Dim rows1 As Integer, rows2 As Integer
Dim USAline() As LineInfo
'
rows1 = Worksheets("USA").Cells(Rows.Count, "A").End(xlUp).row
' rows2 = Worksheets("ProOpt").Cells(Rows.Count, "A").End(xlUp).row
'
' USAline = Worksheets("USA").Range(Cells(2, 1), Cells(rows1, 5))

' Populate the array.
USAline = Worksheets("USA").Range(Cells(2, 1), Cells(rows1, 5))
' Display a message box with a value in the array
MsgBox USAline(25, 1)
End Sub

Why might I be generating the error
"only user defined types defined in public object modules ..." ?

austenr
04-21-2010, 05:24 PM
Please use the code tags when posting your code. Its the square that says VBA in it right above the place you type your question. Click it and place your code where the cursor is blinking.

ZVI
04-21-2010, 09:16 PM
Hi KMoney,

MsgBox USAline(25, 1) is incorrect.
You shall use MsgBox USAline(25, 1).Position or MsgBox USAline(25, 1).Month and so on, because USAline() is array of the stuctures in your code.

But the code is wrong anyway because you can’t populate array of the structures by USAline() = MyRange.Value
ReDim that array instead and loop to populate each of its items, for example:
USAline(1,1).Position = MyRange.Cells(2,1).Value
USAline(1, 1). Month = MyRange.Cells(2,2).Value
etc

Not clear why you are using array of the structures at all.

Vladimir

KMoney
04-23-2010, 04:49 PM
Thanks for helping again Vladimir ...

Let me try to explain what is going on:
I have two arrays, arr1 and arr2. Each element of both arrays consists of five elements, as defined by my UDT PosInfo(). I would like to make sure that each element of arr1 is in arr2 and report any ommissions using something like

'On Error Goto 'Handler that will write USA_Line(i) to report
For i = 1 To UBound(USA_Line)
x = WorksheetFunction.Match(USA_Line(i), PO_Line(), 0)
Next i


but this bit of code generates the same error:
"only user defined types defined in public object modules ...."

Any thoughts? Does the match function not work with UDTs?

Thanks.

KMoney
04-23-2010, 04:50 PM
Here is the entire code:

Sub test3()
Dim USA_Line() As PosInfo, PO_Line() As PosInfo
Dim USA_num_rows As Integer, PO_num_rows As Integer
Dim r As Integer
Dim x As Integer, i As Integer

USA_num_rows = Worksheets("USA").Cells(rows.Count, "A").End(xlUp).row
ReDim USA_Line(1 To USA_num_rows - 1) As PosInfo
For r = 1 To UBound(USA_Line)
USA_Line(r).Month = Worksheets("USA").Cells(r + 1, 1).Value
Next r

PO_num_rows = Worksheets("ProOpt").Cells(rows.Count, "A").End(xlUp).row
ReDim PO_Line(1 To PO_num_rows - 1) As PosInfo
For r = 1 To UBound(PO_Line)
PO_Line(r).Month = Worksheets("ProOpt").Cells(r + 1, 1).Value
Next r

'On Error Goto 'Handler that will write USA_Line(i) to report
For i = 1 To UBound(USA_Line)
x = WorksheetFunction.Match(USA_Line(i), PO_Line(), 0)
Next i


End Sub

Aussiebear
04-24-2010, 02:24 AM
That section of code is significantly different from the initially posted, however that aside, I've never seen any code before start with

Type LineInfo
Position As Integer
Month As String * 1
Year As Integer
Strike As Integer
CPF As String * 1
End Type
What are you trying to accomplish here?

Bob Phillips
04-24-2010, 03:16 AM
Match will not work against an array of types, it requires a single dimensioned array.,

If you want to use types, you need to check each



Sub test3()

Dim USA_Line() As PosInfo, PO_Line() As PosInfo
Dim USA_num_rows As Long, PO_num_rows As Long
Dim r As Long
Dim x As Long, i As Long

USA_num_rows = Worksheets("USA").Cells(Rows.Count, "A").End(xlUp).Row
ReDim USA_Line(1 To USA_num_rows - 1)
For r = 1 To UBound(USA_Line)
USA_Line(r).Month = Worksheets("USA").Cells(r + 1, 1).Value
Next r

PO_num_rows = Worksheets("ProOpt").Cells(Rows.Count, "A").End(xlUp).Row
ReDim PO_Line(1 To PO_num_rows - 1) As PosInfo
For r = 1 To UBound(PO_Line)
PO_Line(r).Month = Worksheets("ProOpt").Cells(r + 1, 1).Value
Next r

'On Error Goto 'Handler that will write USA_Line(i) to report
For i = 1 To UBound(USA_Line)
If USA_Line(i).Month <> PO_Line(i).Month Then

MsgBox "No match at " & i
End If
Next i
End Sub

KMoney
04-24-2010, 12:33 PM
I did not realize that about Match(), thank you. I will proceed from here ...

KMoney
04-24-2010, 12:56 PM
Is there any way to compare the entire element of each array?

i.e. check if USA_Line(i) = PO_Line(i)


This is my whole motivation for using types. Otherwise, using types doesn't accomplish anything.

Bob Phillips
04-25-2010, 04:52 AM
Not really. You have discovered another fundamental flaw in types IMO. Ther are one of those good ideas that doesn't deliver. You could create a class that emulates the type, whic returns a concatenated value which you could compare, but it may be simpler to just compare each element.