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

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

Dick,

Thanks for the response, but I'm still a bit confused. I've made the
changes you suggested, but I'm not sure why you are having me define two
variables , strWhere and lngField). I'm assuming strWhere would be the
value I want to use for the value of id, but I don't know where the lngField
fits in. Here is the code I have based on your recommendations. I am
getting an #VALUE error when using the formula:

=GetMySql(B1)

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

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long

myconn.Open "DSN=MySQL TEST"
mySQL = "SELECT number FROM master where id = " & strWhere
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

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
End With

'COLLECT DATA
myrs.MoveFirst
GetMySql = myrs.Fields(lngField).Value


NODATAHE

myrs.Close
myconn.Close

End Function


Thanks for your help and looking forward to your response.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
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

Michael

The lngField argument is the Field you want to return. A UDF returns one
value to the cell in which it is entered. The problems that you are
experiencing are 1) you aren't passing enough arguments to the UDF and 2)
you're trying to write to cells. With a UDF, you can't change the values of
any cells, rather the value you assign to the function name is put into the
cell into which the function was entered. Think of it in terms of the
built-in worksheet functions in Excel. You can't enter a build-in function
(like SUM) in one cell and have it change the value in another cell - it's
the same with UDF's. So this part

'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
End With

doesn't work with a UDF because you're changing the Excel environment,
namely Cells(iRow, iCol).Value, when a UDF can only return a value.

The first thing you need to do is evaluate whether or not a UDF is really
what you want. If you want something that is going to produce column
headers, then you probably don't want a UDF. But, if you want to change
(from your example) B1 and have it show a different result (presumably from
a different record) then a UDF is probably a good fit.

Based on my limited knowledge of what you're trying to accomplish, I offer
the following suggestions;

Use a Worksheet_Change event so that when you change a particular cell, the
Sub runs and returns different results.
Use two UDFs: one for column headings and one for data.
Use an array-entered UDF (or two) to get all the data in the cells at once.

Tell me why you want to use a UDF and maybe we can find the best way to get
to the same result.

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

"Michael J. Malinsky" wrote in message
...
Dick,

Thanks for the response, but I'm still a bit confused. I've made the
changes you suggested, but I'm not sure why you are having me define two
variables , strWhere and lngField). I'm assuming strWhere would be the
value I want to use for the value of id, but I don't know where the
lngField
fits in. Here is the code I have based on your recommendations. I am
getting an #VALUE error when using the formula:

=GetMySql(B1)

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

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long

myconn.Open "DSN=MySQL TEST"
mySQL = "SELECT number FROM master where id = " & strWhere
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

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
End With

'COLLECT DATA
myrs.MoveFirst
GetMySql = myrs.Fields(lngField).Value


NODATAHE

myrs.Close
myconn.Close

End Function


Thanks for your help and looking forward to your response.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
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

Dick,

I have a VERY basic understanding of UDFs so I wasn't aware of the
restrictions you mentioned. My goal is to allow someone to enter a formula
such as:

=GetMySql(1)

where 1 is the id portion of my query, and in that same cell return the
result, just like the sum function works. I'm not trying to change other
cells and I don't need column headers or anything else, just the result of
the sql query. So if I enter the above formula in A1, A1 will contain, for
example, 20,000. No more, no less. Eventually I'd like to get to a
situation where a user could use a UserForm to do some stuff which would
cause the formula to be put in a certail cell and retreive the result from
the mysql database (I think I can do this part once I get the UDF figured
out).

I understand what the lngField argument is meant to be, but I still don't
understand why I need to provide that argument when I will always be
returning the same field. Could this be hard-coded into the UDF somehow so
I don't have to provide it as an argument?

I don't know if a Worksheet_Change event will work considering that a
particular cell will not be changed (for example, the user would not be
entering a "1" on a worksheet so there would be no value to run through a
sub). I don't need column headings so a second UDF would not be needed. I
also don't need (nor want) a range of data to be returned, so I don't think
an array-entered UDF would be the ticket either.

I hope this helps explain my situation and I again thank you for your
assistance.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
Michael

The lngField argument is the Field you want to return. A UDF returns one
value to the cell in which it is entered. The problems that you are
experiencing are 1) you aren't passing enough arguments to the UDF and 2)
you're trying to write to cells. With a UDF, you can't change the values
of
any cells, rather the value you assign to the function name is put into
the
cell into which the function was entered. Think of it in terms of the
built-in worksheet functions in Excel. You can't enter a build-in
function
(like SUM) in one cell and have it change the value in another cell - it's
the same with UDF's. So this part

'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
End With

doesn't work with a UDF because you're changing the Excel environment,
namely Cells(iRow, iCol).Value, when a UDF can only return a value.

The first thing you need to do is evaluate whether or not a UDF is really
what you want. If you want something that is going to produce column
headers, then you probably don't want a UDF. But, if you want to change
(from your example) B1 and have it show a different result (presumably
from
a different record) then a UDF is probably a good fit.

Based on my limited knowledge of what you're trying to accomplish, I offer
the following suggestions;

Use a Worksheet_Change event so that when you change a particular cell,
the
Sub runs and returns different results.
Use two UDFs: one for column headings and one for data.
Use an array-entered UDF (or two) to get all the data in the cells at
once.

Tell me why you want to use a UDF and maybe we can find the best way to
get
to the same result.

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

"Michael J. Malinsky" wrote in message
...
Dick,

Thanks for the response, but I'm still a bit confused. I've made the
changes you suggested, but I'm not sure why you are having me define two
variables , strWhere and lngField). I'm assuming strWhere would be the

value I want to use for the value of id, but I don't know where the
lngField
fits in. Here is the code I have based on your recommendations. I am
getting an #VALUE error when using the formula:

=GetMySql(B1)

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

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long

myconn.Open "DSN=MySQL TEST"
mySQL = "SELECT number FROM master where id = " & strWhere
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

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
End With

'COLLECT DATA
myrs.MoveFirst
GetMySql = myrs.Fields(lngField).Value


NODATAHE

myrs.Close
myconn.Close

End Function


Thanks for your help and looking forward to your response.

--
Michael J. Malinsky


"Dick Kusleika" wrote in message
...
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

Michael


I have a VERY basic understanding of UDFs so I wasn't aware of the
restrictions you mentioned. My goal is to allow someone to enter a
formula
such as:

=GetMySql(1)

where 1 is the id portion of my query, and in that same cell return the
result, just like the sum function works. I'm not trying to change other
cells and I don't need column headers or anything else, just the result of
the sql query. So if I enter the above formula in A1, A1 will contain,
for
example, 20,000. No more, no less. Eventually I'd like to get to a
situation where a user could use a UserForm to do some stuff which would
cause the formula to be put in a certail cell and retreive the result from
the mysql database (I think I can do this part once I get the UDF figured
out).

OK, I think I have a better understanding now. I think a UDF is proper for
this application.


I understand what the lngField argument is meant to be, but I still don't
understand why I need to provide that argument when I will always be
returning the same field. Could this be hard-coded into the UDF somehow
so
I don't have to provide it as an argument?

You are absolutely correct. You don't need that argument if you are always
returning the same field.


I don't know if a Worksheet_Change event will work considering that a
particular cell will not be changed (for example, the user would not be
entering a "1" on a worksheet so there would be no value to run through a
sub). I don't need column headings so a second UDF would not be needed.
I
also don't need (nor want) a range of data to be returned, so I don't
think
an array-entered UDF would be the ticket either.

I agree - ignore those suggestions.

I don't have MySQL on this machine, so this is untested pseudocode, but I
envision the UDF looking like this:


Function GetMySql(strWhere As String) As Variant

'strWhere may not be a string. You'll need to experiment with
'data types if string doesn't work

Dim myconn As New ADODB.Connection
Dim myrs As Recordset
Dim mySQL As String

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

With myrs
.MoveFirst
If Not (.BOF And .EOF) Then
GetMySql = .Fields(1).Value
Else
GetMySql = 0
End If
End With

myrs.Close
myconn.Close

End Function

Give that a try and we'll modify it from there if doesn't work like you
want.

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