Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    490
    Thanks
    8
    Thanked 0 Times in 0 Posts

    subroutines, named arguments and arrays (Word 2K)

    I am an emergency-only VBA programmer and usually use recorded macros, tweeked a bit. Recently I have been optimising some old recorded macros and have a problem I can't solve: If I call a subroutine using named arguments how can I use an array?

    I have declared the variables, then call the subroutine DoStuff, which when no arrays involved might be:

    sub DoStuff Argument1:=True Argument2:=12, Argument3:="James"

    however what I want is an array with the values for Argument3 of "James", "Bill", "John"
    can I use the named argument syntax and set the values? I've tried various methods but failed each time

    help............... liz

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subroutines, named arguments and arrays (Word 2K)

    hi Liz,

    using named argument don't impact what you can pass to a subroutine and how you pass it.
    if you call a sub, use the name of that sub only :

    DoStuff Argument1:=True Argument2:=12, Argument3:=array("James","Bill", "John")

    or
    mynamarray=array("James","Bill", "John")
    DoStuff Argument1:=True Argument2:=12, Argument3:=mynamarray

    the sub dostuff could be declared as follows:

    sub dostuff(argument1 as boolean, argument2 as long, argument3 as variant)
    '...code that does various stuff with the arguments <g>
    end sub

  4. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: subroutines, named arguments and arrays (Word 2K)

    The word Sub is used when defining a procedure, not when calling it. You can pass an array as follows:

    DoStuff Argument1:=True Argument2:=12, Argument3:=Array("James", "Bill", "John")

  5. #4
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    490
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: subroutines, named arguments and arrays (Word 2K)

    Hans, Pieter,

    Thanks for the help, I seem almost to have got it but am getting an error, whichj I'm sure is very obvious if you do this all the time but as I don't...

    So I chose to declare the variables and use Pieter's second solution as otherwise I will have too many continuation lines. basically the code now does this:

    Private xxArg1(1 to 3) As String
    Private xxArg2(1 to 3) As String
    Private valuesxxArg1(1 To 3) As String
    Private valuesxxArg2(1 To 3) As String

    ' Prepare the arrays

    valuesArg1 = Array("Gill Sans", "Gill Sans", "Gill Sans")
    valuesArg2 = Array(14, 14, 12)

    ' call the subroutine

    DoStuff Arg1:=valuesArg1, Arg2:=valuesArg2

    '~~~~~~~
    sub DoStuff (Arg1, Arg2)


    On debug compile I get an error at the first line opf prepare the arrays: "Can't Assign to array"
    any ideas?

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

    Re: subroutines, named arguments and arrays (Word 2K)

    Declare valuesxxArg1 and valuesxxArg2 as variant (without indices):

    Dim valuesxxArg1 As Variant
    Dim valuesxxArg2 As Variant

Posting Permissions

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