Solved

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

Posted on 2003-12-09
5
983 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9913155
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
ID: 9916232

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
ID: 9919005
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
ID: 9924035

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
ID: 9926756
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

728 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