Results 1 to 7 of 7
  1. #1
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Passing an Array as an Arument (VBA/Word/2000)

    Don,
    What is the code in CalledProcedure?
    Regards,
    Rory

    Microsoft MVP - Excel

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Passing an Array as an Arument (VBA/Word/2000)

    It may be because you have Dim'ed myArray() as a variable in the declarations area of your module rather than inside a particular procedure. Such "global" (I know this might be a loose use, hence the quotation marks) variables may retain value in an unpredictable manner.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Passing an Array as an Arument (VBA/Word/2000)

    <font color=red> Edited by author; Dim statement in Module 1 was misplaced. </font color=red>
    I am not certain whether I have a real problem or not. I have a set of procedures which contain the following code:

    In Module 1 <pre>Public Sub Main()
    Dim myArray()
    ' Some code
    Call Module2.CalledProcedure(myArray())
    ' Some more code
    End
    End Sub
    </pre>


    In Module 2 <pre>Public Sub CalledProcedure(subArray() As String)

    ' some code that redimensions subArray
    End Sub
    </pre>


    My apparent problem is that without the End statement in Module 1, each time the procedure is run the array becomes larger. That is: Run 1 causes the array to have 9 elements; Run 2: 18 elements; Run 3: 27 elements; etc.
    While the End statement cures the problem, I am not convinced that it is the most elegant solution. For instance; if for some reason the process is interrupted and terminated before reaching the End statement, will there be a problem the next time the procedure is run? Or is this phenomenon solely a characteristic of running in VBE?

    Any insight will be greatly appreciated.
    Regards
    Don

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing an Array as an Arument (VBA/Word/2000)

    Oops! Sorry for wasting your time Jerfferson. I prepared the original posted code from memory, and of course; got it wrong. Please see the edited version and my response to Rory.
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing an Array as an Arument (VBA/Word/2000)

    Hi Rory
    Thanks for the speedy response.
    Please note the correction I have made to my original post.
    The two modules are enclosed in the attached .Zip file.

    Module 1 is GraphicInserter
    The entry point is GraphicInserter.InsertGraphicsByBatch
    The call statement of interest is Call SearchDirectory.InsertGraphics(strPath, filenames())

    To explain what may not be immediately obvious; SearchDirectory is a general purpose module I use to capture file and directory information. The InsertGraphics procedure has been inserted in this instance to make it unnecessary to pass and maintain unnecessary arrays.
    Regards
    Don

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing an Array as an Arument (VBA/Word/2000)

    The problem is in the variable lFileCount. This is a module-level variable and it is not reset, so it increases each time the code is run. To remedy this, insert a line

    lFileCount = 0

    at the beginning of the Search procedure.

    Note: you can release the memory used by dynamic arrays when they are no longer needed as follows:

    Erase filenames

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing an Array as an Arument (VBA/Word/2000)

    Thank you Hans. That was sloppy of me.
    Regards
    Don

Posting Permissions

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