Excel calling PBwin DLL Subroutine

[Houng Chang]

Can anyone help to figure out what is wrong here? Thanks a lot in advance.

This task is to call a DLL sub (PBWin 8.04 generated) for calculation outside Excel VBA. Then put the results on the worksheet.



'''PB DLL code '''''''''''''''''''''

''' file: hycUCC2.bas
#COMPILE DLL

SUB ABCD ALIAS "ABCD" (N as integer, A() as single, B() as single, C() as single) EXPORT
dim i as integer
for i=1 to N
C(i)=A(i) * B(i)
next i
END SUB



'''' Excel VBA code in Module'''''''''''''''
Public Declare Sub ABCD Lib "hycUCC2.dll" (K As Integer, AA() As Single, BB() As Single, CC() As Single)


''''' Excel code in worksheet VBA for command button ...
Private Sub CommandButton1_Click()

Dim a(4) As Single, b(4) As Single, c(4) As Single

For i = 1 To 4
a(i) = Cells(i + 2, 2).Value
b(i) = Cells(i + 2, 3).Value
Next i

Call ABCD(4, a(), b(), c())

For i = 1 To 4
Cells(i + 2, 5).Value = c(i)

Next i

End Sub




'''''''' Error message from Windows:
Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience.

[Edwin Knoppert]

The array's are unknown entities between PB and VBA

You'll need another way like passing the address to the first element and the nr of elements to copy.

There is also something like safearray's..

[Scott Hauser]

From the Help file:

If you need To Access a Visual Basic Array, And Not change its Size,
the easiest way is To pass the first element Of the Array As one parameter,
And pass the total number Of elements As another parameter.

#Compile Dll "hycUCC2.DLL"
#Dim All

Sub ABCD Alias "ABCD" (ByVal N As Integer, ByVal A_AR As Dword, B_AR As Dword, ByVal C_AR As Dword) Export
  Dim A(1 To N) As Single At A_AR
  Dim B(1 To N) As Single At B_AR
  Dim C(1 To N) As Single At C_AR
  Local i As Long
  
  For i = 1 To N
      C(i) = A(i) * B(i)
  Next
End Sub

Try this (not tested! I do not have VB 32 installed)

''''' Excel code in worksheet VBA for command button ...
Private Sub CommandButton1_Click()
 
   Dim a(4) As Single, b(4) As Single, c(4) As Single
    
   For i = 1 To 4
      a(i) = Cells(i + 2, 2).Value
      b(i) = Cells(i + 2, 3).Value
      c(i) = 0   '<--probably not needed, but prefer to prefill the empty
   Next i        ' array with something before passing it to the external lib
    
'pass first element see PBWIN help (Appendix-B)
   Call ABCD(4, a(1), b(1), c(1))     
   For i = 1 To 4
   Cells(i + 2, 5).Value = c(i)
    
   Next i
 
End Sub

Edited: I think my advice and code above is incorrect and will probably crash. Scott

[Scott Hauser]

Try This. It is an adaptation of the vbsort sample. I believe it should work.

'============  VB CODE

Public Declare Sub ABCD Lib "hycUCC2.DLL" (AA!(), BB!(), CC!())

Sub main()
   ReDim a(1 To 4) As Single, b(1 To 4) As Single, c(1 To 4) As Single
    
   For i = 1 To 4
      a(i) = Cells(i + 2, 2).Value
      b(i) = Cells(i + 2, 3).Value
      c(i) = 0   '<--probably not needed, but prefer to prefill the empty
   Next i        ' array with something before passing it to the external lib
    
    'I think if you call it like this it should be ok as far ast this line
   ABCD a(), b(), c()

    ' If vbapp code as posted and not modified
   For i = 1 To 4
      Cells(i + 2, 5).Value = c(i)
   Next i
End Sub

The PBWin DLL code follows. I made three duplicate calls to obtain vbArrayLBound, u = vbArrayUBound(A_AR, 1) and
vb = vbArrayFirstElem(A_AR). These may have been redundant. Because I couldn't see what was under the hood of the api call I played it safe, repeating the calls using new "vb" variables. I did cheat and reuse l and u variables over in the for-next loop. I suspect this will be closer to working than my prior sample code.

' PowerBasic dll code

#Compile Dll "hycUCC2.DLL"

#Dim All
%USEMACROS = 1

#Include "VBAPI32.inc"
#Include "Win32API.inc"

Sub ABCD Alias "ABCD" (A_AR As Dword, B_AR As Dword, C_AR As Dword) Export
  Local i As Long
  Local l As Long
  Local u  As Long
  Local vb, vb1, vb2 As Dword

  l  = vbArrayLBound(A_AR, 1)
  u  = vbArrayUBound(A_AR, 1)
  vb = vbArrayFirstElem(A_AR)
    Dim A(l To u) As Single At vb

  l  = vbArrayLBound(B_AR, 1)
  u  = vbArrayUBound(B_AR, 1)
  vb1 = vbArrayFirstElem(B_AR)
  Dim B(l To u) As Single At vb1

  l  = vbArrayLBound(C_AR, 1)
  u  = vbArrayUBound(C_AR, 1)
  vb2 = vbArrayFirstElem(C_AR)
  Dim C(l To u) As Single At vb2

  For i = l To u
    C(i) = A(i) * B(i)
  Next
End Sub

Scott

[Edwin Knoppert]

PB supports creating a safearray by doing this:

Dim a(0 to 100) as long
Local v as variant
v = a()

From VB i don't know..

[Houng Chang]

Scott:

Thanks a lot! Your code works so smooothly.




Edwin:

Thanks for your suggestion too.



Houng

[John Hackl]

Another way.


The slowest part is getting the data back into excel cells

Modify a Excel Variant Array with a PBWin 7.02 dll

http://www.powerbasic.com/support/pbforums/showthread.php?t=23887

[Houng Chang]

Some lesson learned here:

When PB DLL file and Excel file are in the same folder, the Excel file should be loaded from "File > Open > Open >" Excel menu command. You can also click the Excel file from folder file listing.

But if you load Excel file from the history listing in the Excel "File" command, Excel will look for PB DLL file from other directory which Excel is addressing. When this happens, Excel VBA error number 53 shows up.

This happens also when you load an Excel file from File history listing and then click "save" or "Open", the "Save" and "Open" will direct you to different directories. This maybe the reason why VBA looks for PB DLL in the wrong folder.

[Edwin Knoppert]

VB(A) tends to load dll's on demand.
So use the LoadLibary() API to preload the dll from a certain folder.

Good enough..

[John Hackl]

Typically I lnclude the dll in the same subdirectory as the Excel Workbook, then in the Excel module which calls the dll.

ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path

[Cliff Nichols]

Hi Houng Chang,
(sorry so formal, but with some languages hard to tell 1st name from last so to be informal).

Anyways, As you are closely guesstimating, the answer is VBA does like VB6 did and looks to its default directory (and if not there then you get the error)

John is right with his change of directory before looking with

Typically I lnclude the dll in the same subdirectory as the Excel Workbook, then in the Excel module which calls the dll.

ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path

In most cases this fixes everything, but where this may fail is if developing and testing at the same time (Excel is open, and you change the DLL in PB)

In that case I close Excel, and reopen and all is fine again. (Maybe not a "bullet-proof" answer, but then again "Since when would a user have access to your code to change the DLL?")

If you follow John's suggestion you should be golden.

Or if you need a Excel specific example then my archives may clear it up a bit.
www.velmexcontrols.com

(obviously archived for a reason )

[John Hackl]

Including:

End

In in the Excel VBA module will unload the dll.

[Houng Chang]

Hi, John, Cliff and Edwin,

The ChDrive and ChDir make all the difference. All the directory problem solved.

The combination of Excel and PB DLL makes technology development very logical in the big corporation. Most users use only Excel. But when the program grow to quite bulky, long VBA code becomes a burden. With PB-DLL, the program distribution becomes more secure. (Better than .XLA distribution)

Thank you all again.

Houng (first name)

[Edwin Knoppert]

>ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path

Constantly incorrectly mentioned on this board.
Again.. swap the order!

ChDir.. Chdrive.. please!