Using Arrays VBA

[AD_Taylor]

Is it possible to pass the entire contents of an array as a parameter when you call a Sub/Function?

E.g. I have an array with 5 elements in Sub CallThis()

I want to call another Sub, Sub DoThis().

To run properly DoThis() needs to know all 5 elements in the array. It would be ideal if DoThis could accept it and use it in an array format as well.

Thanks,
Adam

 

[Sandeep Warrier]

If DoThis always requires five parameters then one way to do this would be

Sub CallThis()

    'do stuff

    DoThis arrTest(0), arrTest(1), arrTest(2), arrTest(3), arrTest(4)

    'do stuff

End Sub

Sub DoThis(Arr1 As Variant, Arr2 As Variant, Arr3 As Variant, Arr4 As Variant, Arr5 As Variant)

    '
    'do stuff
    '

End Sub

 

[RoryA]

Just pass the array:

Sub CallThis()
   Dim arr(1 To 5)
   Dim n As Long
   For n = 1 To 5
   arr(n) = n
   Next n
   DoThis arr
End Sub
Sub DoThis(arr())
   Dim n As Long
   Dim strOut As String
   For n = LBound(arr) To UBound(arr)
      strOut = strOut & vbCrLf & arr(n)
   Next n
   MsgBox Mid(strOut, 2)
End Sub

 

[AD_Taylor]

Thanks for the reply,

Thats how I have it set up at the moment but I would have liked it to be more dyanmic.

As a bit of background, its for a username/password function.

The first Sub sets an array of usernames, then the function checks these against the username entered by the user and the password again entered by the user.

The problem currently is if I add a new username, then I have to add a new parameter to the function and add an extra check to the function to check this parameter.

It'd be great if I could just do a For LBound(userNames) To UBound(userNames) within the function...

EDIT: Didn't see Rorys reply - I think I tried this and it didn't work...

I'll give it another go and get back to you

 

[Sandeep Warrier]

If you are checking for valid usernames from a list, why don't you use a (hidden) worksheet for this?

You can just use a lookup against the username range.

 

[AD_Taylor]

Just pass the array:

Sub CallThis()
   Dim arr(1 To 5)
   Dim n As Long
   For n = 1 To 5
   arr(n) = n
   Next n
   DoThis arr
End Sub
Sub DoThis(arr())
   Dim n As Long
   Dim strOut As String
   For n = LBound(arr) To UBound(arr)
      strOut = strOut & vbCrLf & arr(n)
   Next n
   MsgBox Mid(strOut, 2)
End Sub

Thanks for this Rory,
Works fine

I think the problem I was having before was caused by me creating my arrays explicitly as strings.

 

[pgc01]

I think the problem I was having before was caused by me creating my arrays explicitly as strings.

Hi Adam

That should not be the problem. YOu can pass an array of strings if you want.

Ex.:

Sub CallThis()
   Dim sArr(1 To 5) As String
   Dim n As Long
 
   For n = 1 To 5
       sArr(n) = VBA.Array("a", "b", "c", "d", "e")(n - 1)
   Next n
   DoThis sArr
End Sub
 
Sub DoThis(arr() As String)
   Dim n As Long
   Dim strOut As String
 
   For n = LBound(arr) To UBound(arr)
      strOut = strOut & vbCrLf & arr(n)
   Next n
   MsgBox Mid(strOut, 2)
End Sub

Remark: this may not work in old versions in the Mac