Trouble with late-bound functions

Question

Greetings I have the following code:

Private Sub makeNewReports()


Dim wkSheet As Worksheet
Set wkSheet = ActiveWorkbook.Worksheets("Grades")

Dim i As Long

Dim myMap As Dictionary
Set myMap = New Dictionary


For i = 4 To 6 'wkSheet.Range("a1").End(xlToRight).Column - 1
    Dim myVals As dateValueItem
    myVals.total_value = wkSheet.Cells(2, i)
    myVals.items = wkSheet.Cells(1, i)
    myVals.student_value = wkSheet.Cells(4, i)
    myMap.Add wkSheet.Cells(3, i), myVals
Next i
End Sub

and the following code for dateValueItem

Option Explicit

Public Type dateValueItem
      total_value As Long
      items As String
      student_value As Long
End Type

when I run the above code I get the problem

'Compile Error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late bound functions'

I'm trying to map together 3 different values to a specific date; the dates are held in row three. Row 2 and 4 has numerical values, and row 1 has string values. The hope is to be able to organize all of these together so I can eventually connect descriptions and values to dates.

You have to create a class instead of a user defined type. – Storax Aug 29 '18 at 17:22 — Storax, Aug 29 '18 at 17:22

score 2 · Accepted Answer · answered Aug 29 '18 at 17:25

This might be the quickest way. Create a class and name it dateValueItem

Option Explicit

Public total_value As Long
Public items As String
Public student_value As Long

And change your code to

Option Explicit

Private Sub makeNewReports()
Dim wkSheet As Worksheet
    Set wkSheet = ActiveWorkbook.Worksheets("Grades")

    Dim i As Long

    Dim myMap As Dictionary
    Set myMap = New Dictionary

    Dim myVals As dateValueItem
    For i = 4 To 6    'wkSheet.Range("a1").End(xlToRight).Column - 1
        Set myVals = New dateValueItem
        myVals.total_value = wkSheet.Cells(2, i)
        myVals.items = wkSheet.Cells(1, i)
        myVals.student_value = wkSheet.Cells(4, i)
        myMap.Add wkSheet.Cells(3, i), myVals
    Next i
End Sub

Trouble with late-bound functions

1 Answers1