How to return Array in function in VBA - Excel

Question

the main goal is copying the worksheets name to an array, and array passes from function to main code to be able to track new added worksheets name to the array and work with them accordingly

Public Function CountWorksheets() As String
Dim i, size As Integer
Dim Arr() As String
size = Worksheets.Count
ReDim Arr(size)
For i = 1 To Worksheets.Count
    Arr(i) = Worksheets(i).Name
Next i
CountWorksheets = Arr
End Function



Private Sub RunLoc_Click()
Dim ar() As String
ReDim ar(Worksheets.Count)
j = 1
If Not IsEmpty(Location.Value) Then
    RunLoc.Enabled = True
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = Location.Value Then
            Worksheets(i).Select
            Range("a1").Select
        End If
    Next i
    ar = CountWorksheets()
End If
TextBox1.Value = Location.Value

End Sub

I expect to have the Arr and CountWorksheets with the same array recorded in both in function, and when CountWorksheets is called, the total of the array is copied to the new one which is named ar.

What's wrong with your code?

user8221156
– user8221156

2019-01-09 14:43:52 +00:00
Commented Jan 9, 2019 at 14:43 — user8221156
– user8221156, Commented Jan 9, 2019 at 14:43
ar = CountWorksheets() -> Error:Cant assign to array

Pooyan Haghpanah
– Pooyan Haghpanah

2019-01-09 14:48:00 +00:00
Commented Jan 9, 2019 at 14:48 — Pooyan Haghpanah
– Pooyan Haghpanah, Commented Jan 9, 2019 at 14:48

QHarr · Accepted Answer · 2019-01-09 14:59:28Z

1

You need to return a variant or String(). Also, adjust your array to start at 1

ReDim Arr(1 To size)

Otherwise you will have an empty position at start.Or, do the necessary substractions to start at 0.

Option Explicit

Public Function CountWorksheets() As Variant
    Dim i, size As Integer
    Dim Arr() As String
    size = Worksheets.Count
    ReDim Arr(1 To size)
    For i = 1 To Worksheets.Count
        Arr(i) = Worksheets(i).NAME
    Next i
    CountWorksheets = Arr
End Function

Private Sub RunLoc_Click()
    Dim ar() As String
    ReDim ar(Worksheets.Count)

    ar = CountWorksheets
End Sub

edited Jan 9, 2019 at 14:59

answered Jan 9, 2019 at 14:49

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return Array in function in VBA - Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related