For Loop UDF

[Gruuuu]

Growing tired of crazy Excel calisthenics to display certain data, I decided to develop a For Loop UDF. It works ok, if yet a little clunky. Here's the code:

Function FORLOOP(
ByVal string_variable As String, 
ByVal start_value As Long, 
ByVal value_limit As Long, 
ByVal value_increment As Long, 
ByVal string_formula As String, 
ByVal string_separator As String, 
ByVal application_volatile As Boolean)

Dim i As Long

If application_volatile Then Application.Volatile

For i = start_value To value_limit - 1 Step value_increment
    FORLOOP = FORLOOP & Evaluate(Replace(string_formula, "#" & string_variable, i)) & string_separator
Next i

    FORLOOP = FORLOOP & Evaluate(Replace(string_formula, "#" & string_variable, i))
End Function

Use:

=FORLOOP("a",3,12,1,"OFFSET(B1,0,#a)","|",FALSE)
[i]Displays E1 through M1, pipe delimited[/i]

Like I said, it works ok. My biggest beef is that there are so many arguments, and it's difficult (yet possible and quick) to increment horizontally (with OFFSET()).

My question:
What do you guys think, can I ditch some of these arguments?
and also:
Can I make this more robust without losing efficiency?

 

[PHV]

You may use Optional arguments with default values.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

You could make this function less general and build-in a part of arguments. Finally you could have the following arguments:
1) ref_range (not present now, E1 in the example),
2) val_limit,
3) val_increment,
4) Optional dir_horizontal=True,
5) Optional app_volatile=False (say).
You loose start value (E1 could be pointed instead) and formula selection, but get direction instead and 3 arguments in best case.

combo

 

[Gruuuu]

You could make this function less general...

While it's true this would be most useful in returning references, I like the idea of having the flexibility. But I did seriously consider using it this way. The rest of your solution looks very sharp, too. If I were going to specialize this to only use cell references, I would definitely be doing it this way.

I did remove the variable name, and it now requires a specific string in its place (#x)

With that, the optional parameter route cleaned it up enough for my tastes, I think, with minimum 4 parameters:

Function FORLOOP(ByVal start_value As Long, ByVal value_limit As Long, ByVal value_increment As Long, ByVal string_formula As String, Optional ByVal string_separator As String, Optional ByVal application_volatile As Boolean)

Dim i As Long
'Debug.Assert 0
If string_separator = "" Then string_separator = ", "

If application_volatile Then Application.Volatile

If InStr(string_formula, "#x") = 0 Then
    FORLOOP = "Must use #x as variable"
    Exit Function
End If

For i = start_value To value_limit - 1 Step value_increment
    FORLOOP = FORLOOP & Evaluate(Replace(string_formula, "#x", i)) & string_separator
Next i

    FORLOOP = FORLOOP & Evaluate(Replace(string_formula, "#x", i))
End Function

Examples:

=FORLOOP(2,8,1,"text(#x,""dddd"")")
[i]Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday[/i]

1	2	3	4	5	6
A	B	C	D	E	F
=FORLOOP(COUNTA($1:$1)-COUNTIF($1:$1,">2"),COUNTA($1:$1)-1,1,"OFFSET($A$2,0,#x)")
[i]C, D, E, F[/i]

=FORLOOP(32,64,1,"CHAR(#x)"," ")
[i]  ! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ? @[/i]