Excel UDF works when called from Sub and doesn't work when called from sheet

Question

I have an UDF function that convert input array of values to a date range. It works when I call it from Sub but when I try to call it from Excel sheet it shows #Value. How I can fix this behavior?

Function GetWorkingDays(WorkingDaysArr() As Variant, MonthYear As String) As String

    Dim Result As String, WorkingDaysCount, ConsecutiveDays, DayStart As Integer
    Result = ""
    WorkingDaysCount = UBound(WorkingDaysArr) - LBound(WorkingDaysArr)
    For i = 0 To WorkingDaysCount Step 1
        If Not IsEmpty(WorkingDaysArr(i)) Then
            ConsecutiveDays = ConsecutiveDays + 1
            If DayStart = 0 Then
                DayStart = i + 1
            End If
        Else
            If ConsecutiveDays > 0 Then
                Dim DayStartStr, DayEndStr As String
                DayStartStr = ConcatDateString(CStr(DayStart), MonthYear)
                DayEndStr = ConcatDateString(CStr(i), MonthYear)
                If DayStartStr <> DayEndStr Then
                    Result = Result + DayStartStr + "-" + DayEndStr + ", "
                Else
                    Result = Result + DayStartStr + ", "
                End If
            End If
            DayStart = 0
            ConsecutiveDays = 0
        End If
    Next i
    Result = Left(Result, Len(Result) - 2)
    GetWorkingDays = Result
    
End Function

Function ConcatDateString(day As String, MonthYear As String) As String

    ConcatDateString = day + "." + MonthYear
    If Len(day) = 1 Then
        ConcatDateString = "0" + ConcatDateString
    End If
    
End Function

Sub test()
Dim varData(31) As Variant
varData(0) = "val"
varData(1) = "val"
varData(2) = "val"
varData(3) = "val"
varData(4) = "val"
varData(27) = "val"
varData(28) = "val"
varData(30) = "val"
MsgBox GetWorkingDays(varData, "06.23")
End Sub

Expected format Result

Where is that UDF defined? In a module, which is used by all sheets, or is it defined inside a specific sheet, which might make it unreachable from other sheets? — Dominique, Jun 16 '23 at 09:34
If the UDF is not defined in a module, the error would be `#NAME?`. — pdtcaskey, Jun 16 '23 at 10:56
@Coilzed: You can simplify your formatting some if you like: `ConcatDateString = String(2 - Len(day), "0") & day & "." & MonthYear`, or eliminate the function and in the main code `DayStartStr = Format(DayStart, "00.") & MonthYear` — pdtcaskey, Jun 16 '23 at 11:44
@pdtcaskey in my opinion problem of you approach is that it's harder to read — Coilzed, Jun 19 '23 at 07:54
@Coilzed: no worries. Efficiency v. readability is always a tradeoff and at the discretion of the programmer! — pdtcaskey, Jun 20 '23 at 13:39

pdtcaskey · Answer 1 · 2023-06-16T11:22:41.590

Your function expects an array (e.g, {1,2,3}), but you provide a range.

Instead of GetWorkingDays(WorkingDaysArr() As Variant,, try GetWorkingDays(WD As Range, and then add WorkingDaysArr = Application.Transpose(WD.Value2) to the code.

If you want to use the function with both, it's more complex: GetWorkingDays(WD, MonthYear As String) then

If TypeName(WD) = "Range" Then
    WorkingDaysArr = Application.Transpose(WD.Value2)
ElseIf TypeName = "Variant()" Then
    WorkingDaysArr = WD
Else
    GetWorkingDays = "Invalid Input"
    Exit Function
End If

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ · Answer 2 · 2023-06-16T15:46:09.270

To be able to call a Function with an argument of type Range or type Variant array, you must have declared the corresponding parameter as Variant type in its definition. Also because within the Function we will need to convert the incoming Range into a one-dimensional array that will have a base of one, while in the other case that it will be called with an array, the array may have a different base. That's why in For-Next we have to use from LBound to UBound and not from 0 to UBound - LBound... Another issue is that if we call with Range it can be A1:A10 or A1:K1. In order for the Function to be general purpose it must solve this internally and the user can call it in any way they want. Beyond these observations there is a problem with the structure of the Function. eg If the statement: [If Not IsEmpty(WorkingDaysArr(i)) Then] is false, then the Else will be executed. There the statement [If ConsecutiveDays > 0 Then] will always be false... eventually the Result will be "" and the procedure [Result = Left(Result, Len(Result) - 2)] will return an error. I won't go into further details because the question is about using Function in two ways:

Option Explicit     'The explicit option solves many errors

Function GetWorkingDays(WorkingDaysArr As Variant, MonthYear As String) As String
   Dim Result As String, ConsecutiveDays As Integer, DayStart As Integer, i As Long
   Dim DayStartStr, DayEndStr As String, trans As Range, lb As Long, ub As Long
   Result = ""
   
   If TypeName(WorkingDaysArr) = "Range" Then
      Set trans = WorkingDaysArr 'use this to pass trans in the next line by reference
      'the definition of the rngTo1Darr is in a group of three functions
      'in an other answer in this site. See the link below
      WorkingDaysArr = rngTo1Darr(trans)
   End If
   'here the WorkingDaysArr have to be of type "Variant()"
   If TypeName(WorkingDaysArr) <> "Variant()" Then
      MsgBox ("GetWorkingDays> WorkingDaysArr parameter accept only 'Variant()' and 'Range' data types")
   End If
   lb = LBound(WorkingDaysArr)
   ub = UBound(WorkingDaysArr)
   
   For i = lb To ub
      If Not IsEmpty(WorkingDaysArr(i)) Then
         ConsecutiveDays = ConsecutiveDays + 1
         If DayStart = 0 Then
            DayStart = i + 1
         End If
      Else
         'if code comes here the next line is always FALSE and Result stays ""
         'i don't think it is what you really need
         If ConsecutiveDays > 0 Then
            DayStartStr = ConcatDateString(CStr(DayStart), MonthYear)
            DayEndStr = ConcatDateString(CStr(i), MonthYear)
            If DayStartStr <> DayEndStr Then
               Result = Result + DayStartStr + "-" + DayEndStr + ", "
            Else
               Result = Result + DayStartStr + ", "
            End If
         End If
         DayStart = 0
         ConsecutiveDays = 0
      End If
   Next i
   'check the lenght else maybe get error...
   If Len(Result) >= 2 Then
      Result = Left(Result, Len(Result) - 2)
   End If
   GetWorkingDays = Result
End Function

Function ConcatDateString(day As String, MonthYear As String) As String
   If Len(day) = 1 Then
      ConcatDateString = "0" + ConcatDateString
   Else
      ConcatDateString = day + "." + MonthYear
   End If
End Function

So you can call from VBA: MsgBox GetWorkingDays(varData, "06.23")

and from a cell:

=GetWorkingDays(A1:A10, "06.23")

=GetWorkingDays(A1:G1, "06.23")

Here is the link for a group of three functions used in code: https://stackoverflow.com/a/76365487/15794828

Excel UDF works when called from Sub and doesn't work when called from sheet

2 Answers2