Announcement

Collapse
No announcement yet.

Excel calling PBwin DLL Subroutine

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel calling PBwin DLL Subroutine

    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.

  • #2
    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..
    hellobasic

    Comment


    • #3
      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.
      Code:
      #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)

      Code:
      ''''' 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
      Last edited by Scott Hauser; 20 Feb 2008, 02:52 AM.
      The most exasperating part of the "rat race" is how often the rats are in the lead!

      Comment


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

        Code:
        '============  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.

        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
        Last edited by Scott Hauser; 20 Feb 2008, 03:39 AM. Reason: typos
        The most exasperating part of the "rat race" is how often the rats are in the lead!

        Comment


        • #5
          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..
          hellobasic

          Comment


          • #6
            Scott:

            Thanks a lot! Your code works so smooothly.
            :laugh::laugh::laugh:



            Edwin:

            Thanks for your suggestion too.



            Houng

            Comment


            • #7
              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/pb...ad.php?t=23887

              Comment


              • #8
                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.
                Last edited by Houng Chang; 27 Feb 2008, 04:37 PM.

                Comment


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

                  Good enough..
                  hellobasic

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      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
                      Code:
                      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 )
                      Engineer's Motto: If it aint broke take it apart and fix it

                      "If at 1st you don't succeed... call it version 1.0"

                      "Half of Programming is coding"....."The other 90% is DEBUGGING"

                      "Document my code????" .... "WHYYY??? do you think they call it CODE? "

                      Comment


                      • #12
                        Including:

                        End

                        In in the Excel VBA module will unload the dll.

                        Comment


                        • #13
                          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)

                          Comment


                          • #14
                            >ChDrive ThisWorkbook.Path
                            ChDir ThisWorkbook.Path

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

                            ChDir.. Chdrive.. please!
                            hellobasic

                            Comment

                            Working...
                            X