PB DLL accessed by Excel VBA & Formulae

naw · Post by **naw** » Mon Jun 08, 2009 11:42 am

How to extend MS Excel formulae functionailty using VBA to access PB compiled DLLs.

In this example we create a new formula that passes 2 parameters "Hello" and "World" from Excel to VBA to PB, the PB code Capitilises the first parameter, displays both parameters individually, then returns a single (modified) string back to VBA which in turn passes the "HELLO world value back to Excel.

PB2VB2XLdll can be called as an Excel function directly, but produces unexpected and undesirable results (sometimes crashy)

The trick to getting it working seems to be by calling PB2VB2XLdll via the proxy function PB2VB2XLvba

Anyway, I hope this proves useful to others & thanks to srod for help in working this out. If anyone knows why the DLL cant be called directly from Excel (and instead needs to be called via a intermediary VBA function) I'd be interested...

Excel Formula:

Code: Select all

=PB2VB2XLvba("Hello","World")

Excel VBA in Module1

Code: Select all

Public Declare Function PB2VB2XLdll Lib "C:\PB2VB2XL.dll" (ByVal PARAM1 As String, ByVal PARAM2 As String) As String

Public Function PB2VB2XLvba(PARAM1 As String, PARAM2 As String) As String
   PB2VB2XLvba = PB2VB2XLdll(PARAM1, PARAM2)
End Function

PB Code Compiled as a DLL with UTF-8 coding

Code: Select all

ProcedureDLL.l PB2VB2XLdll(PARAM1$, PARAM2$)
   PARAM1$ = UCase(PARAM1$)
   MessageRequester("PB INFO", PARAM1$)
   MessageRequester("PB INFO", PARAM2$)
   ProcedureReturn SysAllocString_(PARAM1$ + " " + PARAM2$)
 EndProcedure

srod · Post by **srod** » Mon Jun 08, 2009 12:18 pm

If anyone knows why the DLL cant be called directly from Excel (and instead needs to be called via a intermediary VBA function) I'd be interested...

Well, the dll function needs to be declared before Excel can call it with the correct parameters etc. The only sensible place for that really is within a VBA module. Can't stick it in a single cell!

Thanks for sharing.

Edwin Knoppert · Post by **Edwin Knoppert** » Mon Jun 08, 2009 6:14 pm

Search for xll addin

naw · Post by **naw** » Tue Jun 09, 2009 2:13 pm

@Edwin

couldnt find anything for xll addin do you mean dll addin or xls addin?

Edwin Knoppert · Post by **Edwin Knoppert** » Tue Jun 09, 2009 2:29 pm

http://support.microsoft.com/kb/178474

More:

http://www.hellobasic.com/cgi-bin/forum ... 1164057829

naw · Post by **naw** » Wed Jun 10, 2009 11:04 am

@Edwin
Ah! sorry - I only searched in the forums

thanks for this - so you think it is possible to create Excel XLL Add ins from PB Compiled DLLs?
Interesting and if possible, really useful...

Edwin Knoppert · Post by **Edwin Knoppert** » Wed Jun 10, 2009 1:24 pm

Yes i think so.
I do this with another language though.
(long time ago)

naw · Post by **naw** » Wed Jun 10, 2009 1:30 pm

There could be a nice little *cottage industry* for PB developers, extending the functionality of Excel with PB Code...