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
malramsayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.