Help in User Defined Function

[kprit]

I am creating a function in VB Editor (in excel sheet). I call this function by typing =funName() in a cell.

This function is supposed to fetch data from database depending on passed parameters and display fetched data in the Sheet1.

To display data I write "Sheet1.Cells(rowno,1)=DBValue" in function funName() but when I call this function by typing =funName(a,b,c) in a cell, it does not display data in Sheet1 however it is fetching data from database. I tested this by putting recordset in an global array(instead of displaying in sheet1) and display this global array by making another procedure and called this procedure from Tools->Macro->Run.

I want to call the function from cell only [=funName(a,b,c)]

 

[xlbo]

post the code and how you are calling the function (with actual values rather than a,b,c)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[BrainB]

We may need to see all the code for your function (I suggest a new message if so).

The return value of a function has to be the same name as the function, so somewhere in your code you will need
funname =


Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[kprit]

Code I am using is as follows
--- Function I call from sheet is =PopData("a","b&quot

Global conn as ADODB.Connection

Function PopData(a As String, b as string) As Boolean
PopData = False
Call ConnectToDB
Call FetchData(a,b)
PopData = True
End Function

Sub ConnectToDB
---
---
conn.open
End Sub

Sub FetchData(a as string,b as string)
if a = "SSS" then something
if b = "SSS" then something
recrodset = Conn.Execute SQL("SELECT......&quot
Dim rowno
rowno=1
while not recordset.eof
Sheet1.Cells(rowno,1) = RecordSet Fld1 Val
Sheet1.Cells(rowno,2) = RecordSet Fld2 Val
rowno = rowno + 1
recordset.movenext
Wend
End Sub

 

[xlbo]

ok - you are calling Popdata and that is what will try to be returned but:
Function PopData(a As String, b as string) As Boolean
indicates that Popdata can only return a true or false

Also:
Sheet1.Cells(rowno,1) = RecordSet Fld1 Val
Sheet1.Cells(rowno,2) = RecordSet Fld2 Val

will not work from a UDF - functions / formulae in cells CANNOT affect any part of a worksheet other than the cell they are in - you cannot make a UDF write to other cells unless you call it from code

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[kprit]

ohhhh....

Anyway what I did is I put the recordset output in a global array and have created another procedure called showdata which displays arrays output in sheet1. After getting True from PopData I call showdata from Tools->Macro->showdata->run...

Thanks a lot Geoff.

I implemented (saved .xls file as .xla Add-In) above thing in .xla and put the .xla file in Excel's C:\Program Files\Microsoft Office\Office10\XLStart so that I can get =popdata in every workbook.
And it worked perfectly I was able to call =popdata from aby workbook and it returned true but I can't see my showdata procedure in Tools->Macro hence the data which is available in a global array can't be printed in the sheet1.

 

[xlbo]

kprit - sorry if I'm being thick but are yuo saying you have it working or is there a question buried in there somewhere ?? ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244