Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 991
  • Last Modified:

Passing Arrays From Excel to VB (then to C++ afterwards)

The answer to this question is hopefully simply about how to pass arrays properly....

I've been trying to alter the help file below so that I can pass an excel array from a worksheet to a vba function then to an dll.

"HOWTO: Pass Arrays Between Visual Basic and C"
http://support.microsoft.com/support/kb/articles/Q207/9/31.ASP 

Firstly, I created the dll, then copied their example and it all worked great running the sub manually from my .xla -

*****************************************

Option Explicit

   Private Declare Function AddLongs_Pointer Lib "MyStDll.dll" _
   (FirstElement As Long, ByVal lElements As Long) As Long

   Private Declare Function AddLongs_SafeArray Lib "MyStDll.dll" _
   (FirstElement() As Long, lSum As Long) As Long

   Private Sub testme()
      Dim ArrayOfLongs(2) As Long
      Dim lSum As Long
      Dim k As Long

      ArrayOfLongs(0) = 1
      ArrayOfLongs(1) = 2
      ArrayOfLongs(2) = 3

      lSum = AddLongs_Pointer(ArrayOfLongs(0), UBound(ArrayOfLongs)+1)
      MsgBox "Result with C array = " & Str$(lSum)

      k = AddLongs_SafeArray(ArrayOfLongs(), lSum)
      If k = 0 Then
         MsgBox "Result with Safearray = " & Str$(lSum)
      Else
         MsgBox "Call with Safearray failed"
      End If
   End Sub

*****************************

All I'm trying to do is supply the DLL functions with an array from the worksheet (i.e. the following does not work when an array is passed to it - nor does using "Function testme(ArrayOfLongs() as integer)" work.

What am I doing wrong?

Thanks,
Malcolm

*****************************

Function testme(ArrayOfLongs As Variant)

      Dim lSum As Long
      Dim k As Long

      lSum = AddLongs_Pointer(ArrayOfLongs(0), UBound(ArrayOfLongs) + 1)
      MsgBox "Result with C array = " & Str$(lSum)

      k = AddLongs_SafeArray(ArrayOfLongs(), lSum)
      If k = 0 Then
         MsgBox "Result with Safearray = " & Str$(lSum)
      Else
         MsgBox "Call with Safearray failed"
      End If

End Function
0
malramsay
Asked:
malramsay
  • 3
  • 2
1 Solution
 
nmcdermaidCommented:
When you say Excel array, do you mean an array defined in VBA?

If you use a collection instead, you can just pass the collection variable as a parameter straight into a sub or function in a DLL

ie

Dim cParameter As New Collection

Call cParameter.AddItem("Item 1")
Call cParameter.AddItem("Item 2")
Call cParameter.AddItem("Item 3")




Function testme(CollectionOfStrings As Collection)    ' a function inside a DLL

   Call MsgBox(CollectionOfStrings(1))

End Function



To pass arrays to a C program, you would probably still need to use API's though.
0
 
malramsayAuthor Commented:

Sorry, let me clarify.  When I said "an excel array" I meant that the user will have called the function from the worksheet and inserted their own array parrameters - i.e. exactly like you would do when you use "=sum(A1:A3)".  So, in the example above I insert 3 integers into 3 separate cells and then use "=testme(A1:A3)" which calls the testme function in my first message.

The problem is that this is not working.  In the examples above I get the following error messages.  

For AddLongs_Pointer:

   "#VALUE" in the cell

For AddLongs_SafeArray:

    "Compile error:
    Type Mismatch: array or user-defined type expected"

I hoping it would be a straightforward VBA array quirk or something I was unaware of!??

Thanks,
Malcolm
0
 
nmcdermaidCommented:
So basically you want to add a user defined function to Excel is that right?

The problem is that you are passing in an Excel Range object, not an array.

If you paste this into a module in Excel:


Function TestMe(ByVal rpRange As Excel.Range)
  Dim iRow As Integer
  Dim iSum As Double
 
  Do Until iRow = rpRange.Rows.Count
    iSum = iSum + rpRange.Rows(iRow + 1)
    iRow = iRow + 1
  Loop

  TestMe = iSum
End Function




Then type =TestMe(A1:C1) in a cell, it will add up the three cells.


Note that the parameter to the function is of type Excel.Range.

So you will either need to use the Excel Range object, or write an intermediate function which converts the range object into an array so you can pass it further down.

0
 
malramsayAuthor Commented:

Thanks nmcdermaid, that's works great - the C++ dll recognises the Excel.Range as an array.

I was hoping it would be that straightforward!
0
 
nmcdermaidCommented:
Just be aware that a range object may be irregularly shaped (though of course not when using A1 notation directly from the Excel sheet). I'm not sure what kind of array that would be!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now