Excel VBA質問箱 IV

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

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


7123 / 13646 ツリー ←次へ | 前へ→

【41036】ユーザー定義関数内のFindについて ハチ 06/7/28(金) 9:58 質問[未読]
【41039】Re:ユーザー定義関数内のFindについて Jaka 06/7/28(金) 11:07 発言[未読]
【41042】Re:ユーザー定義関数内のFindについて ハチ 06/7/28(金) 11:40 お礼[未読]
【41044】Re:ユーザー定義関数内のFindについて ichinose 06/7/28(金) 11:47 発言[未読]
【41047】おまけ Jaka 06/7/28(金) 12:18 発言[未読]
【41050】Re:おまけ ハチ 06/7/28(金) 12:59 お礼[未読]
【41055】Re:おまけ Jaka 06/7/28(金) 13:31 発言[未読]
【41056】Re:おまけのおまけ ichinose 06/7/28(金) 13:38 発言[未読]
【41088】Re:おまけのお礼 ハチ 06/7/28(金) 20:31 お礼[未読]

【41036】ユーザー定義関数内のFindについて
質問  ハチ  - 06/7/28(金) 9:58 -

引用なし
パスワード
   おはようございます。

「範囲内で、値がある左端のColumnを求める」関数を
作ろうとしていたのですが上手くいかず悩んでいます。

質問1
下記のコードになるのですが、
test1とtest2の結果が違うのはなぜなのでしょうか?
test1のように"3"が入ることを期待しているのですが、
関数内のFind結果FiがNothingとなり"999"が入ってしまいます。
Function FindA_Col(ByVal 範囲 As Range) As Integer
とやってみたのですが上手くいきませんでした。

質問2
FindA_ColでFindの結果がNothingだった場合、
"999"ではなく「エラー値」を返したいと思っているのですが
記述の仕方がわかりません。
エラーとしては"#N/A"が適当ですか?

よろしくお願いします。

Option Explicit

'test1
Sub Find_test1()

Dim 範囲 As Range
Dim Fi As Range
Dim i As Integer

With ActiveSheet
  .Range("C1").Value = "aaa"
  Set 範囲 = .Range("A1:D1")
End With

Set Fi = 範囲.Find("*", , xlValues, , , xlNext)
If Not Fi Is Nothing Then
  i = Fi.Column
Else
  i = 999
End If

ActiveSheet.Range("A2").Value = i

Set Fi = Nothing
Set 範囲 = Nothing

End Sub

'Test2
Sub Find_test2()

With ActiveSheet
  .Range("C1").Value = "aaa"
  .Range("A2").Formula = "=FindA_Col(A1:D1)"
End With

End Sub


Function FindA_Col(範囲 As Range) As Integer

Dim Fi As Range
Dim i As Integer

Set Fi = 範囲.Find("*", , xlValues, , , xlNext)
If Not Fi Is Nothing Then
  i = Fi.Column
Else
  i = 999
End If

FindA_Col = i
Set Fi = Nothing

End Function

【41039】Re:ユーザー定義関数内のFindについて
発言  Jaka  - 06/7/28(金) 11:07 -

引用なし
パスワード
   Functionで、Findが使えるのは、2002以降だと聞いています。
2000や97だとまともに動かないので、Nothingになります。

>「エラー値」を返したいと思っているのですが
FindA_Col = CVErr(xlErrValue)
とか、他CVErr関数?をヘルプで見るとのっているかも?

因みにFindA_Colは、Variant型

【41042】Re:ユーザー定義関数内のFindについて
お礼  ハチ  - 06/7/28(金) 11:40 -

引用なし
パスワード
   ▼Jaka さん:

回答ありがとうごさいます。
>Functionで、Findが使えるのは、2002以降だと聞いています。
>2000や97だとまともに動かないので、Nothingになります。

なるほど。
Findを使わない方法を考えます。

>>「エラー値」を返したいと思っているのですが
>FindA_Col = CVErr(xlErrValue)
>とか、他CVErr関数?をヘルプで見るとのっているかも?
>
>因みにFindA_Colは、Variant型

CVErr(xlErrValue)で#VALUEになりました。
CVErrについて調べてみます。ありがとうございました。

【41044】Re:ユーザー定義関数内のFindについて
発言  ichinose  - 06/7/28(金) 11:47 -

引用なし
パスワード
   ▼Jaka さん、ハチさん、こんにちは。

>Functionで、Findが使えるのは、2002以降だと聞いています。
>2000や97だとまともに動かないので、Nothingになります。

Jaka さんのおっしゃるとおり、
Excel2002ではハチさんの提示されたコードは
きちんと 3を返してくれます。

Excel2000ではFindがユーザー定義関数としては、
駄目だということは覚えときま〜す。


>
>>「エラー値」を返したいと思っているのですが
>FindA_Col = CVErr(xlErrValue)
これは、

  FindA_Col = [na()]

こんな方法もありますよ!!

【41047】おまけ
発言  Jaka  - 06/7/28(金) 12:18 -

引用なし
パスワード
   ▼ichinose さん:
>  FindA_Col = [na()]
>こんな方法もありますよ!!
ただのエクセル関数も使えるんだ、知らなかった。

xlErrDiv0  #DIV/0!
xlErrNA   #N/A
xlErrName  #NAME?
xlErrNull  #NULL!
xlErrNum  #NUM!
xlErrRef  #REF!
xlErrValue #VALUE!

【41050】Re:おまけ
お礼  ハチ  - 06/7/28(金) 12:59 -

引用なし
パスワード
   ▼ichinose さん:
▼Jaka さん:

回答ありがとうございます。
バージョンを書き忘れていました。当方Excel2000です。
ウチの会社はまだまだ2000が全盛ですので大変、勉強になりました。

後学の為に教えて頂きたいのですが、
ichinose さんの[ ] の部分は
WorkSheet関数を使う為のモノなのでしょうか?
リンク式とか、Excel4.0マクロという意味ですか?
"[ VBA"でWeb検索すると大量に出てきて、正解に辿りつけなさそうです。
よろしければ、教えてください。

↓のコードでいくと
If WorksheetFunction.CountA(範囲.Columns(i)) > 0 Then の部分を
If [CountA(範囲.Columns(i))] > 0 Then としてみたのですが、
正常な結果を返すことができませんでした。

CountAで処理してみました。
'test3
Sub Find_test3()

With ActiveSheet
  .Range("C1").Value = "aaa"
  .Range("A2").Formula = "=FindA_Col2(A1:D1)"
End With

End Sub


Function FindA_Col2(範囲 As Range) As Variant

Dim i As Integer
Dim x As Variant

For i = 1 To 範囲.Columns.Count
  If WorksheetFunction.CountA(範囲.Columns(i)) > 0 Then
    x = 範囲.Column - 1 + i
    Exit For
  End If
Next i

If x <> 0 Then
  FindA_Col2 = x
Else
  FindA_Col2 = [NA()]
End If

End Function

【41055】Re:おまけ
発言  Jaka  - 06/7/28(金) 13:31 -

引用なし
パスワード
   省略しないで正確に書くと
Range("A5").Value = Application.Evaluate("NA()")
MsgBox Application.Evaluate("CountA(A10:A15)")

MsgBox [CountA(A10:A15)]

【41056】Re:おまけのおまけ
発言  ichinose  - 06/7/28(金) 13:38 -

引用なし
パスワード
   ▼Jaka さん:
>正確に書くと
>Range("A5").Value = Application.Evaluate("NA()")
>MsgBox Application.Evaluate("CountA(A10:A15)")

Jaka さんのおっしゃるとおりですが、

http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=8117;id=excel

ここも参考にしてください。

Evaluateを[・・・]こういう表記にするのは、

ここのサイトのサンプルコードの投稿では、Range("a1")が長いので

[a1]なんてする場合がありますけど、業務では

[now()]や[na()]などの引数がないものしか[・・・]の表記はしません。

特にVBAの関数のNow()と[now()]は、

微妙に違うので[now()]と記述すると役に立つこともありますよ!!

これに関しては、

http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=15011;no=15053;id=excel

とにかく、私は、便利に使っています。

【41088】Re:おまけのお礼
お礼  ハチ  - 06/7/28(金) 20:31 -

引用なし
パスワード
   ▼Jaka さん:
▼ichinose さん:

度々の回答ありがとうございます。
返信遅くなって申し訳ありません。

[ ] は、Application.Evaluateの略だったのですね!
といってもApplication.Evaluate自体を始めて知りました。
まだまだ、知らないことたくさんあります・・・

ヘルプを読んでも使いどころがよくわかりませんでしたが、
ichinose さんのリンク先の
>「指定された文字列式を評価した結果を返します」
これで解かったような気がします。

>特にVBAの関数のNow()と[now()]は、
>微妙に違うので[now()]と記述すると役に立つこともありますよ!!

こちらについても[now()]のほうが細かい時刻を取得できると理解しました。

表題からは脱線しましたが、大変勉強になりました。
ありがとうございました。

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