Word VBA: Return an Array from a function 1

[mintjulep]

How do I define a function to return an Array?

Seems like is should be straight forward, but I can't find the right syntax.

Public Function DueDate(Received As Date, ReplyinDays As Integer)
'Returns an array of three dates:  [Due1, Due2, Due3] based on Due3 = Recived + ReplyinDays
'Everything in calendar days.
'If a due date falls on weekend, corrected to the Friday before.

Dim Due1 As Date  'Subject expert
Dim Due2 As Date  'Internal Management
Dim Due3 As Date  'To Client

Due1 = FridayBefore(DateAdd("d", ReplyinDays - 14, Received))
Due2 = FridayBefore(DateAdd("d", ReplyinDays - 10, Received))
Due3 = FridayBefore(DateAdd("d", ReplyinDays, Received))


DueDate = Array(Due1, Due2, Due3)
End Function

Public Function FridayBefore(Feed As Date) As Date
Dim WhatDay As Integer

WhatDay = Weekday(Feed)

Select Case WhatDay
    Case 7 'Saturday
        FridayBefore = DateAdd("d", -1, Feed)
    Case 1  'Sunday
        FridayBefore = DateAdd("d", -2, Feed)
    Case Else
        FridayBefore = Feed
End Select
End Function

 

[Andrzejek]

How about a little example like this:

Option Explicit

Private Sub CommandButton1_Click()
Dim a() As String
Dim i As Integer

a = [blue]Return_Array[/blue]

For i = LBound(a) To UBound(a)
    Debug.Print a(i)
Next i

End Sub
[blue]
Private Function Return_Array() As String()
Dim aryTempAry(3) As String

aryTempAry(0) = "Friday"
aryTempAry(1) = "Saturday"
aryTempAry(2) = "Sunday"
aryTempAry(3) = "Monday"

Return_Array = aryTempAry

End Function[/blue]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mintjulep]

That seems the same as this:

Public Function DueDate(Received As Date, ReplyinDays As Integer) As Date()
'Returns an array of three dates:  [Due1, Due2, Due3] based on Due3 = Recived + ReplyinDays
'Everything in calendar days.
'If a due date falls on weekend, corrected to the Friday before.

Dim DueArray() As Date
ReDim DueArray(1 To 3)
Dim Due1 As Date
Dim Due2 As Date  
Dim Due3 As Date    

Due1 = FridayBefore(DateAdd("d", ReplyinDays - 14, Received))
Due2 = FridayBefore(DateAdd("d", ReplyinDays - 10, Received))
Due3 = FridayBefore(DateAdd("d", ReplyinDays, Received))

DueArray(1) = Due1
DueArray(2) = Due2
DueArray(3) = Due3

DueDate = DueArray
End Function

that fails with "Type Mismatch

 

[Andrzejek]

I don't know the whole code of yours, but I simplified it a little bit just to see what's going on, and it works like a charm:

Option Explicit

Private Sub CommandButton1_Click()
Dim a() As Date
Dim i As Integer

a = DueDate(CDate("5/5/2014"), 13)

For i = LBound(a) To UBound(a)
    Debug.Print a(i)
Next i

End Sub

Public Function DueDate(Received As Date, ReplyinDays As Integer) As Date()

Dim DueArray() As Date
ReDim DueArray(1 To 3)
Dim Due1 As Date
Dim Due2 As Date
Dim Due3 As Date

Due1 = CDate("1/1/2014")
Due2 = CDate("2/2/2014")
Due3 = CDate("3/3/2014")

DueArray(1) = Due1
DueArray(2) = Due2
DueArray(3) = Due3

DueDate = DueArray
End Function

And I get in the Immediate Window:
[tt]
1/1/2014
2/2/2014
3/3/2014
[/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ChrisHirst]

The simple answer is;

Exactly the same way you return any other value type from a function.



Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum