VBAの技


オートマクロでは生成できない必須記述法です。

実現したい機能

書式(R=行,C=列)

セルの値を求める

x = Cells(R,C) Rは行・Cは列

セルに値を入れる

Worksheets("シート名").Cells(R,C) = A

セルに数式を書き込む

Cells(1,2).Formula = "=today()"
式を”で囲みます。

文字列切り出し

Right(文字列,文字数)
Left(文字列,文字数)
Mid(文字列,開始位置,文字数)

文字列操作

 文字列から、特定の文字の位置を見つける   Instr(文字列,検索文字)

文字列中の文字を置換
Replace
(文字列、置換前、置換後)

文字種の変換
 StrConv(文字列, vbKatakana)
  vbHiraganaでひらがなに
 vbWide で全角に
 vbNarrow で半角に 変換します。

文字をコードで指定

Chr(数字) ですが、
ChrW(数字) と指定すると、
ユニコードの文字が指定できる。
例:Replace(文字列,ChrW(160),Chr(32))
で、ユニコードのスペースを通常のスペースに

文字列・数値の変換
*結構シビアです

数値→文字列  CStr(数値)
文字列→数値  Val(文字列)

日付・時間の計算

Dateadd (種別,加算値,日付)
種別は、"yyyy" "m" "d" など
引く場合は加算値をマイナスで

実現したい機能

書式(R=行,C=列)

アクティブなセル情報を求める

行番号: R = ActiveCell.Row
列番号: C = ActiveCell.Column

アクティブなファイルの情報

ファイルのパス:ActiveWorkbook.Path
ファイル名:ActiveWorkbook.Name
パス+ファイル名:ActiveWorkbook.FullName
シート名:ActiveSheet.Name
シート番号:ActiveSheet.Index
シートのコードネーム:ActiveSheet.CodeName

OSやExcelのバージョンを求める

Application.OperatingSystem OSのバージョン
Application.Version エクセルのバージョン
 msgboxで表示させ、書式を確認してください

今日の日付を求める

Date (today()の代わり)

日付・時間の計算

Dateadd (種別,加算値,日付)
種別は、"yyyy" "m" "d" など
引く場合は加算値をマイナスで

デスクトップなど、user領域を指定するには、%USERPROFILE%を使って指定します。
デスクトップは、%USERPROFILE%\Desktop  ピクチャーは、%USERPROFILE%\Pictures 
ドキュメントは、%USERPROFILE%\Documents です。

マクロでどのように記述したらよいか分からないときに、オートマクロで記述させ、その内容を確認し利用します。例えば、1行削除するマクロの場合、オートマクロでは
 Rows("1:1").Select
Selection.Delete Shift:=xlUp
と記述されます。これを見て、1行目全体を指定するのはRows("1:1")であり、削除後上に移動するのが、Shift:=xlUp だと分かります。Selectは不要なので、
Rows("1:1").Delete Shift:=xlUp のように、SelectとSelectionを削除してつなげます。こうすることで、マクロの実行時に選択の操作が実行されて画面が変わるのを止められます。スピードも速くなります。

オートマクロを修正する

オートマクロで作成したマクロは、コピー・貼り付けの際に、選択範囲を移動するので、画面表示がうるさくなります。マクロを実行している際に、この移動を表示させないようにすれば、スピードも速くすっきりします。以下の2行で、作成したマクロを挟んでください。(*subとend subの間を挟みます)

Application.ScreenUpdating = False
 ここに、オートマクロで作成したコマンドが入る。
Application.ScreenUpdating = True  で戻す。

Application.Run"ブック名!マクロ名( )"  または、 Call マクロ名 でも構いません。

オートマクロでは生成できないマクロは、自分で書きます。ここでは、セルの値を1増やすマクロを例に、説明します。このマクロは、index関数でデータを呼び出す際の、参照行番号を繰り上げるのに使っています。

VBAのツールバーを表示する。

 このようなツールボタンを表示させるために、表示・ツールバーの
Visual Basic にチェックを入れます。図の矢印のボタンが、Visual Basic のエディターを表示させるものです。これをクリックします。

Visual Basic エディターを開く。

エクセルのシートとは別のウインドウが開きます。このウインドウの、挿入メニューを開き、標準モジュールをクリックします。(オートマクロを記述していれば、標準モジュールシートは、既にあるので、これに書き足しても構いません。)

コードを書く。

ここでは、シートのセルの数字を1増やすマクロを書く例で説明しています。マクロ名は、nextd()とします。(次のデータという意味)

モジュールシートに、sub nextd()と書いてエンターを押すと、End Subが自動で記述され、subもSubと大文字になります。

Sub nextd() と End Sub の間に、コードを記述します。

数字が1ずつ増えるセルは、座標がI2(アイ・2)なので、VBAで使う座標は(2,9)になります。
1行目は、今入っている数値を読み取り、これをnbという変数に取り込みます。
nb=Cells(2,9)
If nb>10 then nb=0   データが10行しか無く、nbが10を超えたら、1に戻したいので
nb=nb+1           nbに1足します。

この3行でおしまいです。

マクロを実行する。

ツール・マクロ、実行で、動作を確認します。

マクロ実行ボタンを作る。

このメニューでマクロを実行するのは面倒なので、実行用のボタンを作ります。

そのためのツールバーを、表示・ツールバー・フォームで表示します。

右のようなツールバーの、赤枠で囲ったボタンが、コマンドボタン作成です。

これをクリックし、適当な位置にボタンをドラッグで作ると、先ず最初に、マクロの登録を聞いてきます。

ここで、nextdをクリックして選択します。最初に、この選択をしておきます。

ボタンのデザイン・位置・名前などは、後で設定します。

ボタンが、このようになっているときが、編集モードです。
名前をドラッグして、選択し上書きで「次へ」と書き直します。編集モードになっているときは、ドラッグで大きさ・位置を変更できます。

 このように、選択枠が外れると、ボタンは生きているので、クリックするとマクロが実行されます。編集モードに戻したい場合は、右クリックします。

モジュールシート以外の場所にマクロを記述する(プライベートサブ)。

実は、マクロは、標準モジュールだけでなく、各シートやブックにも記述できます。これらは、実行がそのシートなどに特定されるもの(プライベートサブ)です。プロジェクト一覧の下にある標準モジュールでなく、その上のThis Workbookやsheetをクリックして、シートを表示します。

例1:このbookを開いたときは、エンターキィのカーソル移動が横(右)にするが、閉じたときは元の下に戻しておく、というマクロです。

例2:特定のシートのセルが変化したときに実行されるマクロです。入力されると自動実行される。

Changeを選び、 If Target.Column=* and Target.Row=* then を追加して、特定のセルが変化(入力された)したときのみ実行されるようにできます。

 

実現したい機能

書式

警告窓 (音と情報ウインドウ)

Msgbox ”文字列”または数値

OKをクリックして先に進む

確認窓(Yes・Noを確認する)
内容は、カッコ内に

kakunin = MsgBox("質問文 ?", vbYesNo)

Yesボタンをクリックすると kakuninの値が6
vbYesNoCancel にするとキャンセルボタン付
など、各種オプションがある

入力ボックスの配置

x0 = InputBox("ここにメッセージ")

警告ダイアログを出さない

削除などのマクロを実行すると、自動で確認・警告のダイアログが出ますが、これを止める方法です。
Application.DisplayAlerts = False と
Application.DisplayAlerts = True で挟む。
ファイル操作
いずれもファイル名は、フルパスで指定します

簡単な命令で、様々なファイル操作ができます。

ファイルがあるかどうか調べる
        If Dir(filename)<>"" then  **    ファイルがあれば、**する

ファイルの削除    Kill filename

ファイルのコピー貼付け      Filecopy 元ファイル,コピー先ファイル

ファイルの移動・リネーム  Name 元ファイル As 新ファイル
 (このとき、パスを変えれば移動になります) 

フォルダの作成と削除は、 MkDir path  と、RmDir path で可能です。

ファイルの有無     Dir(パス+ファイル名) で、あればファイル名を、無ければヌルを返します。

フォルダの有無  Dir(パス,vbDirectory) で、あればフォルダ名を、フォルダが無ければヌルを返します。空のフォルダの存在も分かります。  

セルの指定には、Cells(行、列)という行や列の番号で指定するものと、Range("座標 ") の形式があります。
Rangeの座標は、A1やB1:B10などのシートでの表記法がそのまま使えます。

変数で範囲指定したい場合は、Range(Cells(g1,r1),Cells(g2,r2)).Select や Rows(r1&":"&r2).などの書き方ができます。

列全体・行全体の指定は、

Range(Columns(2), Columns(4)).Select  または、 Columns(2).Resize(, 3).Select ( 2列目からもう3列(4列まで)選択の意味 )
ColumnsをRowsに変えると、行全体の選択です。
 

範囲の名前で指定する場合は、 Application.Goto Reference:="範囲名" とした後で、Selection. を使います。

ワークシートに設定した範囲を使う

Application.Goto Reference:="範囲名"

選択された範囲の情報を得る

Selection.Rows.Count で、選択されている行数を、Selection.Columns.Count で、同じく列数が得られます。

セルの範囲指定コピー貼り付け   範囲指定してコピー貼り付けします。

 Worksheets("シート名1").Range("A1:AB1").Value = Worksheets("シート名2").Range("A5:AB5").Value

シート名2の範囲A5:AB5を、シート名1の範囲A1:AB1に貼り付けます。

 


 

1.書き出しと書き終わり

Function 名前(引数1,引数2,・・)    *名前・引数はアルファベットで、

End Function                 *これが、書き終わり

2.名前について

定義したい関数名は、シート関数で既に使われているものは使えません。定義済みの関数は英語名なので、ローマ字表記の日本語を使うと、殆どダブりません。この名前が、そのまま戻り値の変数名として使われます。従って、構文の中に、必ず関数名を定義する文が入っているはずです。

例:hirituという関数を定義するとき、

Function hiritu(分子,分母)
  hiritu=分子/分母
End function

3.引数について

定義したい関数を計算するために必要な変数を、引数といいます。引数が無い場合でも( )は必要です。引数が複数ある場合は、,(カンマ)で区切って並べます。上の例のように日本語(全角)は使えません。半角英数のみ使用可能です。

例:hirituという関数を定義するとき、

Function hiritu(bunsibunbo
  hiritu=bunsi / bunbo
End function

注:本来は、変数はdim分で、種類の定義が必要です。整数(±32000の範囲)や小数点以下も含まれる数とか、あらかじめ数の種類によって定義します。しかし、これをしなくとも、自動で定義されます。メモリーを有効に使うためには、細かく定義した方が良いのですが、さほど大きくないマクロでは、自動変数でも構いません。

4.定義された関数を使う

使い方は、シート関数と同じです。引数には、直接セルの座標を書き込みます。関数の書かれたセルに、計算された返り値が入ります。

例:      =hiritu(A4,B4)

5.シート関数(組込関数)とfunction関数(マクロ関数)の違い

シート関数も、マクロ関数も使い方の上では、全く同じです。マクロが組み込まれているシートでないと、マクロ関数は使えないのに対し、シート関数はすでにワークシートに組み込まれているので、定義無しで使えます。マクロ関数を全てのシートで使いたいときは、これをpersonal.xlsというブック名で保存しておきます。エクセルの起動時に、このファイルがあるとbookに組み込んでくれます。この機能を利用して、エクセルのマクロウイルスは、personal.xlsの名前で保存されています。このため、personal.xlsのファイルはウィルスの温床のようになってしまっています。

function文の中では、VBAの関数を使います。これが、組込関数と同じ名前であったりするので混乱しますが、あくまで、別物です。構文が異なるので注意して下さい。例えば、if文は

if 条件式 then ○ else × で書き、行を変えて書く場合は、最後に end if が必要になります。組込関数のif とは、全く別物です。

フォームツールバーのボタンで、複数のボタンを作成し、これらの名前をマクロで変更したい場合は、以下のように指定することで、変数を使えます。
ボタンを選択して、名前ツールバーの表示を確認します。これは、後から付けた名前とは異なるエクセル内部で使うオブジェクト名です。作成時に付けられますが、途中で削除したり移動すると、続き番号にならないので注意して下さい。
更に、マクロで使う場合の名前は、コード名になり、英語名になっています。

 

For i = 1 to 10
 Sheet1.Shapes("Button " & i).Select
 Selection.Characters.Text = Sheet1.Cells(1,i)
Next i
これで、セルのデータから順番に名前を付けられます。コード名が、Button 1 のようになっていて、番号は日本語表示の数字と同じです。

エクセルのシートは、シート名・コード名・インデックス番号の3つで管理されています。
シート名というのは、タブに見えているシートの名前です。コード名というのは、シートが作られたときに自動的に付けられたsheet1sheet2などの名前です。 更に、インデックス番号というのは、並んだシートの順番です。
コード名以外は、後から変更可能です。また、シートを指定するには、どれも使えますが、後から変更されると、シート関数は自動で対応しますが、マクロの記述は自動で変わりません。従って、基本的には、コード名で指定する方が良いのです。
ここでは、コード名Sheet1からSheet3の3つのシートがあります。それぞれ以下のようになっています。

インデックス番号は、ブックを開いたときに並ぶ順番です。ドラッグで変わります。

コード名 シート名 インデックス番号 セルの指定例
Sheet1 Sheet1 3 Sheet1.Cells(1,1)
Sheet2 挿入シート3 1 Worksheets("挿入シート3").Cells(1,1)
Sheet3 名前を変えた 2 Worksheets(2).Cells(1,1)
右は、それぞれのシートのセルを、コード名・シート名・インデックス番号で指定する例です。 挿入などで、名前を付けなかったシートは、Sheet2などの名前が付きますが、これがコード名と同じとは限らないので注意して下さい。
セルの結合は、見かけは便利ですが、VBAで処理する場合は、様々なトラブルが起こります。これを多用することは避けたほうが良いのです。
結合されているセルを選択して、Deletを押せば、結合されていることも無関係に消えてくれます。 この操作は、オートマクロでは
Range("A1").Select
Selection.ClearContents
ですが、これを Range("A1").ClearContents とまとめると、「結合されているセルの一部を変更できません」というエラーになります。A1が結合されていなければ問題ないのですが、結合されているセルがあると、シートをアクティブにして、選択してからでないとクリアできません。

以下は、別のシートから、Sheet2の1列から2列までの1行目をクリアする例です。ポイントは、セルが結合されているときといないときを、MergeCellsで判定し、結合されているときは.MergeArea.ClearContents 、そうでないときは普通に .ClearContents とすることです。

 With Sheet2
 For i = 1 To 2
  If .Cells(1, i).MergeCells Then
 . Cells(1, i).MergeArea.ClearContents
 Else
  .Cells(1, i).ClearContents
 End If
Next i
End With
ちなみに、値を代入するときは、結合されたセルの先頭に代入すれば大丈夫ですが、先頭でない場合は表示もされずエラーも出ません。
簡易には、Cells(1,1)="" と、ヌルを代入すると、結合があってもエラーになりません。
セル結合がある場合、座標を間違えると、マクロを実行しても、表示されないので、トラブル発見に手間取ることがあります。だから、できるだけ使わない方が良いのです。

ワークシート関数を呼び出す

Application.WorksheetFunction.関数名( )

VBAの関数とエクセルのワークシート関数は、記述法が異なります。基本的には、VBAでワークシート関数は使わない方が良い (使えないものや、引数の表記が異なる場合がある)のですが、上のように記述すれば使えます。
範囲の指定は、Range(Cells(g1,r1),Cells(g2,r2)) の表記が使えます。
SUMを使う場合の例: Application.WorksheetFunction.Sum(Range(Cells(1,1),Cells(1,10))
Phoneticを使う例:Application.WorksheetFunction.Phonetic(Cells(1,1))

警告を表示させないようにする

例えば、シートの削除のコマンド ActiveWindow.SelectedSheets.Delete を、

Application.DisplayAlerts = False
  この間に入れれば、警告は出さずに実行されます。
Application.DisplayAlerts = True
 

オブジェクトのボタンを隠すには、

 ActiveSheet.Shapes("Button 13").Visible = False
ボタンの番号は、選択して座標ウインドウを見ます。 
 

ThisWorkBook に、Private Sub Workbook_Open()を作ります。
Bookを開いたときに、Sheet1を開きたいときは

Private Sub Workbook_Open()
   Sheet1.Activate
End Sub
です。

または、
Auto_Open()という名前のサブルーチンを記述します。例えば、Sheet1を開かせたい場合は、
Sub
 Sheet1.Activate
End Sub
とします。

閉じるときに実行するマクロは、Auto_Close() という名前で作ります。
If ThisWorkbook.Saved = False Then
ThisWorkbook.Save
End If
 とすれば、終了前に強制的に上書き保存します。

 ThisWorkbook.Saved = True とすると、保存したと見せかけて、保存せずに終了します。
例)Sub Auto_Close
   ThisWorkbook.Saved = True
  End Sub

ファイル選択のダイアログを表示する

With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = パス & "*.jpg" '← ワイルドカード
If .Show() = 0 Then Exit Sub
MsgBox .SelectedItems(1)
Cells(1, 2) = .SelectedItems(1)   'ファイル名を、セルに出力します。
End With

 SelectedItemsが、配列になっているので、複数のファイルを選択できます。
この配列は、1から始まっていることに注意してください。
.SelectedItems.Count で、選択したファイルの数が分かります。

便利な検索ルーチン

検索関連のワークシート関数は、Application.WorksheetFunction で使えないものが多いので、VBAにはVBAの専用の検索関数を使います。以下のパターンを覚えておくと、シート内のデータを検索してくれます。

Dim YLine As Long
Dim XLine As Integer
Dim Obj As Object
smoji = InputBox("検索文字を入力してください")
Set Obj = Sheet1.Cells.Find(smoji)
If Obj Is Nothing Then
MsgBox smoji + "は見つかりませんでした。"
Else
YLine = Sheet1.Cells.Find(smoji).Row
XLine = Sheet1.Cells.Find(smoji).Column
MsgBox smoji + "は、" + CStr(YLine) + "行目の" _
+ CStr(XLine) + "列目にあります"
End If
この例は、InputBox と MsgBox を使っていますが、要するに smojiという文字列データを、sheet1(シート見出しの名前ではありません)の中で探して、見つかったらそのセルの行(YLine)と列(XLine)の番号を返してくれるものです。

列を指定して検索する場合は、

Set Obj = Sheet1.Columns("C").Find(What:=smoji)    *What:=は、複数のオプションを入れるときに使います。
YLine = Obj.Row  これで発見された行が得られます。

検索を続行して、次の検索値を得るには
 Set Obj = Sheet2.Columns("C").FindNext(Obj)

 これを、Do Loopで繰り返すと、最下行まで行った後は最初に戻ります。

ちなみに、objectがNothing 出ない場合、という書き方は
  If Not ○○ Is Nothing Then となります。Isを<>にするのではないので、注意が必要です。  

matchなどの検索系の関数を使うと、結果が#N/Aなどになる場合があります。このようなセルの参照を含むマクロを組むときに、エラー値は特別な値になるので、if分などで除外しておかないとマクロが止まってしまいます。このような場合は、IsErrorの値を条件にします。
例えば、Cells(I,J) の値を調べる場合は、以下のような条件文を書きます。

 If IsError(Cells(i, j).Value) Then
  'エラーの時
 Else
  'エラーでない時
 End If

エラーの種類で分岐したい場合は、

 If IsError(ActiveCell.Value) Then
errval = ActiveCell.Value  として、errval の値を取得します。この値は、CVErr(**) の引数を以下のように指定した値になります。
これが、#DIV/0! エラー のときは CVErr(xlErrDiv0)  #N/A エラー のときは CVErr(xlErrDiv0)
#NAME? エラーは、CVErr(xlErrName)  #NULL! エラーは、CVErr(xlErrNull)  #NUM! エラーは、CVErr(xlErrNum)
#REF! エラーは、CVErr(xlErrRef)  #VALUE! エラーは、CVErr(xlErrValue)  となります。

  マクロを実行するのには、ボタンをを配置して、これにマクロを登録するか、ショートカットキィを登録してキィボードから指示するか、またはツールメニューのマクロから指定します。マクロの存在を意識せずに、セルの値が入力されたら自動的に実行させたい、という場合には、モジュールシート以外の場所に、特定のマクロを書き込みます。 マクロは、Moduleシート以外にもかけます。

普通に、モジュールシートに、keisanというマクロを作りました。これは、A1(cells(1,1))の値を読み取って、これを2倍して1をたしたものをB1(cells(1,2))に表示するというものです。これを、A1に入力があったら即座に実行させようと思います。

そこで、Module1でなく、sheet1をWクリックして、ここにコードを書き込みます。ちょっと長いですが、名前は
Private Sub Worksheet_Change(ByVal Target As Range) とします。これと、End Sub の間に、
 If Target.Column = 1 And Target.Row = 1 Then        ' 変化を検知するセルの列数(Column)と行数(Row)を指定します
    keisan
    Range("C1").Select
End If
このIf文の組は、複数書き込めるので、セルの値によって実行するマクロを選ぶこともできます。

最後の、Range().Select は、単に実行が終わったことを示すために、カーソルを移動させるものです。

このようなPrivate Subは、Worksheet_Changeの他にもあります。
Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)  セルのWクリックを検知します 
Worksheet_SelectionChange(ByVal Target As Range)  セルの選択を検知します(入力が無くていい)

住所録作成に便利!
郵便番号辞書を使って、郵便番号を変換して住所を入力すると、これを郵便番号と住所の2列に入力するものです。 住所に変換してしまうと、郵便番号は読みとして残るので、これを元の列に入れ、変換後の住所を別の列に移動します。

例では、sheet1のA列が郵便番号、B列が住所11データで、ここに郵便番号変換したものが入ります。変換・確定すると、A列に住所が入りますが、これをB列に移動させ、A列には入れた郵便番号を半角文字で入力されます。これを、A列の値が変化したときにマクロを自動実行させています。

前項のように、これを使うシートのモジュールに作成します。

郵便番号を入れる列番号をXbで、住所1の列をxjの値で決めています。これは、自分の住所録に合わせて変更してください。また、辞書変換で出る住所に県名があって長くなるので、Replaceで消しています。ここも、不要なら変更・削除してください。

コードを打つのが面倒なら、以下からサンプルのエクセルファイルをダウンロードしてください。ダウンロードはここ。(ファイルは、xls形式ですが2007以降でも動きます。)
IMEの郵便番号辞書を有効にして、マクロを有効にして開いてください。または、以下のコードをコピーして、ワークシートのモジュールシートに貼り付けます。

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim xb, xj, yb As Single
 Dim buf, ybn As String
 xb = 1         '郵便番号列
 xj = 2         '住所1の列
 yb = Target.Row
If Target.Column = xb And Cells(Target.Row, xj) = "" Then
 buf = Cells(yb, xb)
 ybn = Application.WorksheetFunction.Phonetic(Cells(yb, xb))
 If Mid(ybn, 4, 1) = "−" Then
   ybn = StrConv(ybn, vbNarrow)
   ju1 = Cells(yb, xb)
   ju1 = Replace(ju1, "千葉県", "")     '省略県名
   ju1 = Replace(ju1, "東京都", "")
   Cells(yb, xj) = ju1
   Cells(yb, xb) = ybn
  End If
End If
End Sub
 

特定のセルを右クリックしたときに実行するマクロを作ります。この例は、B列のセルを右クリックしたときにメッセージが出て、そうでないときは普通の右クリックメニューが出ます。

この場合、Sheet1だけに適用するので、プロジェクトウインドウのSheet1をWクリックしてワークシートのModuleを開きます。

 *このワークシートモジュールを使う上での注意は、ここのマクロは、このシートがアクティブになっていることが前提であることです。
Cells(*、*)は、当然ですが
マクロの中で、アクティブシートを切り替えても、ここに書かれたCell(*、*)は、このワークシートを意味します。


右の種類を開いて、BeforeRightClickを選択します。
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range,Cansel As Boolean) というサブが作られるので、その中に書き込んで行きます。


小さいので、図を大きくしました。

ここでポイントは、Cancel = True です。これは、対象のセルだけ右クリックの通常メニューを表示させない、というものです。

選択されているセルの座標は
Target.Row で列が、Target.Column で行が得られます。

範囲で、複数選択した場合は、上記でその左上のスタートのセルの情報が得られますが Selection.Rows Selection.Culmns で、列の数と行の数が得られます。(複数形に注意)範囲の右下の座標は、Target.Row + Selection.Rows - 1 のようになります。

一つのセルしか選択していなければ、Rows Culumns の値は1になります。
複数のセルを選択した場合
Dim rng As Range For Each rng In Selection.Rows Msgbox rng.Rows Msgbox rng.Row Next rng
rngをRange変数に指定し、For next で、セルの値(rng.Rows)・セルの行位置(rng.Row)を取得します。 sがあるかないかで、値・行数が得られます。*ループ内で、Bookを移動すると、rng変数が効かなくなり、深刻なエラーが出ます。
rngの値を、別の配列変数に入れることで解決しました。
複数選択が列方向なら、RowをColumnカラムに変えて使えます。
次の行を選択する
ActiveCell.Offset(1, 0).Select ですが、フィルターが掛かっているシートで、次の行を選択するには
Do
 ActiveCell.Offset(1, 0).Select
 Loop While Rows(ActiveCell.Row).Hidden
とすることで、非表示でない行をとばして、選択できます。

シートのデータの最終行を得る
 LastRow = Cells(Rows.Count, 1).End(xlUp).Row

特定のセルの値を調べて、条件に合わないときに印刷ができないようにします。この場合は、ThisWorkbookを開いて
Private Sub Workbook_BeforePrint(Cancel As Boolean) と言う名前のマクロを書きます。

上の例は、セル(1,15)の値がOKでないときは、MsgBoxで警告を表示し、Cancel=True で、印刷をキャンセルします。

ヘッダー・フッターを書き込む

  ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
  ActiveSheet.PageSetup.RightFooter = Format(Date, "ggge年m月d日")

これは、右のフッターにセルA1の値を入れ、年号書式を設定するものです。
同様に、 LeftHeader : 左側のヘッダー CenterHeader : 中央のヘッダー RightHeader : 右側のヘッダー LeftFooter : 左側のフッター CenterFooter : 中央のフッター RightFooter : 右側のフッター になります。

ファイルがあるかどうか調べる

Dir(ファイルのフルパス名) この値が、ヌルの時は、ファイルがなく、ファイル名の時は、存在します。

フォルダがあるかどうか調べる
chk = Dir("c:\test\") このときchkには、Cドライブのtestというフォルダの中のファイル名が入ります。フォルダそのものがなければ、ヌルが入りますが、フォルダはあって、中身がない場合も同じなので、注意が必要です。


Kill ファイル名(フルパス)  ファイルを削除します。
いずれも、パスを省略すると、bookを開いた場所(アクティブパス)になります。ファイル名は文字列です。


エクセルを終了して、ワードを開く

ThisWorkbook.Save エクセルを、上書き保存します。
Call Shell("WINWORD C:\**.doc", vbNormalFocus)  または Shell "cmd /c C:\**doc", vbHide
Application.Quit  開いた後で終了します。


拡張子.txtのファイルは、メモ帳でも編集でき、文字データを保存しておくには、最も手軽なファイルです。エクセルで、このtxtファイルに書き出したり、ここから読み込んだりできると、より拡張した使い方ができますね。

読み込み:
Dim datal As String     ’読み込んだ行データを入れる変数です。
txtname = "ファイル名"     ’ファイル名は、パスも付けて指定します。( C:\****\****\***.txt )
Open txtname For Input As #1   ’ファイルをOPENする命令です。
For i = 1 To 100
Line Input #1, datal   Input #1でも良いのですが、行内に,(カンマ)があると2行分になるのを防ぎたいときは、Line Inputを使います。
 Cells(i,1) = datal
If datal = "// -->" Then Cells(i, 1) = "": Exit For   ’For Next を使ったので、txtファイルの終わりの文字を指定しました。
Next i
Close #1      Openに対して、必ずClose で 開いたファイルを閉じます。

書き出し:
txtname = "ファイル名"  ’読み込みの場合と同じです。
Open txtname For Output As #1   ’今度は、 Output として開きます。
For i = 1 To 100
Print #1, Cells(i,1)     ’Print #1 で1行分のデータを書き出します。
Next i
Close #1          ’開いたら閉めるのが決まりです。

書出し2:追記していく場合は

Open txtname For Append As #1 で開きます。

QRコード画像作成の例

エクセルからexeファイルを実行させたい場合、バッチファイルに記述して、バットファイルを実行させる方法と、エクセルから直接コマンドを実行する場合と、2通りの方法があります。

例えば、mkqrimg.exe というQRコード画像を作成するファイルを、エクセルから利用する場合で説明します。

1.バッチファイル:qr.batを作って、ここにexeファイルの起動コマンドを書いておきます。
 C:\QRcode\mkqrimg.exe /O"C:\QRcode\Test.bmp" /T"エンコード%0D%0Aテスト" /S3
 この1行が、バッチファイルの中身です。このように、/以降のオプションが並び、その文字列にスペースや不可視文字が入る場合、記述が面倒になるので、batファイルで確認しておいた方が楽です。
Sub QRbat()
 Dim ShellObject As Object
 Dim MsgBoxRet As String
 Set ShellObject = CreateObject("WScript.Shell")
 ShellObject.Run "C:\QRcode\qr.bat", 0, True
 ActiveSheet.Pictures.Delete    '予め画像を消します。
 ActiveSheet.Pictures.Insert("C:\QRcode\Test.bmp")
'これは、できたQRコード画像をシートに貼り付けるためのものです。
End Sub

2.直接コマンドを実行する場合

Sub qrcode() '  mkqrimg.exeをC:に
Set WSH = CreateObject("Wscript.Shell")  '/T以降のコードは、シートから取得しています。
Cmd = "C:\mkqrimg.exe" + " /O" + Chr(34) + "C:\Test2.bmp" + Chr(34) + " /T" + Chr(34) + Cells(1, 8) + "%0D%0A" + Cells(2, 8) + "%0D%0A" + Cells(3, 8) + Chr(34) + " /S3"
Set wExec = WSH.Exec("%ComSpec% /c" & Cmd)
Do While wExec.Status = 0
DoEvents
Loop
Result = wExec.StdOut.ReadAll
Set wExec = Nothing
Set WSH = Nothing
ActiveSheet.Pictures.Delete  '予め画像を消します。
ActiveSheet.Pictures.Insert("C:\Test2.bmp")
Cells(1, 1).Select
End Sub

この場合、ダブルクォーテーションそのものをコマンドに出力するために、Cmdの文字列を作るのに工夫が必要です。
exeファイルの場所などに、デスクトップやマイドキュメントなどスペースの入るアドレスを指定するのが難しいので、Cドライブにおいています。

この、mkqrimg.exe は、Psytecさんのページで公開されています。

エクセルから、メールの送受信・Webサーバーからダウンロード・アップロード するには

 普通ではできませんが、フリーで提供されているBASP21(ビーエーエスピー21)というコンポーネントをインストールすることで、VBAに機能を追加でき、その機能を使ってこれらが可能になります。実際の使い方は、(officeTANAKAさんのページ)ここにあるのでリンクをおいておきます。 若干制限はありますが、大変便利なもので、Win坊も長く使わせてもらっていました。
しかし、これはVista以降で使うことはできませんでした。そこで、新たに探したところ、
 http://www.happy2-island.com/access/gogo03/capter90302.shtml に、API関数を使う方方が紹介されていました。
InternetOpenとInternetConnectでFTPサーバへ接続した後、FtpPutFileを使うものです。
この関数を使う準備(定義)が面倒ですが、関数を定義した後は、下のようなマクロを作成して、簡単につかえます。この他に必要な関数も含めて、Mojule2.basにまとめたので、ダウンロードして解凍した後、VBAエディターのメニューからインポートして下さい。

Sub ftp1()
Dim lngRC As Long
Dim txtname As String 'FTPしたいファイルの名前(フルパス)
Dim sadd As String 'サーバーのアドレス
Dim snam As String 'サーバーのユーザー名
Dim spasw As String 'サーバーのパスワード
Dim sdir As String '転送先のサーバーパス
Dim sfnam As String '転送先でのファイル名

txtname = ActiveWorkbook.Path + "\**test.txt" '以下の中身を設定します
sadd = "***.ne.jp"
snam = "user*"
spasw = "password*"
sdir = "/***/***/****/"
sfnam = "**test.txt"

lngRC = fcInternetOpen
If lngRC = 0 Then

lngRC = fcFTPConnect(sadd, snam, spasw)

If lngRC = 0 Then
Call fcFTPPutFile(txtname, sdir + sfnam, FTP_TRANSFER_TYPE_ASCII)
End If

End If

Call fcFTPDisConnect
Call fcInternetClose

End Sub
 

 

 

エクセル2003で、OCR(画像から文字データを読み込む)ができます。実は、2007以降では、この機能は省略されてしまいました。 互換性のため、2007でもMicrosoftのアドインをインストールすればできるようですが、2010ではそれもありません。

これを使うには、Office2003を完全インストールしないといけません。CDを入れてセットアップを実行し、この機能が入っているか確認し、無ければ追加インストールします。

Office共通ツールの中の、MicrosoftOfficeDocumentImagingを開き、「スキャン、OCRおよびIndexServiceフィルタ」の項目を調べます。黄色の1になっている場合は、コンピューターからすべて実行を選び、更新をクリックして追加インストールします。

更に、VBエディタを開き、ツール・参照設定でMicrosoft Office Document Imaging 11.0 Type Library にチェックを入れて、参照を設定します。

ここまで準備ができたら、以下のようなsubを作成します。

サンプルファイルがあるので、ダウンロードして参考にしてください。*下は画像なので、コピーできません。

これを起動すると、画像ファイルを聞いてくるので、jpgなどを指定します。画像中の文字が読めない場合にエラーが起きるので、エラー処理を追加しています。

このような新聞記事(朝日新聞 2013/1/27版)をスキャンしてみました。

これは縦書きで、しかも写真やタイトル画像も含まれています。

実際は、400dpiでスキャンしたjpg画像で500KBくらいの容量がありました。余分なものをトリミングしていますが、回転修正や背景の色修正なども加えていません。

カラーでスキャンしたので、新聞の紙の色も出ています。下が、マクロで読み込んだ文字です。A1に書き込みました。B1は、選択したファイル名です。


かなり高い識字率です。こういう機能が、excelのマクロで使えるなんて、すばらしいですね。何故、2007以降で廃止されたのか、残念です。というより、Office2003は、もう改良する必要の無い、完璧なものです。もう入手できないので、持っている人は大事に使ってください。ちなみに、使ったスキャナーは、このようなハンディスキャナーです。


 

 

 

エクセルから、faxを送るマクロです。Microsoftfaxがインストールされていて、faxモデムがあり、電話回線に繋がっていることが条件です。
faxプリンターで印刷する、というマクロを使うには、プリンターをFaxにして、これで印刷というマクロにすれば良いのですが、宛先などを手動で入力することになります。それではマクロを使う便利さが減るので、ここでは直接呼び出す方法を紹介します。

1.faxcom Type Library を使う場合:
参照設定で、faxcom Type Libraryを有効にしておきます。OSのバージョンによって異なりますが、これが無い場合はプリンター一覧にfaxが無い場合です。microsoftFaxをインストールし、モデムを設定してください。
簡単な、サンプルコードは、下記の通りです。

Sub fax1()
Dim faxServer As New FAXCOMLib.faxServer
Dim FaxDoc As New FAXCOMLib.FaxDoc
Dim FaxNumber As String
faxServer.Connect ""
Set FaxDoc = faxServer.CreateDocument("ファイル指定")
FaxDoc.RecipientName = "受信者名"
FaxDoc.SenderTitle = "件名です"
FaxDoc.DisplayName = "Fax送信テスト"
FaxNumber = "03*********"   '-は取り除いておく
FaxDoc.FaxNumber = FaxNumber
FaxDoc.Send
faxServer.Disconnect
End Sub

ファイルの指定は、ワード・エクセル・テキストなど使えますが、一番良いのはpdfです。フルパスで指定してください。

2.Microsoft Fax Service Extended Com Type Library を使う場合
同じく、参照設定で、これを有効にしておきます。両方有効にする必要はありません。簡単な、サンプルコードは以下の通りです。

Dim FaxDoc
Set FaxDoc = CreateObject("FaxComEx.FaxDocument")
FaxDoc.Recipients.Add "*********" '電話番号にハイホンは入れない
FaxDoc.Body = "C:\tmp\TEST.pdf"  ' などフルパスでファイルを指定
FaxDoc.submit ""  ' 件名です
 

どちらを使っても、microsoftFaxが起動します。動きは、同じです。アドレスの入力画面は出ませんが、送信後に確認のダイアログが残ります。faxサーバーに履歴も残ります。 マクロのあるブックのシートをfaxしたい場合、ファイル名が自身のエクセルになるので、うまく行かないようです。その場合は、アクティブシートを開いて、faxプリンターを指定して印刷する、というマクロを作ります。ページ数や用紙の指定など面倒なので、いったんpdfを作って、別ファイルをfax送信した方が良いです。