none
Excel VBA 式の入っているセル群に対して、標準モデュール内でRange.Valueで値を得ようとするとemptyになる。 RRS feed

  • 質問

  • https://answers.microsoft.com/ja-jp/msoffice/forum/all/excel-vba/7438c7b4-2da4-4096-85fb-c3db5479af87

    上記にも同じ投稿をしています。 こちらのフォーラムがVBA専門とご紹介いただき、投稿させていただきます。

    ※まだ初心者なのでリンクや画像は投稿できないとのことです。


    office 365を使用しています。

    プロの開発者ではないのですが、自分の業務でVBAをよく使っています。

    件名の問題で4日ほど費やしております。下記のtestRngPre関数をワークシート関数として利用すると、

    testRngを3回呼び出してイミディエイトウインドウには

    空です

    空です

    A

    と表示され、ワークシート関数としては動作するのですが、私が欲しいのは引数のv配列なのですが、

    私の実行したい別のプロシージャーからtestRngを呼び出すと、1回しか、呼び出されず、v配列はすべての要素がemptyとなることが、起きています。ちなみに、下記のtestRngPre2 testRngPre3 testRngPre4 をワークシート関数と利用してもこの現象は再現されません。

    どのようなcell群に対してこのようなことが起きるのかはわかっていませんが、cell群が式ではなく値の場合は、現象は発生しないようです。

    このような現象を経験された方がいらっしゃったら、アドバイスいただければ幸いです。

    また、ワークシート関数ですとなぜ、複数回呼び出されるのでしょうか。

    Public Function testRng(元Rng As Range, v配列() As Variant) As String 'Boolean

        Dim ws As Worksheet
        Set ws = 元Rng.Parent

        Dim nRw As Long, iniRw As Long, nClmn As Long, iniClmn

        nRw = 元Rng.Rows.Count

        nClmn = 元Rng.Columns.Count

        'Dim v配列() As Variant

        ReDim v配列(1 To nRw, 1 To nClmn)

        Dim tS As String

        iniRw = 元Rng.Row

        iniClmn = 元Rng.Column

        Dim i As Long, j As Long

        With ws

            v配列 = 元Rng.Value

            For i = 1 To nRw

                For j = 1 To nClmn

                    tS = tS & ws.Cells(iniRw + i - 1, iniClmn + j - 1).Value

                Next j

            Next i

        End With

        Set ws = Nothing

        testRng = tS

    End Function

    Public Function testRngPre(元Rng As Range) As String 'boolean
        Dim v配列() As Variant
        testRngPre = testRng(元Rng, v配列)
        If IsEmpty(v配列(1, 1)) Then
            Debug.Print "空です"
        Else
            Debug.Print v配列(1, 1)
        End If
    End Function

    Public Function testRngPre2(元Rng As Range) As String 'boolean
        testRngPre2 = testRngPre(元Rng)
    End Function

    Public Function testRngPre3(元Rng As Range) As String 'boolean
        testRngPre3 = testRngPre2(元Rng)
    End Function

    Public Function testRngPre4(元Rng As Range) As String 'boolean

        testRngPre4 = testRngPre3(元Rng)

    End Function


    • 編集済み 邉信 2020年4月5日 4:02
    2020年4月5日 1:21

すべての返信

  • 昨日までは、上記の関数をxlamファイルに入れていたのですが、

    そういえば、今まで同じような機能の関数をxlaファイルに入れて使用して

    このような現象が数年間おきたことがなかったと思いましたので、

    xlaファイルに入れて使用してみました。

    最初の数時間は上記現象が起きたのですが、

    今は、emptyになることはなくなり、問題なく動作しております。

    xlaファイルに変えたことが原因かよくわかりません。

    明日、関数をワークシートのあるブックに入れてみて問題なく動作するか確認するつもりです。

    2020年4月6日 10:22
  • Public Function testRng(元Rng As Range, v配列() As Variant) As String 'Boolean


        Dim ws As Worksheet
        Set ws = 元Rng.Parent

        Dim nRw As Long, iniRw As Long, nClmn As Long, iniClmn

        nRw = 元Rng.Rows.Count

        nClmn = 元Rng.Columns.Count

        'Dim v配列() As Variant

        ReDim v配列(1 To nRw, 1 To nClmn)

        Dim tS As String

        iniRw = 元Rng.Row

        iniClmn = 元Rng.Column

        Dim i As Long, j As Long

        With ws

            v配列 = 元Rng.Value

            For i = 1 To nRw

                For j = 1 To nClmn

                    tS = tS & ws.Cells(iniRw + i - 1, iniClmn + j - 1).Value

                Next j

            Next i

        End With

        Set ws = Nothing

        testRng = tS

    End Function

    よくわかりませんが、testRng プロシージャーは以下のような感じでいいのでは?

    Public Function testRng(元Rng As Range, v配列() As Variant) As String
        Dim v As Variant
    
        ' セル範囲の値を変数に代入
        v配列 = 元Rng.Value
    
        ' 配列の 1 次元目と 2 次元目を交換して For Each ループ
        For Each v In Application.WorksheetFunction.Transpose(v配列)
            ' 返す値を代入
            testRng = testRng & v
        Next
    End Function
    

    2020年4月7日 1:33
  • ご返信ありがとうございます。

    参考とさせていただきます。

    関数をワークシートのあるブックの標準モデュールとクラスもモデュールに入れても問題の現象は出なくなりました。

    原因はまったくわかりません。再現しないことを祈ります。


    2020年4月8日 6:53