A Function puzzle

[davidm]

The following sub works OK when called from a subroutine as shown.

Sub CopyCyclic(num)
Range("a1") = "Q1"
Range("a2") = "Q2"
Range("a3") = "Q3"
Range("a4") = "Q4"
k = 0
For Each c In Range("a5:a500")
If c.Offset(1, 0) = "" Then
c.Value = c.Offset(-4, 0).Value
c.Offset(0, 1).Value = num
k = k + 1
If k Mod 4 = 0 Then num = num + 1
End If
Next
End Sub

Sub test()
[a:a].Clear
CopyCyclic (1980)

However, its function equivalent fails.

Function CopyCyclic(num)
Range("a1") = "Q1"
Range("a2") = "Q2"
Range("a3") = "Q3"
Range("a4") = "Q4"
k = 0
For Each c In Range("a5:a500")
If c.Offset(1, 0) = "" Then
c.Value = c.Offset(-4, 0).Value
c.Offset(0, 1).Value = num
k = k + 1
If k Mod 4 = 0 Then num = num + 1
End If
Next

Next
End Function

Entering *=CopyCyclic (1970*), for example, returns an error? An
error equally results if it is entered as array (Ctr+Shft+Ent) . Just
curious for an explanation.

 

[keepITcool]

a function cannot modify the "environment" like
a sub can.

it simple "goal in life" is to return a (series of) value
based on input.

thus try like:
Sub CopyCyclic()
[a:a].Clear
[a5].Resize(80, 2) = QtrCyclic(1980, 20)
End Sub

you can call it from the sheet too..
enter as a 2 column,x row array function.
(with ctrl/shift/enter)

(if you leave out the years argument
the function resizes the result to
match the calling array


Function QtrCyclic(start&, Optional years&) As Variant
Dim i&, y&, q&, vres
'when called as UDF (entered as array function)
'resize result to size of array
If years = 0 And TypeOf Application.Caller Is Range Then
years = Application.Caller.Rows.Count \ 4
End If

ReDim vres(1 To 4 * years, 1 To 2)

For y = start To start + years - 1
For q = 1 To 4
i = i + 1
vres(i, 1) = "Q" & q
vres(i, 2) = y
Next
Next
QtrCyclic = vres
End Function





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


davidm wrote :