Excel Formulae, VBA & PB DLL - SOLVED!!!

[naw]

Hi All,

Have recently resurrected an old project to build a DLL of commands in PB that can be accessed by Excel Formulae & VBA...

Problem I'm having is in passing Variables from My Excel Formulae, the MessageRequester Gadget is showing that only the first character of each parameter is being passed?!?!?!?!

Am using Excel2003 on WinXP & PB4.3

ASCII representaqtion of MessageRequester window

Code: Select all

+------------------+
|PB INFO       | X |
+------------------+
|C                 |
|S                 |
|K=V               |
|                  |
|      [ OK ]      |
+------------------+

I've also discovered that VBA seems to be ASCII based while Excel is UTF-8 which means you have to Compile the PB source accordingly...

Excel Formula

Code: Select all

=SetINIvalue("C:\CPU Data.ini","Section","Key","Value")

VBA Code

Code: Select all

Public Declare Function SetINIvalue Lib "c:\NAWPBXL.dll" (ByVal INIFILE As String, ByVal INIgroup As String, ByVal INIkey As String, ByVal INIvalue As String) As String

PB Code

Code: Select all

ProcedureDLL.l SetINIvalue(INIfile.s, INIgroup.s ,INIkey.s, INIvalue.s)
  MessageRequester("PB INFO", INIfile+Chr(13)+INIgroup+Chr(13)+INIkey+"="+INIvalue)
  OpenPreferences(INIfile.s)
  PreferenceGroup(INIgroup.s)
  ProcedureReturn SysAllocString_(WritePreferenceString(INIkey.s, INIvalue.s))
EndProcedure

[srod]

I would guess that Excel is sending strings in Unicode format (probably BSTRs).

Try compiling your dll with the Unicode switch - see if that makes a difference?

[naw]

Hi srod,
With the Unicode Switch, the problem changes to producing Chinese characters...
So I think this is a problem with how the return values are being passed rather than how they are translated...

[srod]

Ah it seems that Excel will pass a string as a 'byte string' (BSTR) encoded in a way to match your locale. If your dll wishes to receive this in Unicode format (which is how Excel works internally with strings) then you have to declare your dll function to accept a variant instead.

http://msdn.microsoft.com/en-us/library/bb687915.aspx

Try changing your declare statement to send a variant by value and use the unicode switch for your dll.

Returning a string will need attention as well as it seems you need to modify a byref parameter instead of explicitly trying to return a string.

[naw]

@srod

Brill - finall got this working - thanks for the pointers...

Solution:

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
   Dim Res As String
   Res = PB2VB2XLdll(PARAM1, PARAM2)
   PB2VB2XLvba = Res
 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

Expected result - converts "Hello", "World" to "HELLO", "World" displaying both in a PB popup window and returns "HELLO World" to Excel

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

[srod]

Is that all you had to do; call a VBA function which then called the dll?

Crap, that should kind have been obvious! Doh!

You know that SysAllocString_() is for unicode strings? There is an Ascii version though.

[naw]

Obvious to you maybe, but if I hadn't googled for VBA & BSTR (as you hinted) I would never have found this http://metasharp.net/index.php/VBA_InterOp

Couldnt have done it without you - still dont understand why using a proxy Function allows it to work, but it does and thats good enough for me

Couldn't have done it without you - thanks srod

[srod]

My goodness, VBA is a fiddly little thing where strings are concerned!

[naw]

Do you think a summarised version would be worthy of posting up in Tips'n'Tricks?

I'm very much a part-time programmer and while this was tricky for me, perhaps the average PB coder would work it out in seconds...

If not, then I'd like to *contribute*

[jack]

I say post it, it's bound to help somebody

[srod]

By all means post it. Could be very useful indeed.

[naw]

Ok - will do then....

thanks chaps