4

I'm opening an ADODB connection in Excel 2007 to query one of the worksheets of the current workbook. When trying to add a custom VBA function, an error raises "Undefined function name". The connection:

Dim connection As String
Dim records As ADODB.Recordset
Dim query As String
Dim fileName As String

fileName = ThisWorkbook.FullName
connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
query = "select t.[Col1] from [Sheet1$] As t"

Set records = New ADODB.Recordset
records.Open query, connection

Sheets(2).Range("A1").CopyFromRecordset records

What I would like to achieve is to have another column in the select, like

query = "select t.[Col1], myFunc() from [Sheet1$] As t"

where myFunc is a Function defined in the workbook.

I know that something like this is possible in Access (to have custom VBA functions in a query). Is this possible in Excel too?

What's the best practice or workaround for this scenario?

Improve this question
8
  • To clear up what the question is: You say "opening an ADODB connection in Excel 2007 to query one of the worksheets of the current workbook". Why you do so? If you are in Excel, why not access the worksheet directly? However: Your "query" is a string. So you can build this string by concatenation as you need. If the function myFunc() returns a string, then maybe: query = "select t.[Col1], " & myFunc() & " from [Sheet1$] As t" is what you want? If not, how should the resulting query string look like? Commented Aug 10, 2014 at 9:46
  • Axel, thanks for your questions! 1.) When working with large amount of data using queries makes operations and maintenance cleaner, more efficient and faster than accessing cells individually. Especially when it comes to joining multiple worksheets (or tables), grouping and ordering by multiple fields. 2.) Concatenating the query string is not an option as concatenation evaluates the function only once before it is passed to the connection object. What I would like to achieve is to evaluate the function in every run, just like it works in any database engine. Commented Aug 10, 2014 at 12:41
  • If you write a query in Access, you can use your custom VBA functions. The query string will look like this: "select field1, myFunc(field2) from table1" and myFunc will evaluate in every round with the actual field2 value. Commented Aug 10, 2014 at 12:43
  • 1
    Ah, i see. You try to call a Excel VBA function like a stored procedure. This is not possible, i think. But maybe someone other knows a way. Commented Aug 10, 2014 at 13:31
  • Exactly. It is possible in Access, that's why I asked the question. But if it's not possible in Excel, whether there is a workaround. Commented Aug 10, 2014 at 17:18

2 Answers 2

Reset to default
5

I think you need some basic explanations here, and maybe an answer to this question:

Where do the functions in SQL come from?

If you're sending queries to any database server that supports ANSI-Standard SQL, the database engine will parse and run 'inline' functions that are native to SQL: LEFT(), SUBSTRING(), SIN(), SQR(), etc. There's a short list here:

http://www.smallsql.de/doc/sql-functions/index.html

Oracle servers will expose additional functions that extend ANSI SQL, as will Microsoft SQL Servers; both PL-SQL and T-SQL have functions that are not available in standard SQL. Both of them allow the DB Owner to create their own functions, which reside on the server and are also available to SQL queries.

Microsoft Jet SQL, which isn't quite the same as ANSI SQL, has a rather limited set of native functions; but nobody minds when they are running Jet SQL in an MS-Access environment, because almost all of the Visual basic for Applications functions are made available to the SQL engine by MS-Access.

Furthermore, all of the globally-declared VBA functions that they've written and made visible in the local MS-Access project are made available to the SQL engine, too.

As long as you're running the query from Microsoft Access.

That's the easy part...

Once you move outside the MS-Access environment, you can't see the VBA.

You can query the data using Jet SQL (and the Microsoft.ACE.OLEDB.12.0 provider is doing exactly that) but, if you're running it from Excel, you're not going to enjoy the MS-Access database engine's ability to use VBA: you've got the native Jet SQL function list, and nothing else

The functions are listed here, and very few other places:

MS Access: Functions - Listed by Category

That list now includes IIF() - the inline 'IF' function - which is all you've got in Jet SQL if you want a CASE statement in your SELECT clause; you'll find that useful if your first lesson in native Jet-SQL is that all the VBA NZ() functions in your query have stopped working.

Many of these functions look like the familiar VBA functions: and this is a source of confusion, because you're not running VBA, you're running SQL.

Very few people understand that this list of native Jet functions is not the same as the list of native VBA functions made available to SQL by MS-Access, and Microsoft do not make this explicit in their documentation.

This is a complete PITA because everyone querying a SQL server or an Oracle DB expects that the server will run all and any functions in their SQL query that are declared, imported or 'native' to the dialect of SQL running on that server. You've declared VBA functions in the Access mdb, and you expected they would be visible to SQL too!

How not to fix this:

I have seen a Sybase server where the brilliant but misguided database owner had imported functions from an external library that you have definitely used without realising that it's there in every MS-Access database: vbaen32.dll, the VBA function enumeration dll. This required quite a lot of work, and never quite worked: please do not attempt to replicate this feat of genius.

So the short answer is 'No'.

Now for the useful answer:

Recordset.GetRows() will return your recordset as a 2-Dimensional VBA array, and you can run your VBA functions on that after the SQL engine has done the heavy lifting of sorting, filtering and aggregation.

You can do this efficiently on a large data set, without an excessive memory footprint, if you run your vba sequentially in chunks on a Forward-Only cursor, calling Recordset.GetRows(Rows:=1024) until you hit the end of the data.

Although you might want to ask: "Why am I doing this?", as it's very difficult to think of a process where better analysis and design wouldn't reveal a better solution. Me, I had to implement that hack for a team whose Excel-dependent process ran on csv files that grew, and grew... And grew to terabyte sizes that could only be read by a database driver. And they work in an environment where getting a proper database server takes 2-3 years of sustained managerial effort.

If you are the fortunate son who inherited that particular process after I quit, I recommend trying my GetRows 'solution' after nuking the site from orbit.



Footnote: Do, please, expand this answer if you find a better online listing for Jet SQL functions.

Meanwhile, I would urge any other 'Stack contributor who reads this to add their own links to a good listing of Jet SQL native functions - if you can find one. Most are wrong and none are comprehensive, and very few are explicit in stating that there's a difference between native functions and imported VBA. For all I know, the Jet Engine is importing and running a restricted set of functions from VBAEN32.dll - but Jet under ADODB definitely isn't a fully-featured VBA host application, and that limitation needs to be clearly understood when you're using it outside MS-Access.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I see only 1 option here:

As the "myFunc()" function does not have any parameters you can input the function to a separate worksheet (e.g. to Sheet2 in A2, in A1 put a header like "A") and reference the cell in the SQL query like:

select t.[Col1], myFunc.A from [Sheet1$] As t, [Sheet2$] as myFunc
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.