"Javier Ortega" wrote in message
...
I'm sorry about the code. here you a

In a Excel Formula you put in for example:

=lista("select * from table1")

Lista check and prepare de SQL and fit the optional parameters and then it
calls the other function "CreaConsulta".

When in CreaConsulta is reached the Refresh, automatically is called lista
function again.

I found a person that have the same problem, you can see the following
link:

http://p2p.wrox.com/topic.asp?TOPIC_ID=669


And other thing I have probed the application.enableevents = false but it
doesn't work.

Any ideas?
Thank you.


I still don't uderstand the logic. I really think you need to tell what you
want to solve. To me, it only makes sense to have a function with an SQL
statement that does fetch exacly one value and returns that retrieved value.
In this case, it seems as if you get multiple records and I have no idea
where that recordset goes.

What optional parameters are there. The statement lokks perfectly valid to
me except for the fact that it may return many rows and many columns.

You have to show the entire code AND an explanation of what you want to do.
I am sure someone from this group can come up with a descent implementation.
I will have a look at the link you submitted.

/Fredrik

Hi Fredrik,

The function use in the Formula has to do only one thing. It has to create a
QueryTable to return all the rows and columns in the recordset.

The parameters in the function lista a

text with the SQL
optional par1 = Destination cell where the Query Table will be inserted
optional par2 = true or false if you want headings or not in the QueryTable
And then the function lista has to return TRUE or FALSE if the QueryTable is
created ok or not. By this way you get a returned value for the formula in
Excel and get the result of the Query.

The probem is that when you get at the point .Refresh(false), insted of
continuing the running code, Excel begins to execute again lista function.

If you have only one of these formulas in you Excel, it works ok because in
the second pass, the code is runned completely. But imagine you have 3
formulas like these with diferent selects.

The running order will be:

lista (select1) - run CreaConsulta (select1) - When it arrives at the
point when the Refresh is called, suddenly goes to the following formula, it
is, lista (select2) and then it goes to CreaConsulta (select2) but without
finishing the code of the first formula, and by this way it goes on and on
till the last formula that is runned completely so i get only 1 result from
all i have in my worksheet.
To solve that I have to excute each formula by editing the formula and hit
Return key for each of them.

It seems to be a bug of Excel or that something is controlling some events.
But I think that functions must be finished before executing other.

Thank you.

"Javier Ortega" wrote in message
...
Hi Fredrik,

The function use in the Formula has to do only one thing. It has to create
a
QueryTable to return all the rows and columns in the recordset.

The parameters in the function lista a

text with the SQL
optional par1 = Destination cell where the Query Table will be inserted
optional par2 = true or false if you want headings or not in the
QueryTable
And then the function lista has to return TRUE or FALSE if the QueryTable
is
created ok or not. By this way you get a returned value for the formula in
Excel and get the result of the Query.

The probem is that when you get at the point .Refresh(false), insted of
continuing the running code, Excel begins to execute again lista function.

If you have only one of these formulas in you Excel, it works ok because
in
the second pass, the code is runned completely. But imagine you have 3
formulas like these with diferent selects.

The running order will be:

lista (select1) - run CreaConsulta (select1) - When it arrives at the
point when the Refresh is called, suddenly goes to the following formula,
it
is, lista (select2) and then it goes to CreaConsulta (select2) but without
finishing the code of the first formula, and by this way it goes on and on
till the last formula that is runned completely so i get only 1 result
from
all i have in my worksheet.
To solve that I have to excute each formula by editing the formula and hit
Return key for each of them.

It seems to be a bug of Excel or that something is controlling some
events.
But I think that functions must be finished before executing other.

Thank you.



This looks very complex. I think your whole idea is flawed. Is there any
sample sheets around with logic similar to yours. The problem described in
the link wasn't very useful. I assume ApplyFormula was a user defined
function but there was no code which made it pretty incomprehensible. I
failed to understand the formula =ApplyFormula($D$1:$E$1
[c1],"Products_List" [c2],"AutoQryTable_1" [c3],TRUE)

What does [c2] mean? What tabout the other parameters? And where is the code
for Applyformula?

Anyway, as far as i can understand, you want to put the result of your query
into some range. That's not something that you should do from a cell
function. I think you should crate a Sub that does this. By doing so, you
will only insert constants which won't cause any kind of recalculation
problem. I still miss a verbal explanation of what you want to accomplish.

/Fredrik

Hi Fredrik,

We need to create a QueryTable because there are other formulas that are
referenced to the data in the Query. That is the reason we need to make a
QueryTable instead of copying the values.

We put in a excel formula a user defined function (function 1), this
function calls another one to create a Query Table (function 2). The correct
order is the following:

1st - lista function is called and executed. It calls CreaConsulta funtion.
2nd - CreaConsulta makes a QueryTable, if it is created ok, it returns TRUE
if not it returns FALSE.
3rd - The code returns to the function lista in order to end the rest of
the code that is to return de value returned by CreaConsulta in the value
property of the cell in excel.

For example: You put in A1 the following lista("select * from table1")
lista calls CreaConsulta and creates the QueryTable, if its created A1.value
will be TRUE otherwise will be FALSE. And if the QueryTable has been created,
it will be in B1 cell.

The problem is only that when you get to the refresh method in CreaConsulta,
insted of continuing the code from that point, Excel interrups the executing
code in order to begin executing again the function lista from the beginning
and that reaction makes that the code is not executing correctly because
there is always part of the code without being runned.