convert Excel VBA function to Access VBA?

P

Pat

Hi,
I have a function in Excel VBA that takes a Range as an argument.
I want to translate this into Access. What is the corresponding
argument type in Access? Recordset?

For example:
in Excel, my function is:
Public Function myFunction(returns As Range) As Double

So in Access, is it:
Public Function myFunction(returns As Double) As Double
or is it:
Public Function myFunction(returns As Recordset) As Double

I just want to take a selected column of returns, grouped by fund ID,
perform a few calcs, and return a single number.
Each return in the returns column is stored as a Double.

The query I want to use it in is very simple, from 1 table:

SELECT Performance.ID, myFunction(Performance.Returns) AS Answer
FROM Performance
GROUP BY Performance.ID;

Or should I just calculate this in a Sub procedure, opening the query
from inside the Sub? I am unsure.
Any help would be appreciated.
Thank you,
Pat
 
B

Bill Mosca

Pat

You can use a user-defined function in a query as you proposed. But do you
need to run the function on each record or on the resulting agregate of
Returns?

It probably would be much clearer if you posted your Excel function and
explain the desired result.
 
P

Pat

Pat

You can use a user-defined function in a query as you proposed. But do you
need to run the function on each record or on the resulting agregate of
Returns?

It probably would be much clearer if you posted your Excel function and
explain the desired result.
Click to expand...

Thank you for your reply. I really appreciate it.

Yes, that's just it - I don't want to calculate this function on every
single return or record individually. That is what is happening now,
and is unwanted.

I want to calculate the function on a *set* of returns, which would be
a set of values in a column of a table.
What is wanted: All the returns that are connected with that ID get
put into the function, as a set, then the function is calculated on
the set of returns for that ID, and a single answer is displayed for
that ID.
How do we feed a set into a function as an argument in Access?
In Excel it is Range but I am not finding anything parallel in Access
VBA? Is it Recordset? I am playing around with a variety of choices
but nothing seems to be what I want..

In this case, the Access table has three columns, ID, date, and
return. ID and date are the combination key. The combination of ID and
date makes each record unique. For each ID, I want to take all the
returns, and calc the function below, in Access.

Here is the complete Excel VBA function that I would like to translate
to Access VBA.

Function omega(returns As Range, Optional threshold As Double) As
Double

If IsMissing(threshold) Then
threshold = 0
End If

Dim bins() As Double
Dim count as Integer, threshold_bin as Integer, i As Integer
Dim risk_sum as Double, reward_sum As Double

count = WorksheetFunction.count(returns)

ReDim bins(0 To count - 1)

For i = 0 To count - 1
bins(i) = WorksheetFunction.Small(returns, i + 1)
Next i

For i = 0 To count - 2
If threshold >= bins(i) And threshold < bins(i + 1) Then
threshold_bin = i
End If
Next i

For i = 1 To threshold_bin
risk_sum = risk_sum + (i / count) * (bins(i) - bins(i - 1))
Next i

For i = threshold_bin + 2 To count - 1
reward_sum = reward_sum + (1 - (i / count)) * (bins(i) -
bins(i - 1))
Next i

omega = (reward_sum + (bins(threshold_bin + 1) - threshold) * (1 -
(threshold_bin + 1) / count)) / (risk_sum + (threshold -
bins(threshold_bin)) * (threshold_bin + 1) / count)

End Function

Any help would be greatly appreciated.
-Pat
 
L

Larry Daugherty

Probably not!

Although Excel is widely understood, the point is that OP should
explain the desired outcome in real-world terms or in 'Access' terms,
not in terms of how any other application works.

We speak Access here, not all of the thousands of other applications
that people might use. The hundreds of silent lurkers here should not
be put to the bother of understanding Excel in order to relate
something to Access.

HTH
 
C

Clif McIrvin

You can use Excel functions in Access by setting a reference to Excel:

http://support.microsoft.com/kb/198571/en-us
Click to expand...

Thank you for this.
Can I call user defined functions in Excel from Access?
I still do not understand how to pass a set of values as an argument
in an Access VBA function.
Thanks again,
Pat


To the best of my knowledge you cannot --- at least easily. The Excel
range object DOES NOT translate into the Access world.

Access does provide a set of domain aggregate functions ... have you
investigated those to see if you can re-create the results of your Excel
function?

Yes, you can call user defined Excel functions from Access, but because
Access has no equivalent to the range object it would do you no good
(unless you want to go to the trouble of creating a clone of a range
object to pass to your function --- I certainly would make no such
attempt!)
 
P

Pat

I will investigate a user defined domain aggregate function. Thank
you.

Additionally, in the book Access Hacks, on p.199, they do what you
mentioned: create a 1 dimensional array of doubles, read the recordset
into it, and use that as an argument into another function. Just saw
it.

Can you provide a reference for how to call a user defined function
residing in Excel from Access?

Thank you,
Pat
 
C

Clif McIrvin

Pat said:
I will investigate a user defined domain aggregate function. Thank
you.

Additionally, in the book Access Hacks, on p.199, they do what you
mentioned: create a 1 dimensional array of doubles, read the recordset
into it, and use that as an argument into another function. Just saw
it.

Can you provide a reference for how to call a user defined function
residing in Excel from Access?
Click to expand...


Larry Daugherty made a valid point. While I will attempt to answer your
question (in a separate post), you might discover a much more elegant
solution by attempting to, as Larry suggested, restate your question in
Access terms (plain English, if you're too new to Access) rather than in
Excel terms.
 
C

Clif McIrvin

Pat said:
I will investigate a user defined domain aggregate function. Thank
you.
Click to expand...

A bit of clarification: the domain aggregate functions are built into
the Access application object; you can write a user defined function
that _uses_ the domain aggregate functions.
Additionally, in the book Access Hacks, on p.199, they do what you
mentioned: create a 1 dimensional array of doubles, read the recordset
into it, and use that as an argument into another function. Just saw
it.

Can you provide a reference for how to call a user defined function
residing in Excel from Access?
Click to expand...

Argh; it appears that I mis-spoke. I can't seem to find any notes from
what I thought I was remembering. It may be that what I did was similiar
to what you reference from Access Hacks and passed the resulting array
to the worksheet functions min or max, as that was one of the things I
was working with then. At the time I was working with non-normalized
data and couldn't make use of the DMin and DMax domain aggregate
functions.

You can copy and paste your Excel function into an Access standard
module and turn it into a user defined Access function that way:

''''''''''''''''''''''''''''''' Begin Module Code
Option Compare Database
Option Explicit


'Function omega(returns As Range, Optional threshold As Double) As
Double
Function omega(returns() As Double, Optional threshold As Double) As
Double

If IsMissing(threshold) Then
threshold = 0
End If

Dim bins() As Double
Dim count As Integer, threshold_bin As Integer, i As Integer
Dim risk_sum As Double, reward_sum As Double

' count = WorksheetFunction.count(returns)
count = UBound(returns) - LBound(returns) + 1
' not sure what would happen here if there were zero arguments passed.

ReDim bins(0 To count - 1)

' This modification assumes that you set a reference to the Excel Object
library in the
' Access VBA window. You may wish to look into late binding ... that's a
subject I
' still need to read up on.

For i = 0 To count - 1
' bins(i) = WorksheetFunction.Small(returns, i + 1)
bins(i) = Excel.WorksheetFunction.Small(returns(), i + 1)
Next i


For i = 0 To count - 2
If threshold >= bins(i) And threshold < bins(i + 1) Then
threshold_bin = i
End If
Next i

For i = 1 To threshold_bin
risk_sum = risk_sum + (i / count) * (bins(i) - bins(i - 1))
Next i

For i = threshold_bin + 2 To count - 1
reward_sum = reward_sum + (1 - (i / count)) * (bins(i) -
bins(i - 1))
Next i

omega = (reward_sum + (bins(threshold_bin + 1) - threshold) * _
(1 - (threshold_bin + 1) / count)) / (risk_sum + (threshold - _
bins(threshold_bin)) * (threshold_bin + 1) / count)

End Function
''''''''''''''''''''''End Module Code

If I read the Excel Small help correctly, this should work using that 1
dimensional array of doubles you mentioned.
 
A

Arvin Meyer [MVP]

Clif McIrvin said:
news:[email protected]...
Click to expand...
Can I call user defined functions in Excel from Access?
I still do not understand how to pass a set of values as an argument
in an Access VBA function.
Thanks again,
Pat

To the best of my knowledge you cannot --- at least easily. The Excel
range object DOES NOT translate into the Access world.
Click to expand...

Well, an Excel Range is an array, so you could do something like (untested):

Sub DoXL()
Dim appXL As Excel.Application
Dim intArray(1 To 10) As Integer
Dim intX As Integer
Dim dblRet As Double

For intX = 1 To 10
intArray(intX) = intX
Next
Set appXL = CreateObject("excel.application")

With appXL
sngRet = .WorksheetFunction.PercentRank(intArray(), 5)
MsgBox dblRet
End With
Set appXL = Nothing
End Sub
 
A

Arvin Meyer [MVP]

Can you provide a reference for how to call a user defined function
residing in Excel from Access?
Click to expand...

I gave an untested example before. Here is one I've actually used:

Function XL360(Arg1, Arg2) As Double
Dim objXL As New Excel.Application

XL360= objXL.WorksheetFunction.Days360(Arg1,Arg2)

Set objXL = Nothing
End Function
 
L

Lance

To try to make a database do something by "range" is the wrong mindset to
have. Databases are not spreadsheets. Excel data is referenced by x,y
coordinates while Access references data by keys.

Now, if I'm understanding your problem correctly, by "range" you are really
talking about a subset of data. This is done in Access by running a query
filtering by some value. This query can return a sum of whatever field you
need. Alternately, the query can return sums ( or many other different
"totaling" calculations ) on EVERY key value in your table at once.

You can then use that query as the basis for a 2nd query using your
functions or fed directly into your function itself.
 
B

Bill Mosca

Hi Pat

It seems like everyone is jumping in here, but maybe I can add something.

You can create an aggregate (Totals) query. Then use it as the "table" in a
new query using your function.

Example:
-Aggregate query qryOnOrder
SELECT ProdID, Sum(QuantityOrdered) AS SumOfQuantityOrdered
FROM MyTable
GROUP BY ProdID

-new query

SELECT ProdID, MyFunction(SumOfQuantityOrdered) AS Result
FROM qryOnOrder

--
Bill Mosca, MS Access MVP
http://www.thatlldoit.com
http://mvp.support.microsoft.com/profile/Bill.Mosca
http://tech.groups.yahoo.com/group/MS_Access_Professionals




Pat

You can use a user-defined function in a query as you proposed. But do you
need to run the function on each record or on the resulting agregate of
Returns?

It probably would be much clearer if you posted your Excel function and
explain the desired result.
Click to expand...

Thank you for your reply. I really appreciate it.

Yes, that's just it - I don't want to calculate this function on every
single return or record individually. That is what is happening now,
and is unwanted.

I want to calculate the function on a *set* of returns, which would be
a set of values in a column of a table.
What is wanted: All the returns that are connected with that ID get
put into the function, as a set, then the function is calculated on
the set of returns for that ID, and a single answer is displayed for
that ID.
How do we feed a set into a function as an argument in Access?
In Excel it is Range but I am not finding anything parallel in Access
VBA? Is it Recordset? I am playing around with a variety of choices
but nothing seems to be what I want..

In this case, the Access table has three columns, ID, date, and
return. ID and date are the combination key. The combination of ID and
date makes each record unique. For each ID, I want to take all the
returns, and calc the function below, in Access.

Here is the complete Excel VBA function that I would like to translate
to Access VBA.

Function omega(returns As Range, Optional threshold As Double) As
Double

If IsMissing(threshold) Then
threshold = 0
End If

Dim bins() As Double
Dim count as Integer, threshold_bin as Integer, i As Integer
Dim risk_sum as Double, reward_sum As Double

count = WorksheetFunction.count(returns)

ReDim bins(0 To count - 1)

For i = 0 To count - 1
bins(i) = WorksheetFunction.Small(returns, i + 1)
Next i

For i = 0 To count - 2
If threshold >= bins(i) And threshold < bins(i + 1) Then
threshold_bin = i
End If
Next i

For i = 1 To threshold_bin
risk_sum = risk_sum + (i / count) * (bins(i) - bins(i - 1))
Next i

For i = threshold_bin + 2 To count - 1
reward_sum = reward_sum + (1 - (i / count)) * (bins(i) -
bins(i - 1))
Next i

omega = (reward_sum + (bins(threshold_bin + 1) - threshold) * (1 -
(threshold_bin + 1) / count)) / (risk_sum + (threshold -
bins(threshold_bin)) * (threshold_bin + 1) / count)

End Function

Any help would be greatly appreciated.
-Pat
 
A

Arvin Meyer [MVP]

Lance said:
To try to make a database do something by "range" is the wrong mindset to
have. Databases are not spreadsheets. Excel data is referenced by x,y
coordinates while Access references data by keys.
Click to expand...

That is true. Well almost because there are a few range type functions in
datasheets. But for the most part, ranges are spreadsheet functionality.
However, exporting a recordset to a range makes sense because you can then
replace all the data, not just what you have in Access.

For instance, If the Excel range were a hundred rows by 10 columns, and the
data existing in them consisted of 50 rows by 10 columns, and you exported a
recordset which had 27 rows in 9 fields. You'd have a big problem. But if
you exported the data to a named range, all of the data would be replaced by
the recordset, and any left-over data would have been deleted.

So If you are dealing with Excel, from Access, you need to be very aware of
Excel's Object Model. Here's an example of how to fill the entire range in
and Excel sheet when using the Excel.CopyFromRecordset method from Access.

With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rst
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
VBA concatenate ranges into a new document 0
VBA for Dependents shortcut menu 0
Working outlook via vba code with xlsx - error 0
Excel - Wich Function should I use? 1
VBA word change keys 0
WORD VBA Search and switch to advanced ... 1
Use TODAY() function as the criteria in a SUMIF formula 1

Top