VBAでアクティブじゃないシートから値を取得する方法

Question

###前提・実現したいこと
VBAでアクティブじゃないシートから値を取得するのに苦戦しています。

今、「sheet1」と「sheet2」という二つのシートがあり、「sheet1」をアクティブにした状態で「sheet2」のセルの値を取得し配列に格納するというようなことをしようと考えています。
ちなみに「sheet2」には下記のような情報がA1から入っています。

name
taro
hanako

これらのセルの内容を、下記関数を使用して取得します。

###セルの値を配列として返す関数

Function getNameList()
    Dim namesSheet As Worksheet
    Set namesSheet = Worksheets("names")
    
    Dim names() As String: ReDim names(0) ' Index 0で初期化
    
    Dim i
    For i = 2 To namesSheet.Cells(Rows.count, 1).End(xlUp).Row
        names(UBound(names)) = Cells(i, 1)
        ReDim Preserve names(UBound(names) + 1) ' 拡張
    Next

    ReDim Preserve names(UBound(names) - 1)
    
    getNamesList = names
End Function

「sheet1」をアクティブにした状態で下記のように実行すると、値が取得できず何も表示されません。

配列の中身を取得して表示

Sub main()    
    ' 名前一覧を取得
    Dim names As Variant
    Dim name As Variant
    names = getNamesList.getNamesList
    
    ' 名前の表示
    For Each name In names
        Debug.Print (name)
    Next name
End Sub

「sheet1」をアクティブにしたまま「sheet2」の値を配列として取得するにはどのような方法があるのでしょうか？
ご回答を頂けると非常に助かります_(..)

Answer 1

私の場合、namesSheet.Cells(i.1)ではなく、nameSheet.Cells(i.1).valueにした方が、セルの値を明示的に指定するのでこの方が良いと思います。

ちなみに、
Dim i
For i = 2 To namesSheet.Cells(Rows.count, 1).End(xlUp).Row
names(UBound(names)) = Cells(i, 1)
ReDim Preserve names(UBound(names) + 1) ' 拡張
Next
とありますが、

Dim names As Variant()
names = namesSheet.Range("A2:A" & namesSheet.Cells(Rows.count, 1).End(xlUp).Row)

だけで2次元の動的配列に一発で格納することができます。
これだと、ループもReDimも不要になる上、高速の処理が可能です。

詳しくは、高速化テクニック〜配列とセルの相互転記
をご覧ください。

Comments

[tarotarosu]

ありがとうございます_(._.)_
ご指摘いただいたように修正したところうまくいきました！
元々の書き方では冗長だと感じていたので助かりました！
ありがとうございました_(._.)_

Answer 2

FunctionのFor～Next内

names(UBound(names)) = Cells(i, 1)

これを

names(UBound(names)) = namesSheet.Cells(i, 1)

に変更してみたらどうでしょう？
元のコードではブックやシートの指定を省略しているのでアクティブシート(sheet1)を参照しているはずです。

Comments

[tarotarosu]

ありがとうございます_(._.)_
ご指摘いただいたように修正したところうまくいきました！
ありがとうございました_(._.)_