How do I pass an User Defined Type into a VBA function?

Question

This seems as if it should be really easy to achieve, but I'm strugling at the moment, any help would be greatly appreciated.

I have a User Defined Type, which defines 4 fields relating to date information. I want to pass the variable linked to this type into a function, indexing through each month. Below is the type, sub and function.

I understand that the function has no knowledge of TestMDate type, I've tried declaring the type within the function, but nothing seems to work.

many thanks in advance

glw

''''''''''''''''''''''''''''''''
'UDT
''''''''''''''''''''''''''''''''
Private Type TestMDate
        MonthS As String
        MonthD As String
        StartD As String
        EndD As String
End Type
    
''''''''''''''''''''''''''''''''
'Sub
''''''''''''''''''''''''''''''''
Sub UpdateChart()

Dim Tstruct(12) As TestMDate
Dim a As Integer, i As Integer


    
''''''''''''''''''''''''''''''''
'
''''''''''''''''''''''''''''''''
Tstruct(1).MonthS = "Jan"
Tstruct(1).MonthD = "01"
Tstruct(1).StartD = "01"
Tstruct(1).EndD = "31"
Tstruct(2).MonthS = "Feb"
Tstruct(2).MonthD = "02"
Tstruct(2).StartD = "01"
Tstruct(2).EndD = "28"
Tstruct(3).MonthS = "Mar"
Tstruct(3).MonthD = "03"
Tstruct(3).StartD = "01"
Tstruct(3).EndD = "31"
Tstruct(4).MonthS = "Apr"
Tstruct(4).MonthD = "04"
Tstruct(4).StartD = "01"
Tstruct(4).EndD = "30"
Tstruct(5).MonthS = "May"
Tstruct(5).MonthD = "05"
Tstruct(5).StartD = "01"
Tstruct(5).EndD = "31"
Tstruct(6).MonthS = "Jun"
Tstruct(6).MonthD = "06"
Tstruct(6).StartD = "01"
Tstruct(6).EndD = "30"
Tstruct(7).MonthS = "Jul"
Tstruct(7).MonthD = "07"
Tstruct(7).StartD = "01"
Tstruct(7).EndD = "31"
Tstruct(8).MonthS = "Aug"
Tstruct(8).MonthD = "08"
Tstruct(8).StartD = "01"
Tstruct(8).EndD = "31"
Tstruct(9).MonthS = "Sep"
Tstruct(9).MonthD = "09"
Tstruct(9).StartD = "01"
Tstruct(9).EndD = "30"
Tstruct(10).MonthS = "Oct"
Tstruct(10).MonthD = "10"
Tstruct(10).StartD = "01"
Tstruct(10).EndD = "31"
Tstruct(11).MonthS = "Nov"
Tstruct(11).MonthD = "11"
Tstruct(11).StartD = "01"
Tstruct(11).EndD = "30"
Tstruct(12).MonthS = "Dec"
Tstruct(12).MonthD = "12"
Tstruct(12).StartD = "01"
Tstruct(12).EndD = "31"

   
For i = 1 To 12
    a = proc_res_field(Tstruct(i))
Next i
End Sub

''''''''''''''''''''''''''''''''
'Function
''''''''''''''''''''''''''''''''
Function proc_res_field(Tstruct As TestMDate)
    Debug.Print Tstruct.MonthS
    Debug.Print Tstruct.MonthD
    Debug.Print Tstruct.StartD
    Debug.Print Tstruct.EndD
    a = 1
End Function

apologies, I've extracted the type, sub and function from my main sub as I didnt want to confuse the issue. I only included the debug print as an example of what I was trying to do with the data passed in from the UDT. — GLW75
Debug.Print Tstruct(i).MonthS you are expecting and giving a struct but then trying to treat it as an array of struct. Remove the (i) which is ALSO undefined. So make it consistent, either an array of structs or a struct, you loop implies it should be a struct. — user12431753
You've now amended your posted code which renders Mark's comment inexplicable to anyone seeing the question now. — SJR
Arrays start at 0 so you are not defining the first element in the array. So you array is 13 elements 0 to 12. — user12431753
yep, thanks for pointing that out Mark, I am trying to process it as a struct. My question is how does the Function know what the TestMDate type is? — GLW75

Tim Williams Tim Williams · Accepted Answer · 2020-07-08T22:05:31

proc_res_field doesn't know anything about i or a

Function proc_res_field(Tstruct As TestMDate)
    Debug.Print Tstruct.MonthS
    Debug.Print Tstruct.MonthD
    Debug.Print Tstruct.StartD
    Debug.Print Tstruct.EndD
    proc_res_field = 1 '?
End Function

How do I pass an User Defined Type into a VBA function?

2 Answers