CODEWITHSUNDEEP
vbaexcel
M. A.
M. A.

Reputation: 415

Type mismatch when returning an array from a function

I am trying to create a function, for use in subs, that creates an array of actual days in a month. I am unable to run the function by using either the Immediate window directly or with a sub. The function's code is the following (Debug.Print instances are removed, as they never even trigger):

Function GenererDager(ByVal month As Integer, ByVal year As Integer) As Integer

    Dim dateFormat As String, daysArr() As Integer, actualDays As Integer

    actualDays = 1

    ' Find the number of actual days in given month
    For dayCheck = 1 To 31

        dateFormat = month & "/" & dayCheck & "/" & year ' mm/dd/yyyy format

        If IsDate(dateFormat) Then

            actualDays = actualDays + 1

        Else

            Exit For

        End If

    Next dayCheck

    ' Redimension the array with the actual number of days in the month
    ReDim daysArr(actualDays)

    ' Populate the array with the correct number of days
    For daysToArray = 1 To actualDays

        daysArr(daysToArray) = daysToArray

    Next daysToArray

    GenererDager = daysArr

End Function

Running the function using the Immediate window with GenererDager(2, 2017) produces the following error message: Compile error: Expected: =

Running the function using the Immediate window with ?GenererDager(2, 2017) produces the following error message: Compiler error: Type mismatch with the very last usage of daysArr selected

The test-sub I'm using to call the function looks like this:

Sub HentDager()

    Dim daysArray() As Integer

    daysArray = GenererDager(2, 2017)

    Debug.Print daysArray(4)

End Sub

Calling this sub in the Immediate window with HentDager() produces the following error: Compile error: Expected: =

I have been pretty much stuck on this problem for a while now, and I have been rewriting the code several times to identify the issue(s), but so far I have been unable to solve it. I might have produced more errors than what I have fixed in the process, as it have dawned on me that I have no clue what I'm really doing right now :-)

Upvotes: 2

Views: 1924

Answers (3)

Sivaprasath Vadivel
Sivaprasath Vadivel

Reputation: 541

few things that seems to be wrong are

  1. your return value of the function is actually dimmed as an integer rather than as integer()

  2. instead of dimming the daysarray as array just dim it as a variant and assign the value

Upvotes: 1

R3uK
R3uK

Reputation: 14537

You have to declare your objects and function as Variant to pass arrays.

Further more, your function needed a few changes :

  • actualDays = actualDays - 1 because you returned the 1st value for which it isn't a date!
  • ReDim daysArr(1 To actualDays) to avoid having an empty value on the 1st index of the array,
    which would have been daysArr(0)

Working function (tested):

Function GenererDager(ByVal month As Integer, ByVal year As Integer) As Variant
    Dim dateFormat As String, daysArr() As Variant, actualDays As Integer, dayCheck As Integer, daysToArray As Integer
    actualDays = 1

    ' Find the number of actual days in given month
    For dayCheck = 1 To 31
        dateFormat = month & "/" & dayCheck & "/" & year ' mm/dd/yyyy format
        If IsDate(dateFormat) Then
            actualDays = actualDays + 1
        Else
            Exit For
        End If
    Next dayCheck
    actualDays = actualDays - 1
    ' Redimension the array with the actual number of days in the month
    ReDim daysArr(1 To actualDays)

    ' Populate the array with the correct number of days
    For daysToArray = 1 To actualDays
        daysArr(daysToArray) = daysToArray
    Next daysToArray

    GenererDager = daysArr
End Function

Tests :

Sub HentDager()
    Dim daysArray() As Variant
    daysArray = GenererDager(2, 2017)
    'Display last day of the month in the immediate window
    Debug.Print daysArray(UBound(daysArray))
End Sub

After a bit of trial and error, here is an example using Integer :

Sub TestIntSub()

    Dim TestInt() As Integer
    TestInt = FctIntArr

    Debug.Print TestInt(1) & "|" & TestInt(2)

End Sub

Public Function FctIntArr() As Integer()
    Dim IntArr() As Integer
    ReDim IntArr(1 To 2)
    IntArr(1) = 545
    IntArr(2) = 232

    FctIntArr = IntArr
End Function

Upvotes: 3

SickDimension
SickDimension

Reputation: 912

You need to define the return type of the function correctly -

Function GenererDager(ByVal month As Integer, ByVal year As Integer) As Integer

Change it to

Function GenererDager(ByVal month As Integer, ByVal year As Integer) As Variant

Upvotes: 2

Related Questions