Solved

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

Posted on 2003-12-09
5
975 Views
Last Modified: 2008-02-01
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
Comment
Question by:malramsay
  • 3
  • 2
5 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
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
 

Author Comment

by:malramsay
Comment Utility

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
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 125 total points
Comment Utility
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
 

Author Comment

by:malramsay
Comment Utility

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
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now