« Excelの荒技(その4) 行の高さを調整するVBA | トップページ | トレインチャンネルの秘密 »

2009年4月26日 (日)

Excelの荒技(その5) 行の高さを調整するVBA(その2)

前回の「行の高さを調整するVBA」の続きである。

前回、行の高さを調整する方法として以下の3つを挙げ、1番目について VBAのコードを掲載した。

方法1: 現在の高さの1.1倍とする
方法2: セルの最後に強制的に改行を挿入した後、高さを自動調整する
方法3: 列幅を0.8倍に変更 => 高さを自動調整 => 列幅を元に戻す

今回は残り全部、即ち、方法2と3のコードと、それらをアドイン化してメニューバーから起動する方法について説明する。
最後に、アドインファイルもアップする。
従って、コードの内容に興味がなく、「行の高さを調整するVBA」を試してみたいだけの人は、下の方の「2.作成したアドインファイルの存在を Excel に認識させる。」から見てもらってもよい。

方法2と3のコードを以下に示す。
なお、コメント「選択範囲のチェック~XXXXX をコピー」のところには、前回載せたコードの該当行をそのままコピーしてくれば良い。
また、表示幅の関係で、行の途中で改行されている箇所もあるので注意。

方法2

Private Sub 改行挿入()
    Dim a As Range
    Dim lastCell_Row As Long
    Dim sdHeight As Double
   
    '選択範囲のチェック~最終有効行 をコピー
    For Each a In Selection
        '最終有効行に達したら終わり
        If a.Row > lastCell_Row Then
            Exit Sub
        End If
        If VarType(a.Value) = vbString And Len(Trim(a.Value)) > 0 And a.RowHeight > sdHeight Then
            '最後に改行を挿入
            a.Value = a.Value & Chr(10)
            a.Rows.AutoFit
        End If
    Next a
   
End Sub

方法3

Private Sub 幅縮小拡大()
    Dim a1, a2 As Range
    Dim lastCell_Col As Long
    Dim rate As Double
   
    '幅の縮小率
    rate = 0.8
   
    '選択範囲のチェック~画面の更新を止める をコピー

    '最終有効桁
    lastCell_Col = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

    For Each a1 In Selection.Areas
        For Each a2 In a1.Columns
            '最終有効桁に達したら終わり
            If a2.Column > lastCell_Col Then
                Exit For
            End If
            a2.ColumnWidth = a2.ColumnWidth * rate
        Next a2
    
        a1.Rows.AutoFit
       
        For Each a2 In a1.Columns
            '最終有効桁に達したら終わり
            If a2.Column > lastCell_Col Then
                Exit For
            End If
            a2.ColumnWidth = a2.ColumnWidth / rate
        Next a2
       
    Next a1
   
End Sub

なお、前回、方法1のコードを載せたが、倍率(例えば 1.1倍)のパラメータ付きで呼ばれる形式にしていたので、以下に「呼び側」のコードも載せておく。

Public Sub 行高さ定率変更X11()
    行高さ定率変更 (1.1)
End Sub

それぞれのプログラムの内容については、見ればだいたい判ると思うので特に説明はしない。Range オブジェクトに関する Columns や Rows の使い方が私自身も怪しいが、とりあえず動作するようなので良しとしよう。

さて、これらのプログラムをアドイン化して Excel のファイルメニューから起動できるようにする方法に移る。

手順としては、以下の流れになる。
1.アドインファイル(.xla)を作成する。
2.作成したアドインファイルの存在を Excel に認識させる。
3.アドインを有効にする。

早速、順に示す。
なお、手順1でアドインファイルを作成する方法を示すが、この記事の最後に置いてあるアドインファイル「行高さ調整.xla」を使うのであれば、手順2から始めれば良い。

1.アドインファイル(.xla)を作成する。

Thisworkbook_4 Excelを開き、[ツール]->[マクロ]->[Visual Basic Editor]で、Microsoft Visual Basic ウィンドウを表示する。ここで、左側の「プロジェクト」のサブウィンドウの「ThisWorkbook」をダブルクリックして ThisWorkbook ウィンドウを表示させ、そこに以下のコードを貼り付ける。

  

  

Private Sub Workbook_AddinInstall()
    '新たにMenuBarを追加する
    Set Menu = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
    'MenuBarの名前
    Menu.Caption = "行高さ調整(&G)"

    'MenuBarにサブMenuを追加
    Set SubMenu1 = Menu.Controls.Add
    'サブMenuの名前
    SubMenu1.Caption = "× 1.1(&U)"
    'サブMenuにマクロを登録
    SubMenu1.OnAction = "行高さ定率変更X11"

    'MenuBarにサブMenuを追加
    Set SubMenu3 = Menu.Controls.Add
    'サブMenuの名前
    SubMenu3.Caption = "改行挿入(&I)"
    'サブMenuにマクロを登録
    SubMenu3.OnAction = "改行挿入"

    'MenuBarにサブMenuを追加
    Set SubMenu5 = Menu.Controls.Add
    'サブMenuの名前
    SubMenu5.Caption = "幅縮小拡大(&W)"
    'サブMenuにマクロを登録
    SubMenu5.OnAction = "幅縮小拡大"

End Sub

Private Sub Workbook_AddinUninstall()
    'MenuBarを削除
    Application.CommandBars("Worksheet Menu Bar").Controls("行高さ調整(&G)").Delete
End Sub

上のコードの内容を少し説明すると、手順3でアドインを有効にした際に、Workbook_AddinInstall の内容が実行されて ファイルメニューに 「行高さ調整」とそのサブメニュー項目が追加される。上の例では、1つ目のサブメニュー項目名を「× 1.1」、それを選んだときに実行されるプログラム名を「行高さ定率変更X11」と指定している。
逆に、アドインを無効にした場合は、Workbook_AddinUninstall の内容が実行されて ファイルメニューから「行高さ調整」が削除される。

次に、実行されるプログラムを記述する。
Microsoft Visual Basic ウィンドウで、[挿入]->[標準モジュール]を選択して Module1 と書かれたウィンドウを表示し、ここに、前回および今回記載したコード群を全て貼り付ける。

Visualbasic

最後に、[ファイル]->[Book1の上書き保存]を選択し、既定のアドイン格納場所へ 「行高さ調整.xla」 としてファイルを格納する。

アドインの既定の格納場所は以下である。なお、「ファイル名を付けて保存」ダイアログで「ファイルの種類」を「*.xla」とすると、自動的にこのフォルダが表示される。

  C:\Documents and Settings\ユーザ名\Application Data\Microsoft\AddIns

2.作成したアドインファイルの存在を Excel に認識させる。

通常のExcelのウィンドウに戻り [ツール]->[アドイン]でアドイン画面を表示し、[参照]ボタンからアドインファイル(行高さ調整.xla)を指定すれば良い。
なお、1で書いた既定の格納場所に格納しておけば、Excelを再起動すれば自動的に認識されるので、この操作は不要である。

3.アドインを有効にする。

Excel_addin ファイルメニューの [ツール]->[アドイン]のアドイン画面で [行高さ調整]にチェックを入れる。
これで、ファイルメニューに「行高さ調整」という項目が追加され、使用可能となる。

 

 

以上である。なお、ファイルメニューから「行高さ調整」の項目を消したい場合は、手順3の逆で、チェックをはずせばよい。

最後に、アドインファイルをアップする。

「行高さ調整.xla (zipで圧縮)」をダウンロード

サブメニューは、以下の5つとなっている。
[× 1.1]  [× 0.9]  [改行挿入]  [挿入取消] [幅縮小拡大]

ここで、
 [× 1.1] は 方法1
 [× 0.9] は 方法1の取消し(高さを 1/1.1 倍する)
 [改行挿入] は 方法2
 [挿入取消] は 方法2の取消し(行末の改行を削除する)
 [幅縮小拡大] は 方法3
である。(取消し系のコードは、アドインファイルの中を参照)

なお、最後にお決まりの言い訳。

  • Windows XP + Excel 2000 or 2002 の環境を前提にしており、他は確認していない。
  • デバッグはあまりしていない。
  • 今回の内容は、1週間程度でWEBからかき集めた情報を 自分勝手に解釈して記載しており、書いた本人もよくわかっているわけではない。(キッパリ sign01 bleah

あしからず。

|

« Excelの荒技(その4) 行の高さを調整するVBA | トップページ | トレインチャンネルの秘密 »

コメント

素敵です。
行高さ調整アドイン利用させて頂きました。
長いこと行ってたメンドクサイ作業が一気に
楽になりました。
ありがとうございました。
さんくす♪(o ̄∇ ̄)/

投稿: ucokinawa | 2010年9月 1日 (水) 13時42分

ucokinawa さん

このアドインは、私自身も作成以来、会社のPCに導入し愛用しています。(もっぱら、[改行挿入] のみを使用しています。)

ココログに仕組みがないため、このアドインが何回ダウンロードされ利用されているのかは分かりませんが、ucokinawa さんのようにコメントを頂くとうれしいですね。

ありがとうございました。

投稿: Toshi | 2010年9月 5日 (日) 22時21分

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/533640/44790689

この記事へのトラックバック一覧です: Excelの荒技(その5) 行の高さを調整するVBA(その2):

» Macrosoft Excel - Book1 macrosoft [重要政治・経済・国際ニュースダイジェスト]
Mac Bookの購入を検討中です。 - Yahoo!知恵袋Microsoftは次期バージョンから再びVBAに対応すると早くも発表していますが、 それまではMacでOfficeを利用するなら、Mac用「Office2004」を使うか BootCampによるWindowsでwin用の「Office」を利用するかどちらかがいいと思います。 もっとも、Excel ...[記事全文] Excelの荒技(その5) 行の高さを調整するVBA(その2): 気になる ...Microsoft Vi... [続きを読む]

受信: 2009年6月24日 (水) 12時42分

« Excelの荒技(その4) 行の高さを調整するVBA | トップページ | トレインチャンネルの秘密 »