Office TANAKA - Excel VBA Tips[Split関数で文字列を区切る]
※文字列を、区切り文字ではなく、位置で分割するやり方は「文字列を位置で分割する」をご覧ください。 Split関数は、ある区切り文字で区切られた文字列を、その区切り文字で区切り、区切られた各文字列を、一次元配列として返します。言葉で表すとややこしいですが、要するに次のような処理をしてくれます。 Split関数の書式は次の通りです。 Split(元の文字列 , 区切り文字) 上の例なら、次のようになります。 Split("123,田中,tanaka" , ",") Split関数は配列を返します。したがって、それを受け取る変数は、バリアント型変数か、動的配列でなければいけません。一般的にはバリアント型変数に受けます。Split関数の返り値(配列)を受け取ったバリアント型変数は、それ以降、配列として操作できます。 このとき注意しなければいけないのは、Split関数が返す配列の要素は0から始まると
Office TANAKA - VBA講座：ファイルの操作(ファイルの一覧を取得する)
任意のフォルダに存在するすべてのファイル名を取得するには、Dir関数にワイルドカードを指定します。次のコードは、C:\Sampleフォルダに存在するすべての「*.xlsx」をアクティブシートに書き出します。 Sub Sample1() Dim buf As String, cnt As Long Const Path As String = "C:\Sample\" buf = Dir(Path & "*.xlsx") Do While buf <> "" cnt = cnt + 1 Cells(cnt, 1) = buf buf = Dir() Loop End Sub Dir関数にワイルドカードを指定して実行すると、まずそのワイルドカードに一致するファイル名が返ります。次に、Dir関数の引数を省略して実行すると、前回に指定されたワイルドカードが指定されたものとしてファイルを探します。そ
Office TANAKA - ワークシート関数[OFFSET 関数]
OFFSET関数に関して、詳しい説明や具体的な使い方などを動画で解説しています。ぜひ、ご覧ください。Youtubeでは、ほかにもたくさんの動画を公開しています。チャンネル登録をお忘れなく！ Office TANAKAチャンネル 書式 ：OFFSET(基準,行数,列数,高さ,幅) 機能 ：基準セルを指定した数だけシフトしたセル範囲を返します 解説 ：セル範囲を可変にするときに使います OFFSET関数の仕組み OFFSET関数は理解しづらい関数のひとつです。しかし、その仕組みを理解できれば、これほど役に立つ関数はありません。まず、OFFSET関数は何をどうする関数なのかを理解しましょう。 OFFSET関数の構文は次の通りです。 OFFSET(基準,行数,列数,高さ,幅) OFFSET関数に指定する引数は、次の3種類に分類できます。 1.基準となるセルを指定する引数→[基準] 2.基準セルを動
Office TANAKA - Excel VBA Tips[連想配列を使う]
のようにデータを格納しておいて、「Japan」というキーで「日本」を探し出す機能です。 「VBAで連想配列は使えますか？」とか「VBAには連想配列がないからな～」などという話をたまに聞きますが、Excel VBA使いとしては「？？」という気がしてなりません。だって、いつもワークシート上で普通にやってることですから。 Excel VBAに連想配列という機能はありませんが、データを変数ではなくワークシート上のセルに格納することで、連想配列のような使い方ができます。ちなみに、かなり高速ですよ。20,000件のデータをランダムに並べ、20,000件目のデータを検索するのに要した時間は20ミリ秒でした。CPUは1GHzでメモリは512MBの環境です。 ひとつだけマクロ例を提示しておきますね。 Sub Sample1() ''データの検索 Dim buf As String buf = InputBo
Office TANAKA - シートの操作[シートを開く]
シートを開くときは、SelectメソッドまたはActivateメソッドを使います。次のコードは、Sheet2を開いてメッセージを表示し、続いてSheet3を開きます。 Sub Sample01() Worksheets("Sheet2").Select MsgBox "Sheet2を開きました" Worksheets("Sheet3").Activate MsgBox "Sheet3を開きました" End Sub 厳密に言うと、SelectメソッドとActivateメソッドでは動作が異なりますが、そんなもの枝葉末節です。普通の人が普通のマクロを組むのでしたら「○○でなければダメ」など意識する必要はありません。ちなみに、Selectメソッドではシートをグループ化できます。次のコードはSheet1、Sheet2、Sheet3をグループ化します。 Sub Sample01_1() Workshe
Office TANAKA - Excel VBA Tips[フルパスをパスとファイル名に分ける]
先日メールで質問されたネタです。「"C:\Sample\Sub\Book1.xls"というフルパスを"C:\Sample\Sub\"と"Book1.xls"に分割するにはどうしたらいいのですか？」と。・・・当サイトではTipsなどで普通にやってますけど、あらためて「よくわからない」と言われたので独立したコンテンツとして解説しましょう。 ここでは次のようなファイルを対象にします。 基本的な考え方 まずは古典的なやり方から。この方法は手間がかかりますけど、応用すればいろいろなケースに応用できます。 パス＋ファイル名という文字列を「パス」と「ファイル名」に分けるには、どこで分割すればいいかを考えます。パスの区切りは「\」記号で表されますので、(1)文字列を後ろから1文字ずつ見ていって最初に存在する「\」の位置を調べなければなりません。次に(2)その位置から左側(パス)と右側(ファイル名)に分割し
Office TANAKA - Excel VBA Tips[実は奥が深いInputBox]
マクロ実行中にユーザーから文字列や数値を受け取るとき、最も簡単な方法のひとつはInputBoxを使うことです。次のコードは、ユーザーに名前を入力してもらい、入力された文字列をセルA1に書き込みます。 Sub Sample1() Dim buf As String buf = InputBox("名前を入力してください") Range("A1") = buf End Sub 特に難しいテクニックでもなく、ややもするとVBAの解説本でさえ紹介されないこともある基本ワザですが、実はこのInputBoxは意外と奥が深いんです。 InputBoxは2つある まず衝撃的な事実からお話ししましょう。Excel VBAで使えるInputBoxには次の2種類があります。 InputBox関数 ApplicationオブジェクトのInputBoxメソッド ひとつはInputBox関数です。文字列の左端を抜き出
Office TANAKA - ファイルの操作[ブックを閉じる]
ブックを閉じるには、WorkbookオブジェクトのCloseメソッドを使います。次のコードは、すでに開いている Book1.xlsx を閉じます。 Sub Sample1() Workbooks("Book1.xlsx").Close End Sub Windowsの「フォルダオプション」で、[登録されている拡張子は表示しない]がオンになっていると、エクスプローラでファイルの拡張子が表示されません。 このとき、Excelで開いているブックも、タイトルバーに拡張子が表示されなくなります。 【拡張子を表示する設定のとき】 【拡張子を表示しない設定のとき】 拡張子を表示する設定のとき、Workbooks("Book1").Closeのように拡張子を指定しないと、エラーになります。 つまり、こういうことです。 拡張子を ("Book1").Close ("Book1.xlsx").Close [フ
Office TANAKA - セルの操作[セル範囲の取得]
操作の対象セル範囲が不定の場合 ここまでの解説では、操作の対象を「セルA1に」とか「セル範囲A1:C3に」などと簡単に決めてきました。もちろん解説なのですから、それは勘弁していただきたいのですけど、実際のマクロ作成では操作の対象を特定することに苦労するものです。操作の対象セルを、どう的確に特定するかが、VBAのビギナーとベテランで差が出るところでもあります。たとえば、次のようなデータを考えてみましょう。 このデータをマクロでクリアします。ただし、データが何行何列あるかは、そのときによって異なります。セルをクリアするにはRangeオブジェクトのClearContentsメソッドを使います。次のコードはセル範囲A1:D5をクリアします。 Sub Sample1() Range("A1:D5").ClearContents End Sub クリアすべきセル範囲の左上はセルA1です。したがって、セ
Office TANAKA - VBA高速化テクニック[画面を止める]
この「VBA高速化テクニック」を書いたのは、1995年です。当時はパソコン通信でした。その後、インターネットの時代になり、私も自分のWebサイトを作りました。それがここ「Office TANAKA」です。Webサイトの公開当初には、まだコンテンツの数も少なかったので、パソコン通信時代に書いた「VBA高速化テクニック」をHTML化しました。それ以来、とても多くのExcelユーザーが、この「VBA高速化テクニック」を参考にしていただいています。それはそれで、とても嬉しいことなのですが。ただひとつ、どうも私の考えが"誤って"伝わっていると感じることも多いです。その典型が、本項「画面を止める」です。 よろしいですか？誤解のないようにハッキリ書きます。もし、画面がひんぱんに切り替わるようなマクロだった場合、そのときは、画面を止めるとマクロの速度が速くなりますよという話です。画面がひんぱんに切り替わる
Office TANAKA - VBA Tips[UserForm]
VBAコンテンツ Home Excel VBA Excel VBA Tips CommandButton 右クリックを判別する Shift＋クリックやCtrl＋クリックを判別する 状況に応じてボタンの表示を変える クリックできないようにする ListBox リストボックスにデータを登録する(1) リストボックスにデータを登録する(2) リストボックスにデータを登録する(3) 選択されているデータを取得する TextプロパティとValueプロパティの違い 複数選択可能なリストボックス リストボックスの中を検索する 複数のリストボックスを連動させる リストボックスを自動スクロールする TextBox 数字と通貨形式の書式を変換する テキストボックスの文字列を選択する テキストボックスの文字数を制限する 全角と半角を区別する ComboBox コンボボックスにデータを登録する OptionBut
Office TANAKA - シートの操作[名前を設定する]
シート見出しに表示されるシートの名前を設定してみましょう。 シートの名前はNameプロパティ シートの名前を操作するには、シートのNameプロパティを使います。次のコードはSheet1の名前を現在の時刻に変更します。 Sub Sample02() Sheets("Sheet1").Name = Format(Now, "hh時mm分ss秒") End Sub 既存の名前は設定できない 新しいシートの名前に、既存のシート名と同じ名前を設定することはできません。ひとつのブックに[Sheet1]が複数存在することは許されないのです。これから設定しようとしているワークシート名が、すでに他のシート名として使われているかどうか調べるには、すべてのシート名を次のようにチェックします。 Sub Sample02_2() Dim s As Variant, flag As Boolean For Each
Office TANAKA - Excel VBA Tips[入力されているデータの最終セルを取得する]
これは基本的なテクニックだと思って今まで解説しませんでしたが、セミナーなどで質問が多かったので、簡単に書いておきます。たとえば、下図のようなリストがあったとします。 この、現在入力されている最終セル(あるいは最終行)を取得するには、どうしたらいいでしょう。 Excelに詳しくない、VBAのプログラミング的なことしか知らないような方でしたら、次のように考えるかもしれませんね。「A列を上から順番に見ていって、空欄セルが見つかったら、その直前が最終セル」だと。その方法でやってみましょうか。 Sub Sample1() Dim i As Long, r As Long For i = 1 To 10000 Range("A" & i).Select If Selection = "" Then r = i - 1 Exit For End If Next i Range("A" & r).Sele
Office TANAKA - ファイルの操作[ブックを開く]
ブックを開くには、WorkbooksコレクションのOpenメソッドを使います。 Excelでは、同時に複数のブックを開くことができます。開いているそれぞれのブックはWorkbookオブジェクトとして操作できます。このWorkbookオブジェクトの集合体がWorkbooksコレクションです。新しいブックを開くということは、この集合体に、新しいメンバーを加えるということです。だからWorkbooksコレクションのOpenメソッドです。 次のコードは、C:\Book1.xlsxを開きます。 Sub Sample1 Workbooks.Open "C:\Book1.xlsx" End Sub Workbooks.Open Filename:="C:\Book1.xlsx" という書き方もできます。 「Filename:=」の意味や、つけるのとつけないのとでは何が違うかについては、下記ページを参考に
Office TANAKA - 今さら聞けないVBA[引数名って書かなくていいの？]
ブックを開くマクロとして、次のようなコードをよく見ます。 Sub Sample1() Workbooks.Open Filename:="C:\Book1.xlsx" End Sub あるいは、まったく同じブックを開くマクロでも、次のように解説されていることがあります。 Sub Sample1() Workbooks.Open "C:\Book1.xlsx" End Sub 「Filename:=」が付くのと付かないのでは、いったい何が違うのでしょう。 命令とオプションの関係 VBAのオブジェクト式には、次の2種類があります。 (1)対象.様子 = 値 (2)対象.命令 (1)は対象の様子や状態を設定する命令です。セルの背景色を設定したり、ワークシートに名前を付けるときなどに使います。この"様子"を専門用語でプロパティと呼びます。一方の(2)は、何らかのアクションを起こさせるようなときに使
Office TANAKA - 今さら聞けないVBA[=と:=の違い]
VBAのコードで、ときどき「:=」という記号を見かけます。たとえば次のようなコードです。 Sub Sample1() Workbooks.Open Filename:="Book1.xls" End Sub これは、Book1.xlsを開くマクロですが、これを次のように書くとエラーになります。 Sub Sample2() Workbooks.Open Filename = "Book1.xls" End Sub 上記のコードは、引数Filenameに"Book1.xlsx"という文字列を設定しているので、何となく正しいようにも思えます。だって、変数に値を代入するときは「=」を使うのですから。 Sub Sample3() Dim Target As String Target = "Book1.xlsx" End Sub 値を代入(設定)するとき「=」と「:=」の違いは何なのでしょう。 「:
![Office TANAKA - 今さら聞けないVBA[=と:=の違い]](https://cdn-ak-scissors.b.st-hatena.com/image/square/6c99338b15e87074c5fb056f3b825c84af52f8c1/height=288;version=1;width=512/http%3A%2F%2Fofficetanaka.net%2Fexcel%2Fvba%2Fbeginner%2F02-01.png)
Office TANAKA - 変数の使い方[Setを使うケース]
変数に値を入れるには「=」記号を使います。次のマクロは、文字列型の変数tmpに"tanaka"という文字列を格納します。 Sub Sample13() Dim tmp As String tmp = "tanaka" MsgBox tmp End Sub このように、変数に値を格納するときは「変数名 = 値」とします。これは、右辺を左辺に代入するという意味で「変数名 ← 値」のようなイメージです。 ところが、ときおり「Set 変数名 = 値」のように、Setという命令を使って変数に"何か"を代入しているコードがあります。 Sub Sample14() Dim ws As Worksheet Set ws = Sheets("Sheet1") MsgBox ws.Name End Sub Set ws = Sheets("Sheet1") は、変数wsにシート[Sheet1]を代入する命令で
Office TANAKA - 変数の使い方[変数の名前について]
VBAでは、変数の命名に関して次のようなルールがあります。(以下、Excel 2007のヘルプより抜粋) 変数名には、文字 (英数字、漢字、ひらがな、カタカナ) とアンダスコア (_) を使うことができます。スペースや記号は使えません。変数名の先頭の文字は、英字、漢字、ひらがな、カタカナのいずれかでなければなりません。同一適用範囲 (スコープ) 内で同じ変数名を複数使うことはできません。また、変数名の長さは、半角で 255 文字以内でなければなりません。 つまり 変数名には文字(日本語を含む)のほか、アンダーバー(_)だけを使えて、そのほかの記号やスペースは使えない 変数名の先頭が数字やアンダーバー(_)ではいけない 同じ適用範囲で同じ名前は使えない 変数名の長さは半角で255文字まで ということですね。 4.は、意識する必要はないでしょう。そんな長い名前を使うことは希でしょうし、もしエラ
1
リリース、障害情報などのサービスのお知らせ
最新の人気エントリーの配信
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く
