Way too long of a function but I need it - can it be done in VBA?

Question

Because of severe lack of knowledge, I made a ridiculously long function so that I could make my calculation. The problem is that it is too long for Excel, and I tried looking online to see how I could maybe make a new function in VBA that referenced my function. I'm super lost on this one and any help would be awesome. The function would just be too messy to post here (it is 30k characters long).

Ok so here it goes - here's a part of the function: =+IF(ISERROR(IF(LEFT(C12,FIND(" ",C12,1))=$C$2,SUMPRODUCT(P12:S12,Selection!$B$4:Selection!$E$4),IF(LEFT(C12,FIND(" ",C12,1))=$C$3,SUMPRODUCT(P12:S12,Selection!$B$5:Selection!$E$5),IF(LEFT(C12,FIND(" ",C12,1))=$C$4,SUMPRODUCT(P12:S12,Selection!$B$6:Selection!$E$6),IF(LEFT(C12,FIND(" ",C12,1))=$C$5,SUMPRODUCT(P12:S12,Selection!$B$7:Selection!$E$7),IF(RIGHT(C12,LEN($C$6))=$C$6,SUMPRODUCT(P12:S12,Selection!$B$8:Selection!$E$8),IF(RIGHT(C12,LEN($C$7))=$C$7,SUMPRODUCT(P12:S12,Selection!$B$9:Selection!$E$9),IF(RIGHT(C12,LEN($C$8))=$C$8,SUMPRODUCT(P12:S12,Selection!$B$10:Selection!$E$10),SUMPRODUCT(P12:S12,Selection!$B$11:Selection!$E$11))))))))),1,IF(LEFT(C12,FIND(" ",C12,1))=$C$2,SUMPRODUCT(P12:S12,Selection!$B$4:Selection!$E$4),IF(LEFT(C12,FIND(" ",C12,1))=$C$3,SUMPRODUCT(P12:S12,Selection!$B$5:Selection!$E$5),IF(LEFT(C12,FIND(" ",C12,1))=$C$4,SUMPRODUCT(P12:S12,Selection!$B$6:Selection!$E$6),IF(LEFT(C12,FIND(" ",C12,1))=$C$5,SUMPRODUCT(P12:S12,Selection!$B$7:Selection!$E$7),IF(RIGHT(C12,LEN($C$6))=$C$6,SUMPRODUCT(P12:S12,Selection!$B$8:Selection!$E$8),IF(RIGHT(C12,LEN($C$7))=$C$7,SUMPRODUCT(P12:S12,Selection!$B$9:Selection!$E$9),IF(RIGHT(C12,LEN($C$8))=$C$8,SUMPRODUCT(P12:S12,Selection!$B$10:Selection!$E$10),SUMPRODUCT(P12:S12,Selection!$B$11:Selection!$E$11)))))))))

Erik it's a little hard to help you with so little detail, could you at least present us what is the goal that the function is trying to achieve ? — Pedro Braz, Mar 12 '15 at 20:43
Post a snippet and the general idea, is it a massive nested IF or something? — Dan Donoghue, Mar 12 '15 at 21:15
I'd suggest group your code in blocks. For every block create a sub routine (function / sub depending on your need). which will execute a part of your whole calculation. Then in the end, create a main function that will call end execute those chunks of routine. — L42, Mar 12 '15 at 23:40

nateAtwork · Answer 1 · 2017-11-28T23:21:56.287

To answer your question "Can it be done in VBA?", the answer is yes. If you can do it with excel functions you can do it with VBA. There may be functional disadvantages though, and coding the function will have completely different syntax (although it is usually pretty easy to translate using google searches).

One thing to consider before you go to VBA though is could the function be broken up into multiple cells? This might suit your needs and get around the character limit, although if it's 30k characters long this might not be practical or even possible to do this.

I would recommend starting out by literally googling "VBA equivalent of excel function XXXX" for each excel function you use. Then work your way inside out from the middle parentheses to perform operations on the inputs in the same order as your excel function. The main difference between VBA functions and Excel functions is that you can perform operations on the same variable line by line instead of using complicated order of operations.

For example, instead of putting =if(a2>3,b3+5,b3-5)*if(A1>3,B2+3,B2-3), you could put:

Function Your_Function_Name1(Cell_one As Range, Cell_two As Range, _
                            Cell_three As Range, Cell_four As Range) As Double
    If Cell_four > 3 Then
        If Cell_three > 3 Then
            Your_Function_Name1 = (Cell_one.Value + 5) * (Cell_two.Value + 3)
        Else
            Your_Function_Name1 = (Cell_one.Value - 5) * (Cell_two.Value + 3)
        End If
    Else
        If Cell_three > 3 Then
            Your_Function_Name1 = (Cell_one.Value + 5) * (Cell_two.Value - 3)
        Else
            Your_Function_Name1 = (Cell_one.Value - 5) * (Cell_two.Value - 3)
        End If
    End If
End Function

and call by =Your_Function_Name1(B3,B2,A2,A1). But it is also perfectly legitimate and usually easier to do this instead:

Function Your_Function_Name(Cell_one As Range, Cell_two As Range, _
                            Cell_three As Range, Cell_four As Range) As Double
    If Cell_three > 3 Then
        Your_Function_Name = Cell_one.Value + 5
    Else
        Your_Function_Name = Cell_one.Value - 5
    End If

    If Cell_four > 3 Then
        Your_Function_Name = Your_Function_Name * (Cell_two.Value + 3)
    Else
        Your_Function_Name = Your_Function_Name * (Cell_two.Value - 3)
    End If

End Function

Both of these functions would be called the same way and yield the same result.

I think that should be enough to get you started, although you will probably end up be posting another question or two once you get into it and start debugging, but at least you will have specific code to ask about. VBA is hard at first but it is worth the time you put into it.

Good Luck!

score 0 · Answer 2 · answered Mar 12 '15 at 22:44

The following guideline is a great way both to refactor your existing code, and to write new code in future:

For every block of code that has, or is big enough to have, a descriptive comment, make a subroutine and name it (in PascalCase) with the descriptive comment. Identify all local variables and redeclare them in the new subroutine. Pass in all global values as named parameters.

Rinse and Repeat until all subroutines are less than 40 lines or so.

score 0 · Answer 3 · edited Oct 30 '17 at 00:12

You can cut your function in half by using IFERROR rather than IF(ISERROR also, your Selection!$B$4:Selection!$E$4 can be reduced to Selection!$B$4:$E$4

=IFERROR(IF(LEFT(C12,FIND(" ",C12,1))=$C$2,SUMPRODUCT(P12:S12,Selection!$B$4:$E$4),IF(LEFT(C12,FIND(" ",C12,1))=$C$3,SUMPRODUCT(P12:S12,Selection!$B$5:$E$5),IF(LEFT(C12,FIND(" ",C12,1))=$C$4,SUMPRODUCT(P12:S12,Selection!$B$6:$E$6),IF(LEFT(C12,FIND(" ",C12,1))=$C$5,SUMPRODUCT(P12:S12,Selection!$B$7:$E$7),IF(RIGHT(C12,LEN($C$6))=$C$6,SUMPRODUCT(P12:S12,Selection!$B$8:$E$8),IF(RIGHT(C12,LEN($C$7))=$C$7,SUMPRODUCT(P12:S12,Selection!$B$9:$E$9),IF(RIGHT(C12,LEN($C$8))=$C$8,SUMPRODUCT(P12:S12,Selection!$B$10:$E$10),SUMPRODUCT(P12:S12,Selection!$B$11:$E$11)))))))),1)

Now that worked for me, but your test of LEFT(C12,FIND(" ",C12,1))=$C$2 seems suspect. If C12 contain Cat in the House, the left side would evaluate to "Cat " with a space on the end. That would be fine in the cells you are testing against contain a space on the end, but I would guess they don't. You might want to make the text LEFT(C12,FIND(" ",C12,1)-1)=$C$2

Way too long of a function but I need it - can it be done in VBA?

3 Answers3