Prev: Running VB6 app on W7 pro 64bit
Next: What Does it Mean Microsoft No Longer Supports VB?
From: DouglasWilliamSmith on 26 Jan 2010 07:48
It's well know that SYNTAX errors in VB code cannot be trapped by ON
ERROR statements. However, for various reasons a procedure of mine
allows user input that is interpreted as executable code. If the user
inputs text that is not syntactically correct, an untrappable syntax
error results and the user is suddenly confronted with a line in the
code pane of the VB window.

The procedure is a a unser defined function for use in Excel 2003
(though I would like to use it in 2007 soon). I would like to know
EITHER
1. (easiest) how it is possible to inhibit the interruption of the
excel session and simply leave vb execution fail (that is, the
function returns some error code to the user in the excel spreadsheet
but the user is not suddenly confronted with a line in the code pane
of the VB window).
2. (impossible?) how to trap the error - perhaps by somehow running a
'compile audit' on the line in question to determine it's
inexecutability before attempting to execute it. The procedure would
then continue to run from the error handling part.

The possibility of user-caused syntax errors is due to a new feature
being added to a substantial procedure, but the problem can be
simplified as follows:

GIVEN: two user defined functions 'Func1' and 'Func2' in module 'A' of
project 'B'
GIVEN: Func2 follows Func1 in the module code

Func1 takes a single string argument, writes code to the second line
of Func2 and then executes Func2 to get its return value:

Function Func1(Func1Input As String)
Set ThisMod = Application.VBE.VBProjects("QTools").VBComponents
("Module1").CodeModule
ThisMod.ReplaceLine ThisMod.ProcBodyLine("Func2", vbext_pk_Proc)
+ 1, "Func2 = " & Func1Input
Func1 = Func2()
End Function

Function Func2()
Func2 = 0 '(initial text of line to allow compiliation before being
rewritten during Fund1 execution)
End Function

If the user enters =func1("10/2") (where "10/2" could be any other
executable text) in an excel sheet cell, the cell returns the value
'5'.

However, if the user enters =func1("10/2 a") (where "10/2" could be
any other non-executable text), the user is suddenly whisked out of
his excel session and is confronted with an error message in the
visual basic window.

Note the WHOLE POINT of allowing user input of code is to allow
ABSOLUTE FLEXIBILITY, such that it is virutally impossible to check
the input text for executability save actually compiling it to see if
it executes.

An alternative that fits the feature's requirements would be to input
the text to be executed as the formula of a cell reserved for vb use
and take the value of this cell as Func1's return value (nonexecutable
text would then result in a trappable error from Excel); however,
during excel calculation cells are nonaccessable so this is no option
for a function.

Another alternative would be to open a hidden, separate instance of
excel in a shell and retrieve the data from that session, but before I
go to that trouble I'd like to know your ideas!

Any ideas?



From: Dee Earley on 29 Jan 2010 04:35
On 26/01/2010 12:48, DouglasWilliamSmith(a)Yahoo.Com wrote:
> It's well know that SYNTAX errors in VB code cannot be trapped by ON
> ERROR statements. However, for various reasons a procedure of mine
> allows user input that is interpreted as executable code. If the user
> inputs text that is not syntactically correct, an untrappable syntax
> error results and the user is suddenly confronted with a line in the
> code pane of the VB window.
>
> The procedure is a a unser defined function for use in Excel 2003
> (though I would like to use it in 2007 soon).

You'll have better luck in a VBA or excel group.
The functionality you're talking about is specific to that environment
and not part of VB.

--
Dee Earley (dee.earley(a)icode.co.uk)
i-Catcher Development Team

iCode Systems
 | 
Pages: 1
Prev: Running VB6 app on W7 pro 64bit
Next: What Does it Mean Microsoft No Longer Supports VB?