For various reasons a user-defined function of mine needs to allow
user input that is interpreted as executable code (in this case any
excel expression).
The most straight forward approach would be for the function procedure
to input code into a module active in the same excel session and
subsequently run the code (ie call a sub to which the user-input code
was written); however, there are two problems with this:
A. Most importantly is a very weird problem with how excel access user-
defined functions in VBA modules. If a function procedure makes ANY
change to ANY module active in the same session, excel 'loses' the
relationship of all the other user-defined functions in the same
module.
The problem is best described by doing it:
0. Open a new session of excel
1. in VBA, add a module to Book1
2. In the new module, write a inoccuous function as the 'control' of
the experiment:
Function innocuous()
innocuous = "innocuous"
End Function
3. in excel, Input =innocuous() in cells A1 and B1 of Book1
(the function returns the string 'innocuous' in each cell; this is
just a control for later steps)
4. Now input the following code in module1:
Function MyCodeInput(InputUserCode)
Application.VBE.VBProjects("VBAProject").VBComponents("Module1").CodeModule.ReplaceLine
_
Application.VBE.VBProjects("VBAProject").VBComponents("Module1"). _
CodeModule.ProcBodyLine("RunUserCode", vbext_pk_Proc) + 1,
_
"RunUserCode = " & InputUserCode
MyCodeInput = RunUserCode()
End Function
Function RunUserCode()
RunUserCode = "not input yet"
End Function
NB: the inputs "VBAProject" and "Module1" assume you have not changed
the default names for the new project and module you created in step 1
The Function MyCodeInput access the code object of VBA and overwrites
the first line of code in the procedure 'RunUserCode'
5. In excel, input =MyCodeInput("2") in cell A2
During returning input into cell A2, the function writes code in
RunUserCode to get it to return the user input
('2' in this case) and subsequently runs RunUserCode to get the
value it is to return to cell A2 (2)
!!>You will see that the functions in cell A1 and B1 immediately
return #NAME?
(assuming you have calc set to automatic - otherwise you will have
to press {F9})
6. Still in excel, input =MyCodeInput("3") in cell A3
The function returns 3 in cell A3 BUT
!!>You will see that the entry in cell A1, B1 and A2 immediately
return #NAME?
7. Go back to A2 and reinput =MyCodeInput("2")
The function returns 2 in cell A2 BUT
!!>You will see that the entry in cell A1, B1 and A3 immediately
return #NAME?
My interpretation: whenever a procedure writes code to ANY active
module in an excell session, excel 'loses' its access to ALL modules
(the above example doesn't prove this for all modules but further test
you can think of for yourselves will bear this out).
The particular occurence of a function in a cell gets recalculated
upon input, but if the function writes code, then ALL other cells
containing user-defined functions will revert to returning #NAME? as
though the functions are not defined in any active modules.
As long as the other cells are returning #NAME?, excel does not even
bother to try to run the function they contain, so that subsequent
recalcs fail to renew excel's access to the functions. The only way to
get the function in such cells to calculate is to re-enter it in one
of the cells, but then all the other cells containing user-defined
functions once again rever to returning #NAME?
B. Problem A is a pretty major problem with coding any function to
write code during execution! However, if the user inputs text that is
not syntactically correct (say s/he enteres "2/" rather than "2"), an
untrappable syntax error results and the user is suddenly confronted
with a line in the code pane of the VB window. This is a further
problem.
An alternative would be to input the text to be run as code as the
formula property of a cell reserved for vb use
and take the value of this cell as Func1's return value (giving
nonexecutable text for the formulat property of a cell would then
result in a trappable error from Excel). Of course, only code that can
be interpreted as excel expressions can be 'run' this way but this
fulfills my particular needs for the function in question; however,
during excel calculation cells are nonaccessable so this is no option
for a function.
Another alternative would be to have an separate instance of excel
running at all times, watching to see if a file with a certain name
gets written. MyCodeInput would then not write input code to a
procedure, but to a file of this name, and then wait until this file
ceases to exist.
The other session of excel would read the code immediately upon seeing
the file exists, input the code into the formula property of a cell
(this would be no problem in the other session as a function would not
be in the process of being input in this session), write the result
returned to that cell as a value in a file 'B' of another name, and
delete the first file it read from.
MyCodeInput would see the file it wrote to had ceased to exist, read
the value in the file 'B' the other session had just written and
return it to the user.
It all seems like a truly crazy work around and I wonder whether there
isn't an easier way! Note DDE functions also don't work during the
input of a function so this is another no-go.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
