Excel(エクセル)VBAの関数、メソッド、ステートメント、プロパティの使用例やエラー発生条件などのサンプル・説明のまとめと定数やプロパティのまとめです。入門,初心者,初級レベルから始めています。
VBA オートフィルタの件数表示
AutoFilterで抽出したデータ数をカウントする
VBAを使ってオートフィルタで抽出したデータ数をカウントする方法です。
また、表示されている最終行番号も取得します。
関数、メソッドなど
XlCellTypeの定数を使用します。Range.SpecialCells(xlCellTypeVisible).Count
指定したセルの範囲(Range)に対して表示されているセル(xlCellTypeVisible)をカウントします。
サンプルコード
Sub autosu() Dim r As Long '最終行用 Dim countdata As Long 'データ数カウント用 '最終行を取得 r = Cells(Rows.Count, 1).End(xlUp).Row 'B列が東証1部のものを抽出 Range("a1").AutoFilter Field:=2, Criteria1:="東証1部" '表示されているデータ数を取得 countdata = Range(Cells(2, 1), Cells(r, 1)).SpecialCells(xlCellTypeVisible).Count MsgBox countdata End Sub
解説
まずはテーブルにある最終行番号を取得し、変数rに格納します。オートフィルタでデータ抽出後にテーブル範囲全体行に対して、表示されている行数をカウントします。
ただ、抽出されたデータ数が0の場合、
「実行時エラー'1004';
該当するセルが見つかりません。」
というエラーが出てしまいます。
これを回避するために
Range.SpecialCells(xlCellTypeLastCell)
で、抽出されたデータの最終行を調べてから分岐させます。
Sub autosu2() Dim r As Long '最終行用 Dim r1 As Long Dim countdata As Long 'データ数カウント用 '最終行を取得 r = Cells(Rows.Count, 1).End(xlUp).Row 'B列が東証1部のものを抽出 Range("a1").AutoFilter Field:=2, Criteria1:="東証3部" '表示されているデータの最終行を取得 r1 = Range("A1").SpecialCells(xlCellTypeLastCell).Row If r1 = 1 Then countdata = 0 Else '表示されているデータ数を取得 countdata = Range(Cells(2, 1), Cells(r, 1)).SpecialCells(xlCellTypeVisible).Count End If MsgBox countdata End Sub
関連・類似ページ
XlCellType列挙値の入っているセルのみを選択
最終セルの取得
VBA ブックの共有の解除
VBAを使ってエクセルブックのの共有の解除をする方法です。
エクセル上だと[リボン]→[校閲]→[ブックの共有]→[複数ユーザーによる同時編集と、ブックの結合を許可する]のチェックを外すことと同じ動作を再現します。
ステートメント構文など
WorkBook.UnprotectSharing(SharingPassword)
WorkBook.ExclusiveAccess
サンプルコード(パスワード設定なし)
Sub kyoyu() ActiveWorkbook.UnprotectSharing ActiveWorkbook.ExclusiveAccess End Sub
実行結果イメージ
解説
例としてActiveWorkbookに対して共有の解除をする処理をしています。UnprotectSharingは共有保護を解除しブックを保存するメソッドです。
が、
単体だとなぜか動きません。
そこでExclusiveAccessで共有ファイルとして開いているブックを他のユーザーが変更できないようにしています。
この二つのメソッドを実行すると保護の解除の処理にすすみます。
「このブックの保護を解除すると、ファイルの変更の履歴が削除されます。また、共有を無効にすると、上書きせずにこのファイルを保存することはできなくなります。このファイルの保護を解除してもよろしいですか?」
というコメントが出て、「はい」か「いいえ」を選択し「はい」の場合は保護を解除します。
このアラートを出したくない場合は事前にApplication.DisplayAlerts = Falseと記述しておけば、 アラートなしで共有化が解除になります。
サンプルコード(共有パスワード設定あり)
Sub kyoyu() ActiveWorkbook.UnprotectSharing Sharingpassword:="1234" End Sub
解説
UnprotectSharingのパラメータSharingpasswordを記述することによって エクセル上でいう「ブックの保護と共有」の「共有の解除」を実行します。
これで終わりです。
この場合はExclusiveAccessを記述していると
「ExclusiveAccessメソッドは失敗しました」というエラーが出てしまうので記述はいらないみたいです。
関連・類似ページ
ブックの共有化ブックの保存
警告や確認メッセージを非表示にする
'Open'メソッドは失敗しました
エラーメッセージ
「実行時エラー'1004':'Open'メソッドは失敗しました:'Workbooks'オブジェクト」
エラー内容
Workbooks.Open メソッド中に発生するエラーです。Open対象のファイルが壊れたりしている時に発生するようです。
エクセルファイルの回復が求められているパターンもありました。
原因エラー例
「'.xlsx'には読み取れない内容が含まれています。このブックの内容を回復しますか?ブックの発行元が信頼できる場合は、[はい]をクリックしてください。
対応
Open対象のエクセルファイルを手動で開いて中身に問題がないようであれば上書き保存すれば良いと思います。ダウンロードしたファイルがメモリ不足のため開けない
「メモリまたはディスク空き容量が不足ているため、ドキュメントを開いたり、保存したりできません。
・不要なブックやプログラムを終了して、メモリの空き容量を。
・ディスクに保存されている不要なファイルを削除して、ディスクの空き容量を増やしてください。」
というメッセージがダウンロードしたファイルを開いたときに出る場合がありました。
環境か設定によるのかわかりません。
このメッセージが出る場合はファイルを開くことが出来ません。
また、メッセージ通りに他のプログラムを終了してメモリに余裕を持たせても開くことが出来ませんでした。
対処方法としてはファイルを右クリックしてプロパティを表示。
「このファイルは他のコンピュータから取得したものです。このコンピュータを保護するため、このファイルへのアクセスはブロックされる可能性があります。」
という表記とその横に
「ブロックの解除」ボタンがあるはずです。
このボタンをクリックしてなんかしら解除すれば開けるようになるはずです。
オブジェクトがシートからはみだします。その操作はできません。
エクセル上で行の挿入や列の挿入をしようとした際に発生したエラーです。
発生条件といいますか、原因は身に覚えはありませんがたまに見かけます。
メッセージには「オブジェクト」と書かれていますがエクセルでいうオブジェクトを指してはいないかもしれません。
対象方法
1.オブジェクトの削除
一応メッセージ通りに対処する方法です。
エクセル上のオブジェクトはジャンプ機能で選択出来ます。
[Ctrl+G]で呼び出して[セル選択]をクリック、選択オプションで[オブジェクト]にチェックを入れてOKするとオブジェクトすべてが選択状態になります。
その状態で[Tab]キーを押せば、オブジェクトを順番にアクティブにするのでエクセル範囲の端の方にあれば移動させればいいと思います。
2.行や列の非表示部分を再表示にする
今回はこの方法でメッセージが表示されなくなり行の挿入が可能になりました。
単純にすべての行・列を選択してそれぞれ右クリックして[再表示]としただけです。
元々、非表示部分はなかったので何故これでエラーが出なくなったのか釈然とはしませんが…。
発生条件といいますか、原因は身に覚えはありませんがたまに見かけます。
メッセージには「オブジェクト」と書かれていますがエクセルでいうオブジェクトを指してはいないかもしれません。
対象方法
1.オブジェクトの削除
一応メッセージ通りに対処する方法です。
エクセル上のオブジェクトはジャンプ機能で選択出来ます。
[Ctrl+G]で呼び出して[セル選択]をクリック、選択オプションで[オブジェクト]にチェックを入れてOKするとオブジェクトすべてが選択状態になります。
その状態で[Tab]キーを押せば、オブジェクトを順番にアクティブにするのでエクセル範囲の端の方にあれば移動させればいいと思います。
2.行や列の非表示部分を再表示にする
今回はこの方法でメッセージが表示されなくなり行の挿入が可能になりました。
単純にすべての行・列を選択してそれぞれ右クリックして[再表示]としただけです。
元々、非表示部分はなかったので何故これでエラーが出なくなったのか釈然とはしませんが…。
実行時エラー '1004':権限のエラー
◯発生したタイミング
ODBCの接続があるブックの更新をしようとした際に発生しました。
デバッグ時の黄色部分
ActiveWorkbook.Connections(1).ODBCConnection.Refresh
◯原因
接続しているODBCConnectionのConnectionプロパティに指定してあったパスワード(PWD)が誤っているだけでした。
もしくは、元々エクセルブック上で単純にデータが接続されている状態に対してRefreshが実行された場合に発生します。
一度手動でエクセル上からデータの更新を行うか、VBA上でConnectionsでID・Passwordを指定すればエラー回避出来ると思います。
ODBCの接続があるブックの更新をしようとした際に発生しました。
デバッグ時の黄色部分
ActiveWorkbook.Connections(1).ODBCConnection.Refresh
◯原因
接続しているODBCConnectionのConnectionプロパティに指定してあったパスワード(PWD)が誤っているだけでした。
もしくは、元々エクセルブック上で単純にデータが接続されている状態に対してRefreshが実行された場合に発生します。
一度手動でエクセル上からデータの更新を行うか、VBA上でConnectionsでID・Passwordを指定すればエラー回避出来ると思います。
ファイルが見つかりません。:VBA6.0 (Error 53)
指定された場所にファイルが見つかりません。エラーの原因と対処方法を次に示します。
・Kill、Name、または Open などのステートメントで、存在しないファイルを参照しています。
・ファイル名とパスが正しいかどうかを確認します。
ダイナミック リンク ライブラリ (DLL) または Macintosh コード リソースでプロシージャが呼び出されましたが、Declare ステートメントの Lib 節で指定されている名前のライブラリ ファイル または リソース ファイルが見つかりません。
ファイル名とパスが正しいかどうかを確認します。
・開発環境で、存在しないプロジェクトを開こうとしたり、テキスト ファイルをロードしようとすると、このエラーが発生します。
プロジェクト名またはファイル名とパスが正しいかどうかを確認します。
詳細については、目的の項目を選択して F1 キー (Windows) または Help キー (Macintosh) を押してください。
対処方法:
ヘルプは参考にせず、別のファイルに標準モジュール・クラス・ユーザーフォーム等をそのままコピーして実行するとエラーは置きませんでした。
恐らく他の状況でも同様のエラーが出るのかもしれないですが、ファイルを変えてみると改善される可能性があると思います。