ptownbro985
New Member
- Joined
- Apr 3, 2012
- Messages
- 17
This is a tricky one. Let's say I created a user defined function named "MyFunction" with three parameters "param1", "param2", and "param3". What I want to do is find the values passed to each parameter BUT... from another procedure!
Function:
Now, let's say I use the function in cell A1 as (used a weird result to illustrate problem):
I started writing my other procedure as:
The problem is that each paramater could be referring another functions making it difficult to find the last ")". As in:
Extra info for those interested:
What's the real reason why I want to do this? Well... long story, but in brief, what I'm ultimately trying to do is let the user overwrite "MyFunction" with a new value. Then using SheetSelectionChange and SheetChanged events at the application level, I want to save the new value to a back-end database using the parameters passed in the function they overwrote to determine which record to update. Go it all working (not as complicated as I may have made it sound), but just can't figure out how to get the parameters passed.
Function:
Code:
Function MyFunction(param1, param2, param3)
MyFunction = param1 & param2 & param3
End Function
Now, let's say I use the function in cell A1 as (used a weird result to illustrate problem):
Code:
=MyFunction(B1, C1, D1)
I started writing my other procedure as:
Code:
Sub MyProcedure()
Dim MyBeg, MyEnd
MyBeg = Instr(1, Range("A1").Formula, "MyFuction") + Len(Range("A1").Formula)
MyEnd = Instr(MyBeg, Range("A1").Formula, ")")
'... stopped when I realized this won't work (see below)
End Sub
The problem is that each paramater could be referring another functions making it difficult to find the last ")". As in:
Code:
=MyFunction(B1, Upper(C1 & lower(D1)), sum(E1, F1, G1))
Extra info for those interested:
What's the real reason why I want to do this? Well... long story, but in brief, what I'm ultimately trying to do is let the user overwrite "MyFunction" with a new value. Then using SheetSelectionChange and SheetChanged events at the application level, I want to save the new value to a back-end database using the parameters passed in the function they overwrote to determine which record to update. Go it all working (not as complicated as I may have made it sound), but just can't figure out how to get the parameters passed.
Last edited: