Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing Arrays (Excel VBA / VB6)

    G'day All:

    Can anyone shed any light on this one?

    My test function returns data in the second array if called directly, but returns zero if called using Application.Run from within Excel.

    However, if I use Excel.Application.Run from VB6, the data are returned OK.

    TIA,

    Dave Wiseman

    '''''''''''''''''''''Excel VBA'''''''''''''''''''''''''''
    Option Explicit
    Option Base 1

    Function TestFn(arrayIn() As Double, arrayOut() As Double) As Long
    Dim i As Long
    Dim j As Long

    j = LBound(arrayOut)
    For i = LBound(arrayIn) To UBound(arrayIn)
    arrayOut(j) = arrayIn(i) ^ 2
    j = j + 1
    If (j > UBound(arrayOut)) Then Exit For
    Next i

    TestFn = 0

    End Function

    Sub VBATestCall()
    Dim lReturn As Long
    Dim i As Long
    Dim arraySend(1 To 4) As Double
    Dim arrayReceive(1 To 4) As Double

    For i = 1 To 4
    arraySend(i) = i
    arrayReceive(i) = 0
    Next i

    lReturn = TestFn(arraySend, arrayReceive)

    MsgBox arrayReceive(1) & " " & arrayReceive(2) & " " & arrayReceive(3) & " " & arrayReceive(4)


    For i = 1 To 4
    arraySend(i) = i
    arrayReceive(i) = 0
    Next i

    lReturn = Application.Run("TestFn", arraySend, arrayReceive)

    MsgBox arrayReceive(1) & " " & arrayReceive(2) & " " & arrayReceive(3) & " " & arrayReceive(4)

    End Sub


    ''''''''''''''''''''''''VB6''''''''''''''''''''''' '''''''
    Private Sub VBTestCall()
    Dim lReturn As Long
    Dim i As Long
    Dim arraySend(1 To 4) As Double
    Dim arrayReceive(1 To 4) As Double

    Dim oXL As Object
    Set oXL = CreateObject("Excel.Application")

    oXL.Workbooks.Open FileName:="C:TestFn.xls"

    For i = 1 To 4
    arraySend(i) = i
    arrayReceive(i) = 0
    Next i

    lReturn = oXL.Application.Run("TestFn.xls!TestFn", arraySend, arrayReceive)

    MsgBox arrayReceive(1) & " " & arrayReceive(2) & " " & arrayReceive(3) & " " & arrayReceive(4)

    oXL.Quit
    Set oXL = Nothing

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Arrays (Excel VBA / VB6)

    You wouldn't normally use Application.Run *within* Excel unless you were applying it to another session of Excel instantiated through code.
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Arrays (Excel VBA / VB6)

    The problem is not due to a difference between VBA and VB. The VB code will also work within VBA.

    ' DaveWiseman
    ' (NewLounger)
    ' 05-Oct-01 04:20
    ' Passing Arrays (Excel VBA / VB6)
    ' Post: 80314
    '
    '
    '
    '
    '
    ' G 'day All:
    '
    ' Can anyone shed any light on this one?
    '
    ' My test function returns data in the second array if called
    ' directly, but returns zero if called using Application.Run from
    ' within Excel.
    '
    ' However, if I use Excel.Application.Run from VB6, the data are
    ' returned OK.
    '
    ' TIA,
    '
    ' Dave Wiseman
    '
    '''''''''''''''''''''Excel VBA'''''''''''''''''''''''''''
    Option Explicit
    Option Base 1

    Sub Main()
    ' VBATestCall
    VBTestCallRevised
    End Sub

    Function TestFn(arrayIn() As Double, arrayOut() As Double) As Long
    Dim i As Long
    Dim j As Long

    j = LBound(arrayOut)
    For i = LBound(arrayIn) To UBound(arrayIn)
    arrayOut(j) = arrayIn(i) ^ 2
    j = j + 1
    If (j > UBound(arrayOut)) Then Exit For
    Next i

    TestFn = 0

    End Function

    Sub VBATestCall()
    Dim lReturn As Long
    Dim i As Long
    Dim arraySend(1 To 4) As Double
    Dim arrayReceive(1 To 4) As Double

    For i = 1 To 4
    arraySend(i) = i
    arrayReceive(i) = 0
    Next i

    lReturn = TestFn(arraySend, arrayReceive)

    MsgBox arrayReceive(1) & " " & arrayReceive(2) & " " & _
    arrayReceive(3) & " " & arrayReceive(4)


    For i = 1 To 4
    arraySend(i) = i
    arrayReceive(i) = 0
    Next i

    lReturn = Application.Run("TestFn", arraySend, arrayReceive)

    MsgBox arrayReceive(1) & " " & arrayReceive(2) & " " & _
    arrayReceive(3) & " " & arrayReceive(4)

    End Sub


    ''''''''''''''''''''''''VB6''''''''''''''''''''''' '''''''
    Private Sub VBTestCall()
    Dim lReturn As Long
    Dim i As Long
    Dim arraySend(1 To 4) As Double
    Dim arrayReceive(1 To 4) As Double

    Dim oXL As Object
    Set oXL = CreateObject("Excel.Application")

    oXL.Workbooks.Open FileName:="D:ExcelVBAHKWorkbook.xls"

    For i = 1 To 4
    arraySend(i) = i
    arrayReceive(i) = 0
    Next i

    lReturn = oXL.Application.Run("HKWorkbook.xls!TestFn", arraySend, _
    arrayReceive)

    MsgBox arrayReceive(1) & " " & arrayReceive(2) & " " & _
    arrayReceive(3) & " " & arrayReceive(4)

    oXL.Quit
    Set oXL = Nothing

    End Sub

    Private Sub VBTestCallRevised()
    Dim lReturn As Long
    Dim i As Long
    Dim arraySend(1 To 4) As Double
    Dim arrayReceive(1 To 4) As Double


    For i = 1 To 4
    arraySend(i) = i
    arrayReceive(i) = 0
    Next i

    lReturn = TestFn(arraySend, arrayReceive)

    MsgBox arrayReceive(1) & " " & arrayReceive(2) & " " & _
    arrayReceive(3) & " " & arrayReceive(4)


    Dim oXL As Object
    Set oXL = CreateObject("Excel.Application")

    oXL.Workbooks.Open FileName:="D:ExcelVBAHKWorkbook.xls"

    For i = 1 To 4
    arraySend(i) = i
    arrayReceive(i) = 0
    Next i

    ' lReturn = oXL.Application.Run("HKWorkbook.xls!TestFn", arraySend, _
    arrayReceive)
    lReturn = oXL.Application.Run("TestFn", arraySend, _
    arrayReceive)

    MsgBox arrayReceive(1) & " " & arrayReceive(2) & " " & _
    arrayReceive(3) & " " & arrayReceive(4)

    oXL.Quit
    Set oXL = Nothing

    End Sub

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Arrays (Excel VBA / VB6)

    The problem does not occur in Word 9.

    I guess that it is a bug in the use of Application.Run in Excel 9.

  5. #5
    New Lounger
    Join Date
    Jul 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Arrays (Excel VBA / VB6)

    Quite correct, Charlotte, you wouldn't "normally" do that!

    But I'm trying to call a VBA routine from a C++ DLL via OLE...

    I think Howard has given me a workaround.

    Thanks for your help too.

    Dave

  6. #6
    New Lounger
    Join Date
    Jul 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Arrays (Excel VBA / VB6)

    G'day Howard:

    Many thanks for having a look at this problem.

    Yes, it does seem like a bug to me (common across XL97, XL2000, XL2002).

    You've given me the clue to a workaround, though. The following code works within the same instance of Excel, which was what I needed. My actual problem is a little more complicated. I actually want to call back into VBA routines from within a C++ DLL via OLE. With luck you're clue will work in that situation too!

    Many thanks indeed!

    Dave


    Private Sub TestCall2()
    Dim lReturn As Long
    Dim i As Long
    Dim arraySend(1 To 4) As Double
    Dim arrayReceive(1 To 4) As Double

    Dim oXL As Object
    Set oXL = GetObject(, "Excel.Application")

    For i = 1 To 4
    arraySend(i) = i
    arrayReceive(i) = 0
    Next i

    lReturn = oXL.Application.Run("TestFn.xls!TestFn", arraySend, arrayReceive)

    MsgBox arrayReceive(1) & " " & arrayReceive(2) & " " & arrayReceive(3) & " " & arrayReceive(4)

    Set oXL = Nothing

    End Sub

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Arrays (Excel VBA / VB6)

    You caught me in a weak moment.

    I recently started a service for supporting others programming/learning Word VBA, so I am not usually going to post code solutions anymore.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles, California, USA
    Posts
    1,734
    Thanks
    0
    Thanked 3 Times in 1 Post

    Re: Passing Arrays (Excel VBA / VB6)

    I hope that doesn't mean that you're planning on just using the Lounge to peddle your wares?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Eileen_sig.gif>

  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Arrays (Excel VBA / VB6)

    No, I'm just going to restrict the type, and frequency, of questions I answer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •