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
Solved

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

Posted on 2003-12-09
5
979 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
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

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…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

856 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