Use Cell Formula in VBA code

I have a Function that works in a cell.
=dbFetch("SQL-string";"Database_name";OutputCell;;;)

THis function returns: [Retrieved 1 Rows from dBase....] in the cell where it is placed
It puts the outcome of the DBQuery in the cell defined as OutputCell

THis works!

But: For several reasons it's not that convenient to do all the DBQuery in a worksheet.
It would be better if i could write some VBA code that uses the dbListFetch function
SO: How can i use this in VBA

I tried

Testvar = [dbFetch("SQL-string";"Database_name";OutputCell;;;)]
Testvar value becomes: Error 2015
OutputCell doesn't seem to get a value


Anyone any idea

Please Login or Register  to view this content.

Regards, TMS

Please Login or Register  to view this content.

Hi TMS

I'm not sure you understand my problem
again the formula
=dbFetch("SQL-string";"Database_name";OutputCell;;)

So when i put this formula in Cell A1 and choose C4 as Outputcell then

A1 will show: [Retrieved 1 Rows from dBase....
C4 will show the outcome of the query: e.g. Manchester

So how can i assign the resulting value in the OutputCell to a variable. Can declare a variable and put that in the formula. Something like:

Dim OutputCell as ??
Testvar = "[dbFetch(""SQL-string"";""Database_name"";" & OutputCell & ";;;)]"
MsgBox OutputCell

I'm not sure you understand my problem

I'm sure you're right. I've asked for others to take a look.

What you're doing is essentially using Evaluate, which means Outputcell is just the address of a range and to get the value of that range, you'd have to read the cell, not the variable. So (and I can't say if this will work) it would be:

Please Login or Register  to view this content.

(note that Evaluate expects US style formulas, hence the commas)

Assuming this function comes from a COM add-in, can you set a reference to that add-in? (it may not be COM exposed) If so, you could call the method directly and pass the Strings and Ranges as arguments.