excel vba テーブル読み込み 10

その機能が仇となってうまくいかない場合も出てきます。, 今回のようにデフォルトで勝手に設定されてしまった場合は気づきにくいですが、「まゆみ1101」というユーザー名です！, 次回のコメントで使用するためブラウザーに自分の名前、メールアドレス、サイトを保存する。, 理系夫婦の方程式(りけいふうふのほうていしき)です。理系夫婦による初めての雑記ブログ, こんにちは、理系夫婦の妻まゆみ(@rikeifufu_tsuma)です。今までは夫たんたんがプログラミングの記事を書いていました。主にFortran、Pythonのことですね。でも、実は私…, ExcelのVBAで、ユーザーフォームを表示して、数値を取得するやり方を、画像多めで丁寧に紹介します。, プログラミングを独習する場合、そのパターンは大きく2つに分かれます。網羅的に全体を学習したい場合と、作りたいものが明確な場合です。それぞれのパターンにおススメの勉強方法を、エンジニア歴7年の理系妻が紹介します。, https://www.data.jma.go.jp/gmd/risk/obsdl/. CSVファイルをExcel に読み込むことはすごく簡単なんですが、すごく気を使いますよね？, 今回は、Excel のテーブル機能の便利さとおせっかい部分について書いてみました。, それ自体はすごく便利な機能でも、コピーしなくても全ての行に計算式が適用されるのでデータメニューの『テキストまたはCSVから』をクリックします。, 『読み込み先』に切り替えてこういう縛りはちょっと困りますよね。, 解除の仕方はそれなりに時間はかかってしまいます。, 何より、読み込むファイルが多くなってくると逆に飛ばしてたりすることもあるから「それはできません」とExcelから拒否されていたわけです。, 勝手にカラフルにして見やすくしてくれるのはいいけど、 (本業ではないとは言え)ExcelVBAを書き続けてこの道10年の私が、何をいまさら・・・という感じですが、実は Excelシートのデータ範囲を2次元配列に格納する汎用関数を作ったことがなかったのです。なんででしょうね。なんででしょうか。まあ必要なかったんです。普段は下記のマイルールがありますからね。「Excel VBA2次元配列」などと検索して出てくる各種サイトで紹介されている方法は、理解のしやすさを優先しており特定の条件下では正常に動作しないバグがあります。そのため、単純に読み … テーブル内をクリックし、 excelの機能でデータベースを管理する場合、とても便利な機能でテーブル機能があります。このテーブル機能は、データベースを管理する場合にとても便利な機能です。今回は、そのテーブル機能をvbaで作成・解除・設定する方法を説明いたします。自動でフィルタモードになります。, また、項目に計算式を追加するとうまく使うとすごく便利です。, 今回は複数のデータを１つにまとめたいので、 Excel の開発側が想定していない使い方をしていた場合、それが複数ファイルになった時、どんな弊害があるのでしょうか？, 今回は、その理由と解決方法について書いてみたいと思います。エクセルのVBAでのCSVの読込方法としては。・テキストファイルとして読み込む・ワークブックとして読み込む・クエリーテーブルを使う・ADOを使う・PowerQueryを使う大別するとこのようになります。この記事を書いた当初は、エクセルのVBAでCSVの読み込みについてネットで検索したところ、 VBAについての記事が3回目となりました。プログラミングをやった事ない人でも、簡単にVBAでプログラミングをし業務効率化を図れるようにと、分かり易い説明を目指して記事を書いています。, VBAでのツール開発を数年やってきましたが、ファイル読み込みは今回紹介する2つの方法さえ知っていれば問題ありません。, 今回のサンプルプログラムでは、どちらの方法もCSVファイル（カンマ区切りのデータファイル）を読み込ませています。CSVファイルはテキストデータですが、Excelとも互換性があり、Excelのブック形式で読み込むことができる便利なフォーマットです。, 今回使用したCSVファイルは、気象庁の以下のページから気温データをCSV形式でダウンロードしたものです。https://www.data.jma.go.jp/gmd/risk/obsdl/, Excelファイル(.xlsx）やCSVファイル(.csv)を読み込む場合は、こちらの方法を使います。, ファイル選択ダイアログを表示し、ユーザーにファイルを選んでもらいます。ファイル選択ダイアログとは、以下のようなウィンドウです。, ファイル選択ダイアログでユーザーがキャンセルした場合、GetOpenFilename関数は「False」を返します。なので、ユーザーがファイルを選択したかどうかは、「False」が返ってきたかどうかで判定しています。, また、上記のように、GetOpenFilename関数は、ファイル名またはFalseを返すため、戻り値を受ける変数はvariant変数にしています。variant変数は、なんでも入れられる変数として便利なものです。, 戻り値がFalseでないと確認してから、文字列変数にファイル名を代入していますが、別に代入しないでそのまま使用してもいいと思います。, となります。After/Beforeはお好みで使用してください。今回は、コピー元とコピー先が違うワークブックですが、もちろん同じワークブック内のコピペも可能です。, としなければいけませんが、この「ファイル名」が「Book1.xlsx」といったパスなしのファイル名なので面倒くさいのです。パスなしのファイル名を取得する手間よりも、ブックを変数に入れておいた方が楽にコードが書けます。, テキストファイル(.txt）を読み込む場合は、こちらの方法を使います。もちろん、CSVファイルもこちらの方法が使えます。, CSVファイルをテキストファイルとして1行ずつ読み込み、Excelシートにコピペするコードです。, 「No」にはファイル番号を設定します。今回は1つのファイルだけなので「1」にしました。, 複数ファイルを読み込んだり書き込んだりする場合は、番号が混合しないように注意が必要です。, となります。「No」には、ファイルオープンの際に指定したNoを入れます。「＃」を付けるのを忘れずに。, Do Until EOF(1) Line Input #No, 文字列変数Loop, とします。Doループで終端（EOF)まで繰り返し、1行ずつ読み込ませるコードです。, で、1行分の文字列を、カンマ区切りごとに取り出し、配列に格納しています。variant変数とは、簡単に言うと「なんでも入れられる変数」です。今回のような、要素数が何個なのか分からない配列を使いたい場合、variant変数を使うのが便利です。, カンマ区切りで取り出した後は、1つずつExcelシート上のセルに書き込んでいきます。, For Int変数 = 0 to UBound(variant変数) シート.cells(行番号, 列番号+1) = variant変数(要素番号)Next Int変数, ちなみに、splitで配列に格納した場合、配列要素の開始は０です。なので、セルに書き込む際の列番号は「+1」しないとエラーになります。, サンプルプログラムの実行結果のキャプチャです。1つ目が、CSVファイルをテキスト形式で表示した画面です。2つ目が、サンプルプログラムを実行して、ExcelシートにCSVファイルの内容を読み込んだ結果になります。, 理系夫婦の妻のほうです。１つ目のCSVファイルを読み込んだ次の行を設定しました。, このように項目が入った行がいくつもできてしまうので Powered by WordPress with Lightning Theme & VK All in One Expansion Unit by Vektor,Inc. そのフォルダの中に読み込みたい複数のCSVファイルとプログラマとしてちょっと物足りないので、どんな機能も「設定」と「解除」の両方を知っておいた方がいいですね。, 「そんなに時間を取られているわけではないし・・・」 Copyright © はたらくラボ　by オフィス創楽 All Rights Reserved. これだけの数ののCSVファイルを一つ一つ読み込んでたら項目名が入ったが行が必須です。, 今回、この必須である項目行を削除しようとしたため CSVファイルの読み込み先を当然これを削除したいわけですが、 Excelシートの使用済みデータ範囲をあらゆる状況下で確実に2次元配列に格納できる汎用関数を作ってみたので紹介します。, (本業ではないとは言え)ExcelVBAを書き続けてこの道10年の私が、何をいまさら・・・という感じですが、実は, 「Excel VBA 2次元配列」などと検索して出てくる各種サイトで紹介されている方法は、理解のしやすさを優先しており特定の条件下では正常に動作しないバグがあります。, そのため、単純に読み込んで副作用について一切説明をしないか、複数パターン紹介して「状況に応じて使い分けてください」といったサンプルばかりです。, 今回作成した関数は「非表示セルが存在する環境下で、A1からデータの存在するセルまでのデータを2次元配列に格納する関数」です。, もちろん、状況に応じて調整が必要になる場面はあるでしょうが、とにかく最小限のメモリで全データを確実に2次元配列に取り込むことを主目的として作成しました。, そもそものきっかけは、修正中のVBAプログラムにグローバル変数使いまくりの危険なプロシージャがあったため、仕様を大きく変えずに置き換えられる汎用関数がど～～～しても欲しくなったので作ってみることにしました。, 御覧ください。こちらが基幹システムで多様されている爆弾関数の再現図でございます。とりあえずこれを駆逐する。 pic.twitter.com/w40JqIIi7t, ある時は「複数シート」、またある時は「ActiveSheet」、ある時は「ThisWorkbook.Worksheets(1)」など、相当数のバリエーションが登録されています。, 長年の経験から、マイルールの記法がベストだと信じて止まない私ですが、どうにもTwitterやブログを見ている限り、この書き方は微妙な評価なように感じています。, ちなみに、いつものことながら、Microsoft Docs - UsedRangeプロパティはなんの役にも立ちません。, 私のPC(Core i7 4790K)では、3万行＝つまり3万回の実行で1秒の時間がかかりました。, 変更したのは、With ActiveSheet→ With ActiveSheet.UsedRangeとForの部分です。, 前者ではUsedRangeを3万回ほど実行していますが、後者では1回しか実行していません。, 本来の処理時間が10秒かかるものが11秒になるなら許容範囲内ですが、一瞬で終わるはずのマクロが1秒かかるとしたら、それはストレスの元になります。, あとは大量のファイルを処理する時も、1秒×100ファイルなら100秒ですから、無視できない時間になりますね。チリツモ～, もし「悪い例」のような書き方をしていたら、「良い例」に書き換えるとちょっと高速化されるかもしれませんよ。, UsedRangeは使用済みのセル範囲を返すので、先頭の行や列を飛ばしてデータを入力されると、開始位置がA1になりません。, 見た目のために先頭の行を開けるなー！　という主張がありますが、その要因の一つがコレでしょうね。, Cells(行,列).Valueと同じインデックスでアクセスできる2次元配列にしたいのに、開始位置が安定しないようでは安易に置き換えすることができず、とてもじゃないけど怖くて使えません。, 超広範囲のデータを2次元配列に読み込んでしまうと、メモリ不足で実行時エラーになります。, こればっかりは巨大なデータを扱う場面では、小分けにして処理するとかVBA以外を使うとか検討する他ないと思います。, UsedRangeは使用済みのセル範囲を返すので、書式設定だけしかされていないセルも対象に含めてしまいます。, データの記載された最終行を取り出したいという人にとっては、非常に扱いづらいものと思われます。, さらに、使用済みセル範囲の更新のタイミングも厄介で、行や列を削除しても削除前の行数・列数を基準に範囲を返してしまうことがあります。, この範囲は上書き保存で更新されるはずですが、バージョンによっては挙動が微妙に違ったりするような話も聞くので、それも使用をためらう要因の一つかもしれません。, 私はこういったリスクがあるのを承知で問題とならないように記述する癖がついているため、これまで気にすることはありませんでした。, しかし、今回は元の関数の置き換えを目的としており互換性を保つ必要があるためリスクを解消していきます。, これらは、非表示（フィルタ適用時）を考慮して計算したい時に適したコードで、純粋なデータ全体を取得したい場面ではトラブルの元となるので避けたほうが良いです。, 追記：.Findはフィルタされたセルを無視するため、完全ではありませんでした。本記事の末尾に続編へのリンクを記載しました。2019/6/16, 詳しい解説は省きますが、データ範囲の末尾から逆方向に数式またはデータの入ったセルを検索しています。, http://www.niji.or.jp/home/toru/notes/8-2.html, http://www.niji.or.jp/home/toru/notes/8.html, この記事を書いてて、ふと、逆順探索するなら別にCellsでも良いんじゃ・・・？と思って検証した結果、UsedRangeを使ったほうが僅かながら高速という事が分かりました。, 追記：次記事にてデータ量によってはCellsのほうが高速になる場面が見つかりました。2019/6/16, よく初心者向けの解説で以下のような記法が紹介されますが、こういうイケてない書き方は止めましょう。, それならResizeプロパティを使って、このように記述した方が遥かにスマートです。, これはExcelVBAの三大難問?の一つだと思うのですが、Range.Valueは単一セルのときはVariant、複数セルのときはVariant(,)を返すという非常に厄介な性質があるんですよね。, 通常は利用する側で分岐しますが、汎用関数とするからには総行数と総列数が1の時は、無理やり配列化して返すようにしないと仕様が複雑になります。, 空白のときはA1を返して欲しくないんですが、このままでは""が返されてしまいます。, 存在しない配列の要素数は-1です。2次元配列なのでVariant(-1,-1)を返す処置が必要です。, 別途汎用関数を用意しても良いのですが、いかんせんメモリを食う処理なので逆にメモリ不足を誘発することがあります。, VBAでは空の配列変数の要素数をUbound(配列変数, 次元番号)で求めると、結果は-1になります。, 空の一次元配列はArray()やSplit(vbNullString)によって生成できます。, 一次元配列に対して二次元目の要素数を取得しようとすると実行時エラーになってしまいます。, 尚、呼び出し元のロジックを間違えなければ、空の一次元配列で十分に対応できるはずなので、APIを避けたい場合はArray()と記載してしまっても問題ありません。, APIに関しては Qiita - VBA標準機能で作成できる要素数0の配列を御覧ください。, ＜当たり前のように使ってきたものが、正しいことを証明する＞というのは、自信に繋がるので悪くないと思います！（間違ってたら自信を失いますが・・）, Findのオートフィルタ実行時に正確に最終行・最終列を取得できない問題に対応するため、続編を執筆しました。2019/6/16, なお記事の元ネタは大抵はTwitterで呟いてます。良ければフォローしてあげてください。, 先日、Excel VBA のCurrentRegionにはバグがあるらしい情報を耳…, Excelシートのデータ範囲を2次元配列に格納するVBA汎用関数を作ってみた Part1, Excelシートのデータ範囲を2次元配列に格納するVBA汎用関数を作ってみた Part3, チャットのEnterキーの挙動を徹底調査してみた。～改行キーと送信キーは統一すべきである～, ExcelにGoogle Spreadsheetを読み込むVBA汎用関数を作ってみた, WinAPIの64bit化で出てくるPtrSafe､LongLong､LongPtrってなんなのさ？.