Hi
I am new to posting in forums, but I have often had good use of other's q and a's. I havn't found a useful answer to my current problem.
I have recently startet coding again after 10+ years without (since university) so I am a bit rusty, please bear with me. (My Excel is in my local language (danish))
My problem is, that I have a Excel Workbook with lots of similar formulas. That I decide that all needs an "Iferror" around. That's a lot of correcting, and it makes it more difficult to read the formulas.
So I thought I would make a UDF that takes the formula and wrap it in whatever. Many of my formulas are CountIfs and AvarageIfs.
I also use "Name tags/references" (I don't know what they are called in the english version, but I name a column, also to make it easier to read, write and understand the formula).
Example - Cell formula:
"Stage_4_start" is a nametag for a column with a Date.
"BDC" is a nametag for a column with at String to be match with the String in the cell B1. (B1 is a List I can choose from)
The reason for the first if is that "All" is not in the BDC column, but I want them all.
So I wanted to make a UDF a la (pseudo code - don't syntex check):
And call it with:
And then get the string "IFERROR(AVARAGEIFS(Length_stage_1;Stage_4_start;">="&B$2;Stage_4_start;"<"&C$2;Model;"=UDV");"-")"
Then I want this String to be evaluated, surfing a bit gave me a UDF Eval (or similar):
So I thought:
But I have problems both with the first and the second.
I have also tried various use of INDIRECT.
But it dosen't get me were I want and doesn't make my formulas easier to support...
Hope all this is understandable.
Regards
Helle
I am new to posting in forums, but I have often had good use of other's q and a's. I havn't found a useful answer to my current problem.
I have recently startet coding again after 10+ years without (since university) so I am a bit rusty, please bear with me. (My Excel is in my local language (danish))
My problem is, that I have a Excel Workbook with lots of similar formulas. That I decide that all needs an "Iferror" around. That's a lot of correcting, and it makes it more difficult to read the formulas.
So I thought I would make a UDF that takes the formula and wrap it in whatever. Many of my formulas are CountIfs and AvarageIfs.
I also use "Name tags/references" (I don't know what they are called in the english version, but I name a column, also to make it easier to read, write and understand the formula).
Example - Cell formula:
Code:
= IFERROR([INDENT]IF( EXACT($B$1;"All");[/INDENT]
[INDENT=2]AVARAGEIFS([/INDENT]
[INDENT=3]Length_stage_1;
Stage_4_start;">="&B$2;
Stage_4_start;"<"&C$2;Model;"=UDV");[/INDENT]
[INDENT=2]AVARAGEIFS([/INDENT]
[INDENT=3]Length_stage_1;
Stage_4_start;">="&B$2;
Stage_4_start;"<"&C$2;
Model;"=UDV";
BDC;"="&$B$1)[/INDENT]
[INDENT]);[/INDENT]
"-")"BDC" is a nametag for a column with at String to be match with the String in the cell B1. (B1 is a List I can choose from)
The reason for the first if is that "All" is not in the BDC column, but I want them all.
So I wanted to make a UDF a la (pseudo code - don't syntex check):
Code:
Function Wrap (func As String arg As String, bdc As String)[INDENT]Dim TempString As String
Dim bdcString As String
If bdc = "All" Then[/INDENT]
[INDENT=2]bdcString = "" Else
bdcString = bdc[/INDENT]
[INDENT]EndIf[/INDENT]
[INDENT]
TempString = "IFERROR(" & func & bdcString & ";\"-\")"
Wrap = TempString[/INDENT]
End FunctionAnd call it with:
Code:
=WRAP( "AVARAGEIFS", "(Length_stage_1;Stage_4_start;\">=\"&B$2;Stage_4_start;\"<\"&C$2;Model;\"=UDV \")", "All")And then get the string "IFERROR(AVARAGEIFS(Length_stage_1;Stage_4_start;">="&B$2;Stage_4_start;"<"&C$2;Model;"=UDV");"-")"
Then I want this String to be evaluated, surfing a bit gave me a UDF Eval (or similar):
Code:
Function Eval(Ref As String)[INDENT]Application.Volatile[/INDENT]
[INDENT]Eval = Evaluate(Ref)[/INDENT]
End FunctionSo I thought:
Code:
=EVAL(WRAP({All the above}))But I have problems both with the first and the second.
I have also tried various use of INDIRECT.
But it dosen't get me were I want and doesn't make my formulas easier to support...
Hope all this is understandable.
Regards
Helle
