[[20190925124914]] 『evaluateの使い方』(skyblue) >>BOT

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『evaluateの使い方』(skyblue)

初めてevaluateを使いました。

 Result = [SUM(a)]のところで
「型が一致しません」とのエラーが出ました。
rangeの使い方が悪いのかもしれません。
ご指導のほど、お願いします。

Sub t20190925()

    Dim Result As Long
    Set aa = ThisWorkbook.Sheets("test")
    Set a = Range(aa.Cells(2, 5), aa.Cells(3, 5))
    Result = [SUM(a)]
    MsgBox Result
End Sub

< 使用 Excel:Excel2016、使用 OS:Windows10 >


 素直に書けばいいと思うけど・・・。

 文字 = "=sum(A1:A2)"
 MsgBox Application.Evaluate(文字)
(BJ) 2019/09/25(水) 13:23

BJさん有難うございます

 データが少ない場合はA1:A2などを使っても良いかと思いますが、多数使用する場合、例えば列が26を超える場合などは厄介です。

 原因と対策が有ればよろしくお願いします。 
(skyblue) 2019/09/25(水) 13:32


こうなら書けますが、オブジェクト変数に代入してしまっているので…。
    Result = aa.[sum(E2:E3)]

または、こうも書けますよ。

    Result = aa.Evaluate("sum(" & a.Address & ")")

でも、Evaluateは判りやすいとは思わないので、以下のように書いてはどうでしょうか?

    Result = WorksheetFunction.Sum(aa.Range(aa.Cells(2, 5), aa.Cells(3, 5)))

今回の場合、いちばん判りやすいのは、以下だと思います。

    Result = WorksheetFunction.Sum(a)
(???) 2019/09/25(水) 13:32

 ぶつかり。

 意味が良く解ってないけど、数式で使える文字にすればいいだけ。
 address でも使えば・・・。

 (なんか知らないけど、ぶつかった後に書きこんだつもりなのに書き込まれてなかった。)
(BJ) 2019/09/25(水) 13:40

 過去スレですが

http://www.excel.studio-kazu.jp/kw/20070212134849.html

 『Evaluate Method の研究』
 が参考になると思います

 [] を使った場合には、変数は使えないということのようです

 BJさんの言う通り
 シートの数式で変数が使えないのと同じですね

 ただ「名前」は使えるので、下記のようなことはできるようです

 Sub t20190925()

    Dim Result As Long
    Dim aa As Worksheet
    Dim a As Range
    Set aa = ThisWorkbook.Sheets("Sheet1")
    Set a = Range(aa.Cells(2, 5), aa.Cells(3, 5))

    a.Name = "b"

    Result = [SUM(b)]

    MsgBox Result
 End Sub

(渡辺ひかる) 2019/09/25(水) 13:58


???さん 今回もありがとうございます。

 Result = WorksheetFunction.Sum(a)

は従来使っていましたが、[]は変数の相性が悪いという事でしょうかね。

  しばらく記載が出来ませんでした。

渡辺ひかる さん 有難うございました。

 サンプルコードまで記載いただき感謝します。

[]で楽が出来ない事が分かりました。

 皆さん大変お世話になりました。
(skyblue) 2019/09/25(水) 14:11


渡辺ひかる さんの

    a.Name = "b"

    Result = [SUM(b)]

 を走らせて、きちんと結果が出せました。

 今後使わせていただきます。

(skyblue) 2019/09/25(水) 14:25


 心配なんだけど。

   a.Name = "b"
   Result = [SUM(b)]

 これの意味解っているのかな?
(BJ) 2019/09/25(水) 14:33

 >これの意味解っているのかな?

 サンプルで書いたつもりでしたけど そのまま使われてしまうとは思わなかったです。

 これって 「名前」がどんどん追加されていきますし
 同じ名前の競合なども予想されますから、その辺の管理をきちんとしないとダメですね。

 できることと、使えることは 違いますから。

 変数のように 終了すれば消えるというものではないので、
 質問者さんは注意してくださいね。

(渡辺ひかる) 2019/09/25(水) 15:09


渡辺ひかるさん ありがとうございました

 終了したと思った後もアフターケアで見なければならないことが分かりました。

ご指摘の点は確認してみます。

 a.Name = "b" は初めて見るので感心しました。

??? さんの Result = WorksheetFunction.Sum(a) がコードは長いが、良いかなと思うし、

じつはこれまでこの方法でやっていたのですが、ここで得た知識を色々試してみたいですね。

(skyblue) 2019/09/26(木) 08:44


すでに指摘されていることですが、老婆心ながら。
>[]は変数の相性が悪いという事でしょうかね。
Evaluateは、ワークシート上での環境を想定したものです。
a と言う情報は、VBAのコード上のものですから、
それを使って評価することはできません。
コンテキストが特殊なものであって、
VBAのコード全般を評価(evaluate)できるわけではありません。
そこをきちんと抑える必要がありますね。

(γ) 2019/09/26(木) 08:52


 >??? さんの Result = WorksheetFunction.Sum(a) がコードは長いが、

 コードの長さが問題なら、こんなこともできますけど

 Sub t20190926()

    Dim Result As Long
    Dim aa As Worksheet
    Dim wf As WorksheetFunction

    Set aa = ThisWorkbook.Sheets("Sheet1")
    Set a = Range(aa.Cells(2, 5), aa.Cells(3, 5))

    Set wf = WorksheetFunction

    Result = wf.Sum(a)

    MsgBox Result
 End Sub

(渡辺ひかる) 2019/09/26(木) 09:55


>Result = WorksheetFunction.Sum(a) がコードは長いが、

wo

まで入れた後

Ctrlキー + Spaceキー 押下で、

入力候補の2番目に出てくるので、

↓矢印キーで選んで、
Tabキーで確定

とすればとりあえず、入力は楽になると思います。

ぼくは、
works
まで打ってからCtrl+Space としてますかね。

あと、もうちょい複雑な関数の組み合わせなんかするときは、

Sub test2()

    Dim Rng As Range

    With Range("A1").CurrentRegion
        Set Rng = Intersect(.Cells, .Offset(0, 0))
    End With

    With WorksheetFunction
        a = .Index(Rng, .Match("aaa", Rng.Columns(0), 0), _
                   .Match("あああ", Rng.Rows(0), 0))
    End With
End Sub

こんな感じで変数やWithを上手く使ったり、
横に長くなるときは、改行をしたりして、
入力の手間を省いたり、読みやすさを向上させるといいと思います。

この辺は、普通にコードの質問をしてもなかなか出てくる情報ではないので、
そういう(入力する文字数を少なくしたい的な)テーマで聞いた方がいいと思います。

セルに名前の定義を使うのもありと言えばありですが、
普通は、初期設定として手動でセルに名前を付けておいて、
それを固定値としてマクロで使うかなと思います。

テーマが、

『evaluateの使い方』
なので、そっちに言及すると、

シート上では、
「数式」をセルに、「文字」で入力しますよね?
それが、エクセル流の数式だと判断できる場合、
計算結果をセルに表示するようにエクセルは作られています。

その機能をVBA上で利用しようとするのが、
Application.Evaluate メソッドになります。

なので、セルに入力してエクセル君が数式と判断できる文字列を作って、
渡してあげないといけません。

つまり、
Set a = Range(aa.Cells(2, 5), aa.Cells(3, 5))
a.Name = "b"
というのはセル範囲に名前を付けて、

シート上のセルで、

=SUM(b)

というような数式が成立するように設定しています。

なので、
Evaluateメソッドでも同じように計算ができるようになるので、
強引にそういう作業を自動で行っているということです。

サンプルを教わって、「できた!」と感動しているだけでは、
先に進めないです。
なぜ?こうなるのか、
その1行1行の理解に努めていくとよいと思います。

参考URL>>
https://www.tipsfound.com/excel/01406

(まっつわん) 2019/09/27(金) 14:56


皆様、たくさんのご指導、アドバイス有難うございます。

解決できて終わったと思わず、何度もフォローする必要が有ると感じました。

 渡辺ひかる さんの次の方法は便利で早速使えます。

    Set wf = WorksheetFunction
    Result = wf.Sum(a)

 
(skyblue) 2019/09/28(土) 09:51


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.