Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


13347 / 13645 ツリー ←次へ | 前へ→

【5724】範囲に付けた名前を関数で使用したい! やまごん 03/5/28(水) 23:09 質問
【5732】Re:範囲に付けた名前を関数で使用したい! ichinose 03/5/29(木) 9:03 発言
【5736】Re:範囲に付けた名前を関数で使用したい! こうちゃん 03/5/29(木) 9:50 回答
【5775】Re:範囲に付けた名前を関数で使用したい! やまごん 03/5/30(金) 1:49 お礼
【5776】Re:範囲に付けた名前を関数で使用したい! こうちゃん 03/5/30(金) 8:20 発言
【5793】Re:範囲に付けた名前を関数で使用したい! やまごん 03/5/30(金) 23:01 質問
【5807】Re:範囲に付けた名前を関数で使用したい! こうちゃん 03/6/2(月) 9:14 回答
【5826】Re:範囲に付けた名前を関数で使用したい! やまごん 03/6/3(火) 0:44 お礼

【5724】範囲に付けた名前を関数で使用したい!
質問  やまごん  - 03/5/28(水) 23:09 -

引用なし
パスワード
   シート上で式を分かりやすくするために、セルに名前を定義しています。
範囲にも名前を付けているのですが、シート上の計算では使用できますが、関数で読めません。
いい方法ご存知の方いらっしゃいませんか?

[具体例]
A3からA10までの範囲に「DATA」と命名
B3からB10のセルに「=DATA+1」と記述すれば、Aの列の値に1を足した値を表示します。
function cal(X)
cal=x+1
end function
のマクロをつくり、C3からC10のセルに、=cal(data)としても、#VALUEとなります。

よろしくお願いします。

【5732】Re:範囲に付けた名前を関数で使用したい!
発言  ichinose  - 03/5/29(木) 9:03 -

引用なし
パスワード
   ▼やまごん さん:
おはようございます。
>[具体例]
>A3からA10までの範囲に「DATA」と命名
>B3からB10のセルに「=DATA+1」と記述すれば、Aの列の値に1を足した値を表示します。
配列使わなくてもできるんですね、知りませんでした(メモメモ)。

>function cal(X)
>cal=x+1
>end function
>のマクロをつくり、C3からC10のセルに、=cal(data)としても、#VALUEとなります。
'===================================================
Function samp(nm As Range)
  Dim rng As Range
  Dim cr As Range
  If nm.Count = 1 Then
    Set cr = nm
  Else
    Set rng = Application.Caller
    Set wk = Application.Intersect(rng.EntireRow, nm)
    If Not wk Is Nothing Then
     Set cr = wk
    Else
     Set wk = Application.Intersect(rng.EntireColumn, nm)
     If Not wk Is Nothing Then
       Set cr = wk
     Else
       Set cr = Nothing
       End If
     End If
    End If
  samp = cr.Value + 1
End Function

引数nmをVariant型にした場合は、事前にデータの型のチェックも必要ですね。

【5736】Re:範囲に付けた名前を関数で使用したい!
回答  こうちゃん E-MAIL  - 03/5/29(木) 9:50 -

引用なし
パスワード
   やまごんさん、こんにちは

ichinoseさん、こんにちは。VBAとしてはichinoseさんの解が正解だと思いますが、やまごんさんの「名前」や「範囲」の理解のためにあえて、横レスさせていただきます。

>A3からA10までの範囲に「DATA」と命名します。
>B3からB10のセルに「=DATA+1」と記述すれば、Aの列の値に1を足した値を表示します。

ワークシート上の計算式で「=DATA+1」のように入力すると、暗黙でその名前範囲の行が補完されると考えてください。
ですからこの例の場合で、B11セルに同様の計算式を指定すると#VALUE!エラーになります。

VBAで範囲名をつかうには、「暗黙の行」を明示的に指定すれば利用できます。
たとえば・・・

Function cal(x As String)
  cal = Range(x).Range("A" & ActiveCell.Row) + 1
End Function

のマクロをつくり、C3からC10のセルに、=cal("DATA")としてみてください。

C3セルにはA3セルの値に1を加算した値が表示されると思います。
この場合は名前範囲を外れた場合(C11セルとか)、Null+1で値は1となり、エラーにはなりません。

#通常VBAで名前範囲を使うのは WorkSheetFunction.Sum(名前範囲)のように、範囲全体を利用するケースが多いように思いますが・・

余計なお世話でしたらごめんなさい。

【5775】Re:範囲に付けた名前を関数で使用したい!
お礼  やまごん  - 03/5/30(金) 1:49 -

引用なし
パスワード
   みなさん、こんばんわ。
早速の回答ありがとうございます。

ichinoseさんのマクロ実行してみました。
まだ、マクロの中身を十分に理解していないのですが、要望どおりです。(^○^)
ありがとうございました。

さて、こうちゃんさんご回答に対してですが、B11のセルにデータを入力するとエラーが出るのは承知しています。
で、ご提案のマクロを作ってみました。
結果、
  A B C 
1 
2 
3 1 2 4
4 2 3 5
5 3 4 6
6 4 5 7
7 5 6 8
8 6 7 9
9 7 8 1
10 8 9 1
となってしまいました。
Aの列は、入力、BはDATA+1、cは=cal("DATA")です。
一応、ご報告まで・・・。

さて、VBAので名前の付け方の話ですが、私は、このように使用しています。
これは、変形四節リンクの角度を計算し、センサの電圧を求め、コントローラのデジタル値を求めるときに使用しています。
Aの列:角度データの入力。 [角度]
Bの列:リンクの他端に設定されたセンサの角度をマクロで計算。 [センサ角]
Cの列:角度を電圧に変換。 [センサ電圧]
Dの列:角度をデジタル値に変換 [デジタル値]
なんて感じです。[ ]の中は、範囲の名前です。
実際は、もっと色々なデータを表示させています。
数年経っても分かるように、他の人が見ても何をしているのか分かるように、各列に名前を付けて、計算式を分かりやすくするように努力しています。

今回の話では、C、Dのセルの計算は出来るが、Bのセルの計算には、$A3なんて記載しておく必要があるのが、面白くなかったわけです。
理解していただけましたでしょうか???

以上、ありがとうございました。

【5776】Re:範囲に付けた名前を関数で使用したい!
発言  こうちゃん E-MAIL  - 03/5/30(金) 8:20 -

引用なし
パスワード
   やまごんさん、こんにちは

>さて、こうちゃんさんご回答に対してですが、B11のセルにデータを入力するとエラーが出るのは承知しています。
>で、ご提案のマクロを作ってみました。
>結果、
>  A B C 
>1 
>2 
>3 1 2 4
>4 2 3 5
>5 3 4 6
>6 4 5 7
>7 5 6 8
>8 6 7 9
>9 7 8 1
>10 8 9 1
>となってしまいました。
>Aの列は、入力、BはDATA+1、cは=cal("DATA")です。
>一応、ご報告まで・・・。

すみません、例題だと思い、手を抜いてました。
名前範囲の先頭位置を考慮していませんでした。
具体例はこんな感じにすべきでした・・
Function cal(x As String)
  cal = Range(x).Range("A" & ActiveCell.Row - Range(x).Row + 1) + 1
End Function

【5793】Re:範囲に付けた名前を関数で使用したい!
質問  やまごん  - 03/5/30(金) 23:01 -

引用なし
パスワード
   ▼こうちゃん さん:
ありがとうございます。
他の方も必要とされるかもしれないと思いましたので、ご報告した次第です。

今回は、ちゃんと動きました。
このマクロは、簡潔なので、非常に気に入りました。

ところで、質問があるのですが、
Function cal(x As String)
  cal = Range(x).Range("A" & ActiveCell.Row - Range(x).Row + 1) + 1
End Function
の"A"の意味は、
今回定義したdataが一列の配列なので、配列の一列目という意味ですよね。
もし、dataが2列の配列であった場合、2列目の値を取得するには、”B"とすればよいのですね。

で、ActiveCellでは、そのセルを選択しないと値が変化しません。
コピー&ペーストでは、それぞれのセルがアクティブにならないので、それぞれのセルを選択し、一度アクティブにする必要があります。
コピー&ペーストで値が変更できる方法って無いのでしょうか?

別の質問にしたほうがよいのですかね・・・。

【5807】Re:範囲に付けた名前を関数で使用したい!
回答  こうちゃん E-MAIL  - 03/6/2(月) 9:14 -

引用なし
パスワード
   やまごんさん、こんにちは

>ところで、質問があるのですが、
>Function cal(x As String)
>  cal = Range(x).Range("A" & ActiveCell.Row - Range(x).Row + 1) + 1
>End Function
>の"A"の意味は、
>今回定義したdataが一列の配列なので、配列の一列目という意味ですよね。
>もし、dataが2列の配列であった場合、2列目の値を取得するには、”B"とすればよいのですね。
です。^^

>で、ActiveCellでは、そのセルを選択しないと値が変化しません。
>コピー&ペーストでは、それぞれのセルがアクティブにならないので、それぞれのセルを選択し、一度アクティブにする必要があります。
>コピー&ペーストで値が変更できる方法って無いのでしょうか?

ですから、VBAの関数としてはichinoseさんの回答が正解です。
ichinoseさんの回答の関数であればコピペでもいけますよね。
元の値が変更になった場合にも対応してますよ。

#私の回答は、あくまで「範囲」および「範囲名」に対する理解を深めるための「参考例」です・・^^;

【5826】Re:範囲に付けた名前を関数で使用したい!
お礼  やまごん  - 03/6/3(火) 0:44 -

引用なし
パスワード
   ▼こうちゃん さん:

こうちゃんさん こんばんわ

了解しました。
ありがとうございました。

13347 / 13645 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free