[VBA]SpecialCellsが利かない？

質問

選択範囲で定数(数値)が含まれているセルを表示する自作関数を
作成したいと考えていますがうまく動いてくれません。
以下のサンプルコードを実行すると選択した範囲全てが表示されてしまいます。
初心者で申し訳ないですがご教示下さい。

■ 標準モジュールソース
---------------------------------------
Function SAMPLE(SelectRange)

For Each c In SelectRange.SpecialCells(xlCellTypeConstants, xlNumbers)
MsgBox c.Value
Next

End Function
---------------------------------------
→ 再現方法：A1に「1」と入力して、B1セルに「=SAMPLE(A1:A2)」と入力

よろしくお願いします。

回答No.2

mitarashi

ご提示のコードでは再現されますが、下記コードはCell.Countは1を与え、MsgBoxも一回しか表示されません。正常動作です。
理屈は分かりませんが、ユーザー定義関数内でMsgBoxという行為に無理があるのではないでしょうか。
Sub test()
Dim c As Range

Debug.Print Range("A1:A2").SpecialCells(xlCellTypeConstants, xlNumbers).Cells.Count
For Each c In Range("A1:A2").SpecialCells(xlCellTypeConstants, xlNumbers)
MsgBox c.Value
Next
End Sub

ところで、A列全体を対象にしたときとても遅いという件について試してみました。
Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub test2()
Dim c As Range
Dim cnt As Long
Dim myVal As Variant
Dim buf As Variant
Dim startTime As Long
Dim i As Long

myVal = 1
For Each c In Columns(1).Cells
If c.Value = myVal Then cnt = cnt + 1
Next c
Debug.Print "Cellにアクセス", GetTickCount - startTime, cnt

startTime = GetTickCount
cnt = 0
buf = Columns(1).Value
For i = 1 To UBound(buf, 1) 'xl2010なので 1048576 行
If buf(i, 1) = myVal Then cnt = cnt + 1
Next i
Debug.Print "配列に入れてアクセス", GetTickCount - startTime, cnt
End Sub

結果　時間の単位はmsecです。
Cellにアクセス 11625412 1
配列に入れてアクセス 94 1
Cellにアクセス 11628891 1
配列に入れてアクセス 78 1

速度差15万倍という結果でした。
ご参考まで。

回答No.1

noname#203218

指定セル範囲の最初の数値セルデータを取得したいのであれば下記方法で可能だと思います。
（文字列データは取得しません。）
ご参考まで。

Function SAMPLE(myRng As Range) As Double
Dim c As Range

On Error Resume Next
Application.Volatile

'セルが空白でなく、かつセルデータが数値である場合データ取得
For Each c In myRng
If c.Value <> Empty And IsNumeric(c.Value) Then
SAMPLE = c.Value
Exit For
End If
Next

End Function

補足

早急な対応有難うございます。
わざわざサンプルコードまでご教示頂きましたが残念ながら
希望の仕様とは異なるものでした。

具体的にやりたい事は、フィルタ結果から任意数値の出現回数をカウントしたいです。
（COUNTIFとSUBTOTALを合わせたもの）

ただ、上記仕様を簡単に実現すると

--------------------------------------------------------
Function SAMPLE(SelectRange As Range, Val As Integer)

For Each c In SelectRange
If c.Value = Val Then Cnt = Cnt + 1
Next

SAMPLE = Cnt

End Function
--------------------------------------------------------
となりますが、この場合仮に式が「=SAMPLE(A:A,1)」（A列全セルの"1"をカウント）の場合
とてつもなく時間がかかるため、可視セル且つ定数（数値）のみのセルで処理を
行いたいと考えています。

補足ですいませんが、何が意見等ございましたら
回答頂ければ幸いです。お忙しい中すいません。