import excel function for use in queries

[justin martin]

I am trying to import the rounding function from excel to
use as a function in queries. This is what I have, and it
is not working (numero = number to be rounded, numero1 =
number of desired digits):

Public Function round(numero As Variant, numero1 As
Variant)

Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
round = objExcel.WorksheetFunction.round(numero, numero1)

objExcel.Quit

Set objExcel = Nothing

End Function

Can someone tell me what I am doing wrong? Thanks!

justin

 

[Jonathan]

-----Original Message-----
I am trying to import the rounding function from excel to
use as a function in queries. This is what I have, and it
is not working (numero = number to be rounded, numero1 =
number of desired digits):

Public Function round(numero As Variant, numero1 As
Variant)

Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
round = objExcel.WorksheetFunction.round(numero, numero1)

objExcel.Quit

Set objExcel = Nothing

End Function

Can someone tell me what I am doing wrong? Thanks!

justin

.

Hi Justin, you may like to change the data types for your
varibles from variant to double.

If you are using Access 2002 and possibly 2000 you will
find that the round function is available in VBA.

Luck
Jonathan

 

[Ken Snell]

Did you set a reference to the EXCEL object library in the Visual Basic
Editor?

It probably would be easier to write your own rounding code instead of all
the overhead for opening/accessing EXCEL just for a rounding function. And
as Jonathan suggests, there is some Round function capability in ACCESS. And
there are many ways to get rounding done using other functions.

 

[Joe Fallon]

You can always write your own functions to do the same thing as Excel.

Here are 3 common ones: Floor, Ceiling and Round

Function Floor(N, ByVal Precision)
'
' Similar to Excel's Floor function
' Rounds down (toward zero) to the next higher level of precision.
' Precision cannot be 0.
'
Precision = Abs(Precision)
Floor = Int(N / Precision) * Precision
End Function


Function Ceiling(N, ByVal Precision)
'
' Similar to Excel's Ceiling function
' Rounds up to the next higher level of precision.
' Precision cannot be 0.
'
Dim Temp As Double
Precision = Abs(Precision)
Temp = Int(N / Precision) * Precision
If Temp = N Then
Ceiling = N
Else
Ceiling = Temp + Precision * Sgn(Temp)
End If
End Function

Function RoundN(X, N As Integer)
'
' Rounds a number to N decimal places
' Uses arithmatic rounding
' N should be in the range 0-10 for proper results
'
Dim Factor As Long
Factor = 10 ^ N
RoundN = Int(X * Factor + 0.5) / Factor
End Function