How to use sub or function array result in NETWORKDAYS formula

[Skiff]

Hello!
Firstly - maybe answer is already somewhere here, but search give only different problems answers.

I've made this neat macro that calculates days free of work and then populates this into new sheet, makes named range from it to use it in NETWORKDAYS formula last parameter.
But I'm always wondering could I not do this by adding another sheet to workbook? I have similar function that tests one day, but for this formula you need all work-free days in a year.

Already tried with similar sub that returns array, but I cannot use array in Excel formula?
So maybe I can add something like named range for array to use in in formula? Or any other way?

Best Regards
Skiff

 

[jasonb75]

Something like this should do it.

Sub test()
Dim v As Variant, v2 As String, n As Long
v = Array(1, 2, 3)
    v2 = "={"
        For n = LBound(v) To UBound(v)
            v2 = v2 & v(n) & ","
        Next
    v2 = Left(v2, Len(v2) - 1) & "}"
        ActiveWorkbook.Names("rng").RefersToR1C1 = v2
End Sub

 

[Skiff]

Thanks! But that's not enough sadly.
Had to change v2 = "={" to v2 = "{" because of an error that said wrong formula

dni_wolne is original subroutine result
dni_wolne2 is new

I'm testing further and try to get those double quotes

EDIT:
Had to make some changes, but it worked. Thanks!

Sub test()
Dim v As Variant, v2 As String, n As Long
v = Array(1, 2, 3)
v2 = "={"
For n = LBound(v) To UBound(v)
v2 = v2 & """" & v(n) & """" & ","
Next
v2 = Left(v2, Len(v2) - 1) & "}"
ActiveWorkbook.Names("rng").RefersToR1C1 = v2
End Sub

I also had to change delimiter since in polish lanugage "," is delimiter for decimal point and in formulas ";" is used. But that's only for languages that uses "," as decimal point delimiter.

Result:

 

[jasonb75]

Dates and regional formats can often be problematic if not dealt with correctly, glad to see you figured it out while I was offline. I should have tested it with actual dates rather than just numbers for simplicity.