What are XLL's and DLL's?

[AnthonyWB]

Hello everyone. This is just a general programming question and not a programming probem that needs to be fixed. What are XLL's and DLL's. Why would you use (or develope) them as opposed to coding in the VBA editor. Do you need Visual Studio to develope them or can they be made through the VBA editor in an Excel spreadsheet. What is the learning curve to develope them? Thanks in advance.

[romperstomper]

They are compiled add-ins (strictly dlls are dynamic link libraries and don't necessarily have anything to do with Excel, but I assume you are talking about COM/automation add-ins here).
Because they are compiled code, they run faster (generally) and the source code cannot be viewed (without reverse engineering) so they are more secure. You do need a 'proper' programming language like VB or C to build them - you can't do it in VBA, at least not since the days of Office XP which had a Developer edition that could create dll files.
The learning curve for writing dlls in VB6 is not that steep from a VBA background; in VB.Net it's a little steeper. For XLLs you need C and the curve is exponential.

[AnthonyWB]

Solid answer rompstopper. Thank you.

From a project developement point of view, is there a way to judge whether or not a specific VBA project would be a good candidate for redevelopment in a"proper" langauge utilizing COM/automation add-ins ? I perform a massive quantitative assesment on a bond and swap portfolio and there are numerous spreadsheets and each spreadsheet has numerous vlookups, named ranges, indexes, offsetts...you name it. Previously the creation of the files and calculation was done manually totaling about 24 hours of work (or 3 working days). Using just VBA and the excel editor the number of working days has been squeezed down to about 1. Now, it works and it is not-so-hard to maintain but from a professional standpoint it looks ameaturish. More importanlty it is highly insecure ! The data set for the automation are three large Excel spreadsheets and are provided to us from "our" technology group. The technology group stores the data in an Oracle database, and I have "read access" to that database as well.

I have beginning program knowledge in C.

[romperstomper]

Hard to answer. If security is a major issue, then yes it's probably worth it. Even if you just move any UDFs to a dll and call them from the worksheets. If speed is the issue, you'd have to test to see where the holdups were. (I'd look at the VLOOKUPs first - they are rarely the best way.)
You might want to have a look at this book though.

[AnthonyWB]

Security is an issue. Currently there are no UDFs (user-defined-functions) in the "automation"...perhaps I should develope some ? The main reason why I did not develope UDFs is because most of my formulas utilize vlookups, and conditioning statements

As an example : IF(vlookup( )=TRUE , vlookup( ), " do-something-else" )

At its worst there could be additional conditiong statements such as a combination if IFs, ANDs, ORs.

What are good alternatives to vlookups. Based on some threads that I searched on this forum vlookups were said to be less computationaly intensive then other search methods. Were they wrong ? Are there better alternatives.

[romperstomper]

It depends on the VLOOKUP really. Mostly they are inefficient because they tend to reference large tables when you are only really interested in two columns (the key column and the returned data one). They are also often used to return several columns of data for the same lookup value. In both of those situations I would use INDEX and MATCH instead; if there are several return values, put the MATCH result into a helper cell and refer to it directly from several INDEX formulas - much more efficient. Also, if you are using Excel 2007, use IFERROR rather than If(ISERROR()) which I assume is what you meant with your IF example.

[AnthonyWB]

let me put some work in at the cell formula level. I have not used MATCH for anything. And yes I meant ISERROR. But note I do have conditional statemtns that use vlookups. I am using Excel 2007. Thanks

[AnthonyWB]

For a specific example:

Please Login or Register  to view this content.

And this is in several thousands of cells in one specific column.