If you want to loop through the values of several arrays and substitute each
"row" of them into the function and evaluate the function separately for each
set of variables, that should work fine.
--
Regards,
Tom Ogilvy
"Evaluate function parameter as VBA code" wrote:
> Dear sir
>
> thanks for your help`~~
> I am afraid that I have NOT describe my problem detailed enough.
>
> As we known that, the variables name in the input string to be evaluated
> must be replace with correct value before be evaluated.
>
> In a cell of excel, if
> =MyFunc( "Log( MyArray(AAA)/MyArray(BBB) + MyArray(CCC) )" )
> is used as a formula.
> AAA, BBB, CCC MyArray should be available in the VBA code, not only just
> a named excell cells, am I right??
> MyArray is an array which is defined in VBA code, not named region in
> excell.....
>
> I want to do the string repleacement as following:
> 1. replace simple value variables, jsut like AAA, BBB, CCC, which type
> should be long, int etc. the string should be be like
> "Log( MyArray(10)/MyArray(20) + MyArray(30) )"
>
> All the simple value variables in a function are known, so I can
> replace them correctly.
>
> 2. replace variables of array, just like "MyArray(xx)"
> S1 = Replace(S1, "MyArray(1)", MyArray(1))
> S1 = Replace(S1, "MyArray(2)", MyArray(1))
> S1 = Replace(S1, "MyArray(3)", MyArray(3))
> S1 = Replace(S1, "MyArray(4)", MyArray(4))
> S1 = Replace(S1, "MyArray(5)", MyArray(5))
>
> I can finish this job in "for i=LBound(MyArray) to UBound(MyArray)",
> and all the variables of array in a function are known.
>
> What do you think?
> Is there a more efficient way to implementate this feature?
>
>
> Thanks a lot~~
> "Tom Ogilvy" wrote:
>
> > > a, b, c are named cells, which are of double type.
> >
> > You didn't say anything about trying to create arrays
> >
> > I also said
> >
> > > The passed function/4th argument must be a valid worksheet function
> > > formula. (you could use it in a cell).
> >
> > Log(MyArray(AAA)/MyArray(BBB) + MyArray(CCC))
> >
> > does not fit that definition.
> >
> > if AAA was a defined name refering to lets say 10 contiguous cells in a
> > column, likewise BBB and CCC, then you could have a function like
> >
> > Function MyFun(S As String) As Variant
> >
> > MyFun = Evaluate(S1)
> > End Function
> >
> > Called from a multicell array entered formula like
> > =MyFun(Log(AAA+BBB/CCC))
> >
> > would return an array.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> >
> > "Evaluate function parameter as VBA code" wrote:
> >
> > > en.....
> > >
> > > Since you use the string replacement like S1 = Replace(S, "AAA", a),
> > >
> > > which means the parameter for Evaluate(S1) cannot contains a variable,
> > > right?
> > > ....
> > > but... how can I do a replace for a array variables?
> > >
> > > just like
> > >
> > > Dim MyArray(100) as double
> > >
> > > Now I want to evaluate "Log(MyArray(AAA)/MyArray(BBB) + MyArray(CCC))". I
> > > tried in Excel, but the string replacment does NOT work....
> > >
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > C isn't a valid name, so I used defined names AAA, BBB, CCC
> > > >
> > > >
> > > > Function MyFun(a As Double, b As Double, _
> > > > c As Double, S As String) As Double
> > > > S1 = Replace(S, "AAA", a)
> > > > S1 = Replace(S1, "BBB", b)
> > > > S1 = Replace(S1, "CCC", c)
> > > > Debug.Print S1
> > > > MyFun = Evaluate(S1)
> > > > End Function
> > > >
> > > > =myfun(AAA,BBB,CCC,"Log(AAA+BBB/CCC)")
> > > >
> > > > returned 1.02802872360024
> > > >
> > > > The passed function/4th argument must be a valid worksheet function
> > > > formula. (you could use it in a cell).
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > >
> > > > "Evaluate function parameter as VBA code" wrote:
> > > >
> > > > > Dear all
> > > > > I want to implementate a feature like this :
> > > > >
> > > > > User type a formula in a cells : =MyFun(a, b, c, "Log(a+b/c"))
> > > > > a, b, c are named cells, which are of double type.
> > > > >
> > > > > the VBA code :
> > > > > Function MyFun(a as double, b as double, c as double, S as string) as double
> > > > >
> > > > > MyFun = Log(a+b/c) ' I want to to get this result, just like evaluate
> > > > > the string S as VBA source code. It the S is changed by user in the formula
> > > > > of the cell, the function can return the correct value.
> > > > > ' Tt's complex since equation parsing is
> > > > > needed to implemente this feature, I just don't know there is a simple method
> > > > > of not
> > > > >
> > > > > End Function
> > > > >
> > > > > Any help or clus is appreciated.
> > > > > Thanks a lot~~
Similar Threads
