Microsoft Developer Network >
Forums Home
>
Microsoft ISV Community Center Forums
>
Visual Basic for Applications (VBA)
>
Using vba for custom formulas
Using vba for custom formulas
- I have a file with huge formulas and nested if statements, they are excessive and hard to understand. I was thinking of using vba for these formulas, but notice that when i use a vba function, it gets called over and over again for each cell ( i think) is this the only alternative?
what are the downfalls of using vba functions? compared to formulas?
McC
Answers
- John,
What i used to do when i first started out was to break the if statements into individual cell formulas. so i would check for one condition in 1 cell and then based on that result i would use the next cell to check a condition based on the first cell.
mostly i used that way to help me figure out how to make a more complex if function in one cell. this may help you make things more simple though.
but aside from that, if you are using vba code and it is running over and over it may be an issue with how you coded it and in what event. if you could post the code or give and example of it and explain what event you have it in, etc... more details about it.
i think for the pro and con, you have seen the con of the functions in the cells. security yes, will be an issue with vba. but any program you use is a security issue if the program is maliscious. you users need to trust you.
a pro for vba is being able to create complex function and break them out in a readable way. you have a great deal of control in code that you do not in the cells. and you can comment the code very well in vba to be sure you understand what you did if you need to come back months or even years later.
if you can give some more details, we can help more
FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial- Marked As Answer byJohn Basedow Tuesday, November 10, 2009 9:21 PM
- Marked As Answer byJohn Basedow Friday, November 13, 2009 3:08 PM
- Unmarked As Answer byJohn Basedow Thursday, November 12, 2009 2:25 PM
All Replies
- 1. Writing code.
2. User should have their excel security lovered.
I think you better use vlookup instead of nested IF.
Hope this help. - John,
What i used to do when i first started out was to break the if statements into individual cell formulas. so i would check for one condition in 1 cell and then based on that result i would use the next cell to check a condition based on the first cell.
mostly i used that way to help me figure out how to make a more complex if function in one cell. this may help you make things more simple though.
but aside from that, if you are using vba code and it is running over and over it may be an issue with how you coded it and in what event. if you could post the code or give and example of it and explain what event you have it in, etc... more details about it.
i think for the pro and con, you have seen the con of the functions in the cells. security yes, will be an issue with vba. but any program you use is a security issue if the program is maliscious. you users need to trust you.
a pro for vba is being able to create complex function and break them out in a readable way. you have a great deal of control in code that you do not in the cells. and you can comment the code very well in vba to be sure you understand what you did if you need to come back months or even years later.
if you can give some more details, we can help more
FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial- Marked As Answer byJohn Basedow Tuesday, November 10, 2009 9:21 PM
- Marked As Answer byJohn Basedow Friday, November 13, 2009 3:08 PM
- Unmarked As Answer byJohn Basedow Thursday, November 12, 2009 2:25 PM
- My bad, I just did a test formula:
Public Function TestExcelPrivateFormula(i1 As Integer, i2 As Integer, i3 As Integer, i4 As Integer)
Static b As Integer
b = b + 1
TestExcelPrivateFormula = i1 + i2 + i3 + i4
End Function
it only runs when this formula is added to a cell or a value is changed in the cells the formula refers too
McC
