Compile Error - User-defined Type Not Defined

aread

New Member
Joined
Dec 27, 2019
Messages
25
Office Version
365
Platform
Windows
I have been trying to use code I found online and tweaked a little to combine and sum duplicates, but I need to have two values for each key. It then needs to paste that data in a worksheet called "Consolidation" in rows A, B, and C. I am currently getting the error on the line Private Function ReadMultiItems() As Dictionary and am not sure what is wrong. I have added References that others have recommended online as well, but their problems were different. Any suggestions?

Here is some sample data from columns A, B, and M:

Vendor-MatlQty in OPUnAmt.in loc.cur.
30000586-10197957,25712,772.32
30000555-10197959203,072.80
30000571-10197954,8966,462.72
30000586-10197954,8388,514.88
30000586-10197952,4194,257.44
30000571-10197954,8966,462.72
30000572-1019795357335.58
30000586-10197957,25712,772.32
30000589-10196581,3235,702.13
30000601-101965210,00014,730.00
30000593-101955111,0247,496.32
30000593-101955111,0247,496.32
30000600-101947914,968.70240,864.56
20000153-1019425188,607291,831.61
20000153-1019425187,788290,564.37
20000153-1019425188,139291,107.47
20000153-1019425186,274288,221.76

I have a Class Module with the following:
VBA Code:
Public MatlVend As String
Public Volume As Long
Public Spend As Long
And then the module contains the following code:

VBA Code:
Sub Main()

    Dim dict As Dictionary
    
    ' Read the data to the dictionary
    Set dict = ReadMultiItems
    
    ' Write the Dictionary contents to the Immediate Window(Ctrl + G)
    WriteToImmediate dict
    
    ' Write the Dictionary contents to a worksheet
    WriteToWorksheet dict, ThisWorkbook.Worksheets("Consolidation")

End Sub

Private Function ReadMultiItems() As Dictionary

    ' Declare and Create the Dictionary
    Dim dict As New Dictionary
    
    ' Get the worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    
    ' Get the range of all the adjacent data using CurrentRegion
    Dim rg As Range
    Set rg = sh.Range("A1").CurrentRegion

    Dim oMatl As clsMatl, i As Long, MatlVend As String
    ' read through the data
    For i = 2 To rg.Rows.Count
        
        MatlVend = rg.Cells(i, 1).Value
        
        ' check if the customerID has been added already
        If dict.Exists(MatlVend) = True Then
            ' Get the existing customer object
            Set oCust = dict(MatlVend)
        Else
            ' Create a new clsCustomer object
            Set oCust = New clsMatl
        
             ' Add the new clsCustomer object to the dictionary
            dict.Add MatlVend, oMatl
        End If
        
        ' Set the values
        oMatl.MatlVend = MatlVend
        oMatl.Volume = oMatl.Volume + rg.Cells(i, 2).Value
        oMatl.Spend = oMatl.Spend + rg.Cells(i, 13).Value
            
    Next i
    
    ' Return the dictionary to the Main sub
    Set ReadMultiItems = dict

End Function


' Write the Dictionary contents  to a worksheet
' https://excelmacromastery.com/
Private Sub WriteToWorksheet(dict As Dictionary, sh As Worksheet)
    
    Dim row As Long
    row = 1
    
    Dim key As Variant, oMatl As clsMaterial
    ' Read through the dictionary
    With ThisWorkbook.Worksheets("Consolidation")
    For Each key In dict.Keys
        Set oMatl = dict(key)
        With oMatl
            ' Write out the values
            sh.Cells(row, 1).Value = .MatlVend
            sh.Cells(row, 2).Value = .Volume
            sh.Cells(row, 3).Value = .Spend
            row = row + 1
        End With
        
    Next key
    End With
End Sub
 

VBE313

Board Regular
Joined
Mar 22, 2019
Messages
210
Office Version
365
Platform
Windows
Do you have "Microsoft Scripting Runtime" Checked in VBE>Tools>References?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,078
Office Version
365
Platform
Windows
Have you set a reference to the "Microsoft Scripting Runtime" library?
 

aread

New Member
Joined
Dec 27, 2019
Messages
25
Office Version
365
Platform
Windows
No, that one was missing...

Now I am getting the same error and it selects the text "oMatl As clsMatl" but when I click Ok, it still highlights Private Function ReadMultiItems() As Dictionary
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,078
Office Version
365
Platform
Windows
Did you copy the class code that would have come with the code you posted?
If so did you follow the instructions on how to use it?
 

aread

New Member
Joined
Dec 27, 2019
Messages
25
Office Version
365
Platform
Windows
There weren't any instructions other than copy the code and paste it into a class module, which I did.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,078
Office Version
365
Platform
Windows
Did you rename the class module to clsMatl
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,078
Office Version
365
Platform
Windows
As a matter of interest, where did you get the code from?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,078
Office Version
365
Platform
Windows
Whilst it doesn't say it the class module needs to be renamed, in the code you linked to it should be ClsCustomer, but as you have changed it, it needs to be clsMatl
 

Forum statistics

Threads
1,081,716
Messages
5,360,819
Members
400,600
Latest member
Stuckagainandagain

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top