473,887 Members | 2,253 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Excel Functions in Access

NeoPa
32,584 Recognized Expert Moderator MVP
Many of us have noticed that there are some very useful functions available to you when using Excel, but these same functions are not available, as standard, in Access.
A particular issue I had recently (and I know others have come across this recently too) is rounding up and down. I know the Excel ROUNDUP() function rounds away from zero rather than upwards specifically, but is still useful in most circumstances.

To allow an Access database to use Excel (and by extension other Office libraries) you need to make the library available (this is on a database level rather than an Access installation level so be aware) simply :
  1. Open (or switch to) the VBA Window (Alt-F11).
  2. From the menus select Tools / References...
  3. Find the relevant library (in this specific case it will be Microsoft Excel 9.0 Object Library for Access 2000 which I'm using here, or whichever is right for your current version of Office).
While you're doing that you'll probably notice a whole bunch of other available references (libraries) which are available to link to.

This will save reproducing a bunch of functions which you may already be familiar with but find, to your disappointment, are not natively available in Access.

When I originally set this up I did some testing badly and thought that the Excel functions were available to SQL in a database that had the Excel library referenced. I subsequently discovered that not to be the case.

However, Public functions in your database are accessible to Jet SQL so if you have a requirement to implement an Excel function it is still possible to do so by the expedient of creating an encapsulating function (defined as Public in your database) which, itself, calls the Excel function. The VBA code in the database CAN access the Excel libraries. It is only Jet SQL that cannot.

Posted by Scott Price

This is a specific example of an Excel function; Forecast(). Called from a VBA code module in Access. (Developed using Access 2003).

Expand|Select|Wrap|Line Numbers
  1. Public Function xlForeCast() As Double
  2.  
  3. Dim MyDate As Integer 'Will be the point for which you are forecasting, in this case 2007
  4. Dim MyRange() As Variant 'Will be the independent element of the forecast function
  5. Dim MyRange1() As Variant 'Will be the dependent element of the forecast function
  6. Dim MyArray() As Variant 'Temp array to hold the query result set values before being split into the two preceding arrays
  7. Dim db As DAO.Database
  8. Dim rs1 As DAO.Recordset
  9. Dim ls As Integer 'Temp variable to count the rows in the list
  10.  
  11.  
  12. Set db = CurrentDb()
  13. Set rs1 = db.OpenRecordset("qryGetHistory") 'Opens the query that feeds the data
  14.  
  15.     With rs1
  16.         .MoveFirst
  17.         .MoveLast
  18.         ls = .RecordCount
  19.         .MoveFirst
  20.         MyArray() = .GetRows(ls) 'Populate the temporary array with the query results
  21.     End With
  22. 'Split the required data into two arrays, drawing from columns two and three in the query/array
  23. MyRange() = Array(CInt(MyArray(1, 0)), CInt(MyArray(1, 1)), CInt(MyArray(1, 2)), CInt(MyArray(1, 3)), CInt(MyArray(1, 4)))
  24. MyRange1() = Array(CInt(MyArray(2, 0)), CInt(MyArray(2, 1)), CInt(MyArray(2, 2)), CInt(MyArray(2, 3)), CInt(MyArray(2, 4)))
  25. MyDate = CInt(DatePart("yyyy", "July 30")) 'Set the Desired point to forecast for
  26. rs1.Close
  27. Set rs1 = Nothing 'Reset the recordset, releasing memory
  28. Set db = Nothing
  29.  
  30.     xlForeCast = Excel.WorksheetFunction.Forecast(MyDate, MyRange1, MyRange) 'Calls the Excel forecast function
  31.  
  32. Erase MyArray 'Reset the Arrays to zero, releasing memory
  33. Erase MyRange
  34. Erase MyRange1
  35. End Function
Oct 11 '07 #1
0 18848

Sign in to post your reply or Sign up for a free account.

Similar topics

13
35580
by: Allison Bailey | last post by:
Hi Folks, I'm a brand new Python programmer, so please point me in the right direction if this is not the best forum for this question.... I would like to open an existing MS Excel spreadsheet and extract information from specific worksheets and cells. I'm not really sure how to get started with this process. I ran the COM Makepy utility from my PythonWin (IDE from ActiveSTate),
1
9332
by: v_verno | last post by:
Good day, I have a web page that shows info retrieved from a MySQL db and I would like to convert all these data into an .XLS file. Have searched the net high and low but seems that I'm unable to find a free tool that can do the above and therefore I'm forced to do everything from scratch. Till now have developed two different versions of the programs and both somehow work but still have a small problem that I can't understand.
3
3444
by: info | last post by:
After using clipboard functions in Excel controlled from Access VBA, Excel doesn't quit when I use the following ExcelApp.Quit Set ExcelApp = Nothing If I don't use the clipboard functions in my Access code, Excel is terminated properly. I have seen discussions on this subject by can't find the answer to my
10
8213
by: Steve | last post by:
I am trying to create a DLL in Visual Studio 2005-Visual Basic that contains custom functions. I believe I need to use COM interop to allow VBA code in Excel 2002 to access it. I've studied everything I can find on COM Interop and .NET. I've also tried many of the 'Walkthroughs' on the MSDN site relating to COM add-ins, .NET and Office XP but am unable to get even these working in Excel 2002 or Word 2002. I've installed the Office XP...
4
10502
by: David_from_Chicago | last post by:
What I am trying to do is to simulate the LINEST functionality from Excel in Access through VBA. When I use LinEst in Excel I can get back five statistical results. Here are is the formula array I use: Slope {=LINEST(H72:H94,I72:I94,TRUE,TRUE)} SE {=LINEST(H72:H94,I72:I94,TRUE,TRUE)} RSquared {=LINEST(H72:H94,I72:I94,TRUE,TRUE)} F-Stat {=LINEST(H72:H94,I72:I94,TRUE,TRUE)}
5
4106
by: billelev | last post by:
Hi there. I need to perform a number of financial calculations within a database I am creating. Rather than writing my own functions, I figured it would be worthwhile to use the functions that already exist within excel. I have found sample code on the web for calling an excel function (being new to Access I tend to borrow bits and pieces from anywhere and everywhere): Function fXLCoupDayBS(dtmSettlement As Date, dtmMaturity As Date,...
2
7562
by: paigenoel | last post by:
I was wondering how you can add a function (acos) which exists in MS Excel into MS Access 2003? I need several functions, one of which is ACOS. My formula work well in Excel and now I need to use it in a database. Is this possible to use Excel functions in Access?
5
15181
ADezii
by: ADezii | last post by:
Periodically, the same or similar question appears in our Access Forum: How can I use Excel Functions within Access? For this reason, I decided to make this Subject TheScripts Tip of the Week. In order to demonstrate the use of Excel Functions within the context of Access, I performed the following steps in sequence: Created a Public Function called fStripNonPrintableCharacters() which will encapsulate the logic for executing the Excel...
4
3392
by: drt | last post by:
NEDERLANDS: Hallo, Ik heb eigen functies gemaakt in access, die werken perfect in de access query. Zodra ik echter vanuit excel een draaitabel maakt naar de access query (als een externe database) krijg ik de foutmelding dat er een 'ongedefinieerde functie' in de access database aanwezig is. Kan iemand mij op weg helpen dit probleem op te lossen? Alvast bedankt voor de reactie,
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11173
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10771
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10434
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7143
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6011
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4633
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4239
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3245
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.