Michael

It depends on what you want to return. Do you want to get one field from
one row, or are you planning on using an array formula to return all of the
fields? If you macro works now, then your almost there. You need to define
what arguments you're going to pass to the UDF. For instance, you might do
this

Function GetMySql(strWhere as String, lngField as Long) as Variant

then change this line

mySQL = "SELECT number FROM master where id = " & Range("C2").Value

to

mySQL = "SELECT number FROM master where id = " & strWhere

Then you don't need to loop through all the fields because you're simply
returning one value to one cell. Instead, you would have something like

myrs.MoveFirst
GetMySql = myrs.Fields(lngField).Value

You should only need a MoveFirst, because there should only be one record in
your recordset.

Now to fill a whole table, you might use the UDF like this

=GetMySql($A1,Column())

With your unique Id in A and your table starting in B2, you can copy this
formula as far left and down as you need. I think this may be particularly
slow however.

Post back if that doesn't hit the mark.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Michael J. Malinsky" wrote in message
...
I know that VBA can be used to read/write info from/to a mysql database
to/from an Excel worksheet using a Sub routine and putting the results of
a
query into a recordset then pasting the recordset into a cell on the
sheet.
What I'm wondering is if a UDF can be used in a similar manner. The query
uses a table with unique values, so having a result with more than one
value
would not occur. This is the code I have that does what I want, but I'd
like this to be in a UDF. I've tried and failed and would greatly
appreciate any assistance.

Private Sub test()

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long
Dim lString As String
Dim iRow As Integer
Dim iCol As Integer

myconn.Open "DSN=MySQL EPACE"
mySQL = "SELECT number FROM master where id = " & Range("C2").Value
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.LockType = adLockOptimistic
myrs.CursorType = adOpenKeyset
myrs.Open , , adCmdTable

myrows = myrs.RecordCount
iRow = 1
iCol = 1
x = 0

Worksheets("Sheet2").Range("A2").Select

With myrs
If .BOF Then GoTo NODATAHERE

'GET FIELD NAMES
For Each AField In .Fields
lString = AField.Name
Cells(iRow, iCol) = lString
iCol = iCol + 1
Debug.Print lString
Next
iRow = iRow + 1
iCol = 1

'COLLECT DATA
.MoveFirst
While Not .EOF
For Each AField In .Fields
Cells(iRow, iCol) = .Fields(x).Value
x = x + 1
iCol = iCol + 1
Next
iRow = iRow + 1
x = 0
iCol = 1
.MoveNext
Wend

End With

NODATAHE

myrs.Close
myconn.Close

End Sub

TIA

--
Michael J. Malinsky