im writing a function for excel, and i have three arguments, two are integer variables, and the last is a string variable used to store a mathematical expression which needs to be able to use one of the variables previously mentioned to do math. is there a way to do this? and i'm pretty sure im using the wrong variable type for the expression, which is the correct one?
Look into the Worksheet. (or Application.) Evaluate method.
Last edited by leelnich; 03-05-2018 at 01:45 PM.
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
My code looks like this (it doesn't work) :
Please Login or Register to view this content.
here is my workbook.
You aren't actually passing an equation, but the result of it, so this should suffice
Please Login or Register to view this content.
Don
Please remember to mark your thread 'Solved' when appropriate.
I am trying to have an incremented input (i) be run through an expression (Equation) (n) times then have all (n) outputs be added together.
it should be something like this:
=SIGMA(9,1,2*i)
^^type in cell^^
(2*1)+(2*2)+(2*3)+(2*4)+(2*5)+(2*6)+(2*7)+(2*8)+(2*9)
^^Calculation Steps^^
=90
^^Returns^^
Last edited by Batnoob; 03-05-2018 at 11:54 PM. Reason: Clarification
Hello Batnoob,
Will the user be able to change the equation when using this function?
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Please Login or Register to view this content.
Ben Van Johnson
Yes, the user should be able to change the equation. the equation is an argument so user should be able to input anything e.g.Originally Posted by Leith Ross
=SIGMA(9,1,2*i)
=SIGMA(12,-7,4*i+6)
etc.
Last edited by Batnoob; 03-05-2018 at 11:58 PM.
Ben, like the rest of everything i tried, i just got a #Value error, when i tried your suggestion.
PS, I clarified things in my previous explanatory post
If Equation is passed as a string, it must be entered as such - with QUOTES. In a cell: =SIGMA(5,1,"4*i+6")
And here's the UDF (which assumes you actually wanted to use the incremented "a" value, not the static "i" value):BTW, your original code was needlessly recursive. Just accumulate the value in the loop and assign it to SIGMA after.Please Login or Register to view this content.
Last edited by leelnich; 03-06-2018 at 02:55 AM.
There was no recursion in the original code. This line
is not recursive- Sigma is effectively a local variable not a function call.Please Login or Register to view this content.
I tested that- and you are correct. But I confess to not understanding why. Is it because the syntax lacks parenthesis - or parameters for that matter?
Regardless of the answer, the extra variable can be dropped:Please Login or Register to view this content.
Last edited by leelnich; 03-06-2018 at 12:07 PM.
Yes, without the brackets it's just a variable- as when you use it to return the value at the end.
Thanks for pointing it out. I never really considered that implication when using functions - some recursive - in the past.
You're welcome.
I like your avatar- I often feel like hitting my laptop like that to make the code do what I want.
Like iron smithing, the process of forging code is learned by observing the properties of your raw materials ... or something like that.
i am so confused. so i want the user to be able to type in the cell
Formula:
Please Login or Register to view this content.
and it calculates (in this case to 6, because 2*1+2*2=6), but instead everything i've gotten returns #VALUE
does the user have to wrap the variable that they put in the equation with special symbols to correctly pass it to VBA (kind of like batch %var% or POSH's $var)?
:: BatNoob
' BatNoob
/* BatNoob*/
As leelnich said, they will have to put the equation itself in quotes.
This goes in a cell:
=SIGMA(2,2,"2*i")
...and this is your User-Defined Function (macro):Please Login or Register to view this content.
Last edited by leelnich; 03-08-2018 at 02:39 AM.
thank you both!+1 reputation each.
You're most welcome, thank you for the rep! If concluded, please mark your thread as SOLVED (Thread Tools above post #1). Regards – Lee
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks