Showing results 1 to 5 of 5

Thread: Access array variable from VBA

  1. #1
    Join Date
    26.10.2009
    Posts
    20

    Frage Access array variable from VBA

    Hi

    Is it not possible to access an array variable at top level from VBA and use SubItem?

    I have a variable testarray with Array Dim 1: 4

    In VBA:
    Dim var As Variable
    Set var = ThisProject.Variables.Item("testarray")
    var is Nothing

    When I change to:
    Dim var As Variable
    Set var = ThisProject.Variables.Item("testarray[0]")
    var is not Nothing

    6.22 SP1 Build 8

    Regards

  2. #2
    Join Date
    28.07.2008
    Posts
    64

    Default Re: Access array variable from VBA

    Try this:
    Dim var As Variable
    Set var = ThisProject.Variables.Item("testarray[0].Name_of_the_SubItem")

  3. #3
    Join Date
    26.10.2009
    Posts
    20

    Default Re: Access array variable from VBA

    That was not what I ment.. I would like to have the array variable and index in the VBA code. Something like this:

    Dim var As Variable
    Dim val As Double
    Dim index As Integer
    Set var = ThisProject.Variables.Item("testarray")
    
    val = var.SubItem(index).Value
    
    ' Instead of:
    Set var = ThisProject.Variables.Item("testarray[" & index & "]")
    
    val = var.Value
    By using the SubItem way it seems to be easier to loop through an array with unknown size. ArraySize can then be used to limit the loop.

  4. #4
    Join Date
    30.05.2007
    Location
    Salzburg
    Posts
    879

    Default Re: Access array variable from VBA

    This issue have been discussed in following thread: http://www.copadata.com/forums/showthread.php?t=973

    Short translation:
    Arrays itself are complex variables, which do have information like ArraySize, StartOffset aso. for the editor.

    In the Runtime only the single elements of the arrays are available not the array structure (as you maybe know it with VBA -> like Ubound(myArray))

    With the help of a function the array size of the instance can be found out....
    -> /snip VBA code....

    Regards,
    Herbert

  5. #5
    Join Date
    26.10.2009
    Posts
    20

    Default Re: Access array variable from VBA

    Ah okay. I skipped that thread because it wasn't in English

    I have used a different workaround:

    Public Sub loopArray()
        Dim var As Variable
        Dim count As Integer ' loop counter
        Dim done As Boolean ' done flag
    
        done = False
        count = 0
    
        While done = False
            count = count + 1 ' arrays starts at 1        
            Set var = ThisProject.Variables.Item("arrayVar[" & count & "]")
            
            If (var Is Nothing) Then ' All have been looped through
                done = True
            Else
                If var.IsOnline Then
                    ' insert code here 
                End If
            End If
        Wend
    End Sub

    This will off course not work if there are deactivated elements somewhere in the array structure but it does not need to loop up to a defined maximum even if the array is much smaller.

Similar Threads

  1. Variablen Array Zugriff
    By michael.grauer in forum VBA
    Replies: 2
    Last Post: 8th March 2010, 15:06
  2. Access to operating system
    By michael_k in forum zenon Supervisor
    Replies: 4
    Last Post: 13th January 2010, 05:22
  3. Variablen-Array vergrößern
    By elric in forum zenon Supervisor
    Replies: 2
    Last Post: 12th March 2009, 16:05
  4. Control array
    By ziadrached in forum VBA
    Replies: 1
    Last Post: 3rd December 2008, 15:08
  5. VBA and Microsoft Access
    By bjoerndavid in forum VBA
    Replies: 2
    Last Post: 30th November 2007, 22:22

Posting Rules

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