作業を効率化させるExcel VBA Tips集

このツールは大したVBAコードが書かれているわけではありません。
単に置換処理をしているだけです。

ことの発端は、あるブログでHTMLソースコードを掲載したくて作りました。HTMLソースコードを掲載する場合、<記号や>記号の変換はもちんのこと、<pre>タグを使うのでTABも置換してやる必要があります。HTML自体を秀丸等で開発する場合は、TAB→スペースに自動変換してくれますが、僕の使っている開発環境に変換機能が付いておらず、VBAで自作しました。

このFC2ブログをはじめ、ある程度ブログ投稿画面で変換は行えますが、TABをスペースに変換してくれる機能はありません。まして、対象がMT4の為、デフォルトで<記号や>記号の変換機能は付いておりません。

【概要】
「ソース」のテキストボックス欄に、HTMLコードをコピー＆ペーストし、変換ボタンを押下する。「変換結果」テキストボックスに変換後のコードが出力される。



【こんな時に便利】
・手軽にまとめて置換したい

【ダウンロード】
・掲載用HTMLコード変換.xls

【注意事項】
<pre>タグでブログ掲載を考慮して、TABはスペース２つ分に変換してます。

ソースは至って、シンプルなので、自由にカスタマイズしてください。

【“HTMLをブログ掲載用のコードに変換するツール”の続きを読む】

Excelで設計書やテスト仕様書などを作成していて、シート数が多くなってくると、
前のシートで何て書いたっけ？？と、Ctrl + PageUpまたはPageDownを連打して他のシートを参照し、そのわずらわしさにイラっとしたことはありませんか？
そして、ちょっとでもそのイライラを改善する為に、リネームコピーして２つExcelファイルを画面に表示させ、見ながら編集ってしたことありませんか？




そして、次第に集中力が切れてくると・・・どっちが編集中のファイルだったっけ？？
と混乱の原因にもなります。

上記改善策の流れを整理すると以下のようになります。

１．作業中のブックを上書き保存
　↓
２．マイコンピュータより、１のファイルをコピーする
　↓
３．２のファイルを参照用として開く

で、”どっちが編集中だったか？”とファイルを見分けるよりも、誤動作を防ぐため参照用シートは読み取り専用であれば、上書き保存をしようとした時に、気づくことができます。

これらを自動化させるには、以前紹介したVBAを組み合わせれることで可能です。

・作業を効率化させるExcel VBA Tips集 ファイルを読み取り専用に設定する
・作業を効率化させるExcel VBA Tips集 VBAから作業中のブックをバックアップする

以下に組み合わせた場合のVBAを掲載します。
【“資料作成中、頻繁にシートをまたぐ回数が多くなってきた場合に良いかも・・・”の続きを読む】

このVBA単品では作業の効率化は行えないかも知れませんが、
他のVBAと組み合わせることで、効率化が図れると思います。
その組み合わせ例については、次回に。

今回紹介するVBAは保存されているファイルを読み取り専用に設定する方法です。

VBAで行う場合、FileSystemObjectを使います。

【“ファイルを読み取り専用に設定する”の続きを読む】

Excelで設計書などを作成している場合、
定期的にバックアップしたくなります。

おそらく、以下のような手順で行われると思います。

１．作業中のブックを上書き保存
　↓
２．マイコンピュータより、１のファイルをコピーする
　↓
３．ファイル名を適当な名前にリネームする

下記に紹介するVBAをツールバーに登録したり、ショートカットキーに割り当てていれば、
ボタン一つで、バックアップが作成できるので、上記１〜３の手順を１つの動作で行えます。

【“VBAから作業中のブックをバックアップする”の続きを読む】

以前、紹介した下記の記事。

・作業を効率化させるExcel VBA Tips集 選択した図形をJPEG画像として保存する

そもそも、何がしたかったか？というと、
ブログに掲載したい画像をExcel上で作成し、それを画像ファイルとして保存、
保存した画像ファイルをブログへアップロードすることを効率化させたいと思っていました。
その流れが下記の手順となります。

[Excelオートシェイプをブログへアップロードする手順]
１．Excelで図形を作成する
　↓
２．Excelで図形をコピー
　↓
３．グラフィックソフトを開く
　↓
４．コピーした図形を貼り付ける
　↓
５．JPEG指定で保存する
　↓
６．ブログの管理画面を開く
　↓
７．JPGファイルをブログへアップロード


そこで効率化させるポイントとして、
Excel上で作成したオートシェイプの図形をJPG画像ファイルとして自動保存させるという点でした。（上記赤字部分参照）

今回はアップロード自体も自動化させようという企みです。（上記青字部分参照）

で、前回紹介したコマンドプロンプトの起動が役に立ちます。

で、なんでコマンドプロンプトが必要なのか？というところなんですけど・・・

理想はVBA上でアップロードもすべてやりきれたら良いのですが、僕がやってる効率化のプログラミングで、インターネットにかかわる部分はJava言語で行ってます。
で、Javaで作ったアプリを実行させるにはコマンドプロンプトにてコマンド実行させてやる必要があるということです。

以下が僕が掲載しているもう一つのブログです。
・もしかしたら使えるかも？Java Tips集 XML-RPCを使ってJavaアプリからブログへファイルをアップロードする

上記リンクのページに記載しているJavaアプリをコマンドで実行してやると指定ファイルをブログへアップロードしてくれるというソースについて記載しています。
このJavaアプリは以下からダウンロードできます。が、JavaなのでもちろんJREがインストールされていることが前提です。

【ダウンロード】
任意の場所にダウンロードして解凍してください。classファイルとJarファイルが展開されます。
解凍先パスは後述のサンプルで設定するので覚えておいてください。
・ファイルアップロードJavaアプリ.lzh

【Javaアプリ制限事項】
・現状、上記のアプリでは日本語ファイル名のアップロードが行えません。原因究明中です。

下記掲載のVBAからコマンドによりJavaアプリへパラメータを渡してコールすることになるのですが、コメント文中にある「ユーザ設定箇所」に初期値を設定してやる必要があります。
設定する項目は以下のものです。

【“Excel上の図形をブログにアップロードする”の続きを読む】

サンプルというよりは手法というのが近いかもしれませんが、
VBAからexeファイルを起動させることができます。
下記例ではコマンドプロンプトを起動させています。”cmd.exe”と記載箇所にパスも含めて、exeファイル名を記載すればどんなアプリケーションも実行させられます。

'--------------------------------------------------------------------
' 関数名 : VbaToCmd
' 機能   : VBAからコマンドプロンプトを起動する
' 引数   : なし
' 戻り値 : なし
' 作成日 : 2008/03/09
' 作成者 : lily(http://vbatips.blog37.fc2.com/)
'--------------------------------------------------------------------
Sub VbaToCmd()

    Call Shell("cmd.exe", vbNormalFocus)

End Sub

で、今回のサンプルで何を企んでいるかといいますと、上記を実行すると単にコマンドプロンプトが立ち上がるだけですけど、その記載に続けてコマンドを記載しますと、そのコマンドを実行してくれます。

例えば、

以下のように記載しますと、コマンドプロンプト起動と同時にカレントディレクトリの一覧を表示してくれるわけです。

'--------------------------------------------------------------------
' 関数名 : VbaToCmd
' 機能   : VBAからコマンドプロンプトを起動する
' 引数   : なし
' 戻り値 : なし
' 作成日 : 2008/03/09
' 作成者 : lily(http://vbatips.blog37.fc2.com/)
'--------------------------------------------------------------------
Sub VbaToCmd()

    Call Shell("cmd.exe /k dir", vbNormalFocus)

End Sub

で、アプリ起動のサンプルにコマンドプロンプト起動をチョイスしたのはなぜか？って話しなんですが、
それは、次回以降で。

文字列中に全角文字が混在するかどうか？チェックする関数を作りました。
引数の文字列に全角文字が含まれている場合にtrueを返します。
裏を返せば、半角のみの場合はfalseを返します。

'--------------------------------------------------------------------
' 関数名 : CheckZenkaku
' 機能   : 対象文字列に全角文字が含まれているかどうかチェックします
' 引数   : str ：対象文字列[I]
' 戻り値 : True ：全角文字混在　False：全て半角文字
' 作成日 : 2008/03/09
' 作成者 : lily(http://vbatips.blog37.fc2.com/)
'---------------------------------------------------------------------
Function CheckZenkaku(ByVal str As String) As Boolean
Dim strAnsi As String

    strAnsi = StrConv(str, vbFromUnicode)
    
    If Len(str) = LenB(strAnsi) Then
        CheckZenkaku = False
    Else
        CheckZenkaku = True
    End If

End Function

この逆のパターンで、文字列中に半角文字が混在しているかどうかをチェックする関数も作りました。

'--------------------------------------------------------------------
' 関数名 : CheckHankaku
' 機能   : 対象文字列に半角文字が含まれているかどうかチェックします
' 引数   : str ：対象文字列[I]
' 戻り値 : True ：半角文字混在　False：全て全角文字
' 作成日 : 2008/03/09
' 作成者 : lily(http://vbatips.blog37.fc2.com/)
'---------------------------------------------------------------------
Function CheckHankaku(ByVal str As String) As Boolean

    If str = StrConv(str, vbWide) Then
        CheckHankaku = False
    Else
        CheckHankaku = True
    End If

End Function

特に必要ないと思いますが、一応、呼び出し元のサンプルを以下に掲載して置きます。
というか、テストプログラムです。
【“文字列中に全角文字が含まれるか？半角文字が含まれるか？チェック”の続きを読む】

このブログ以外でもブログを書いてますが、記事を書く際、Excel上で作成したオートシェイプを記事中に掲載することがあります。それを行うには、以下の手順を踏むことになります。

[Excelオートシェイプをブログへアップロードする手順]
１．Excelで図形を作成する
　↓
２．Excelで図形をコピー
　↓
３．グラフィックソフトを開く
　↓
４．コピーした図形を貼り付ける
　↓
５．JPEG指定で保存する
　↓
６．ブログの管理画面を開く
　↓
７．JPGファイルをブログへアップロード

一番力を入れたいのが、手順１の作業なのですが、それ以外の作業も意外に時間かかります。
ということで、今回２、３、４、５の手順をExcel VBAで自動化させることにしました。という経緯です。
なので、Excel上で作成したオートシェイプの図形をボタン一つでファイルに保存させたい方向けのサンプルです。

【制限事項】
今回のサンプルの仕様として、指定できる図形は、１つのみです。
例えば、下図のようにExcelシート状に複数の図形が存在し、それを１枚の画像として扱いたい場合は、複数の図形を選択してグループ化してください。そのグループ化した図形に対してJPGファイルを出力させることが可能です。



[図１　複数図形が存在する場合]

今回のサンプルはネット上のソースを流用しています。その為、内部でおこなっている動作については良くわかっていません。少しは調べましたが、情報に乏しく断念しました。
【“選択した図形をJPEG画像として保存する”の続きを読む】

条件付書式を更新する際、メニューの[書式] - [条件付き書式]で表示されるダイアログの設定で内容を変更していて面倒だと思ったことはありませんか？

これが、セル１つ２つならなんてことないんですが、シート全体に及ぶ場合は、頭が痛くなりそうです・・・
そんな場合でも、下図に示すような「書式のコピー／貼り付け」アイコン(図１参照)を使ってやれば、１箇所で設置した条件付き書式をコピーすることができます。しかし、このアイコンは条件付き書式だけでなく、普通の書式も全部コピーしてしまうので厄介です。 例えば、セル毎にフォントサイズが異なる場合は、コピー元のセルで扱っているフォントサイズがコピー先にも反映されるなど・・・。



- 図 1 書式のコピー／貼り付けアイコン -

上記のような問題を、VBAにやらせると条件付き書式のみを多くのセルに設定することができます。 今回はそんなサンプルですが、あまり汎用的には作っておらず、改造を想定して複雑な作りこみは避けております。

以下がサンプルコードです。

【概要】
引数に指定された範囲のセルに、条件付き書式を3つ追加する。
この条件付き書式はD列に設定されているデータを基に、条件を判断して背景色を変更するもの。
条件付き書式の意味は図２を参照してもらえればすぐにわかると思う。
また、条件付き書式は１つのセルに３つまでしか設定できないようです・・。

'--------------------------------------------------------------------
' 関数名 : SetFormatConditions
' 機能   : 選択範囲内のD列データ条件書式設定
' 引数   : rg : 選択セル[I]
' 戻り値 : なし
' 作成日 : 2008/03/03
' 作成者 : lily(http://vbatips.blog37.fc2.com/)
' 備考   :このサンプルではD列のデータに対しての条件
'--------------------------------------------------------------
Sub SetFormatConditions(rg As Range)

Dim lRow As Long
    rg.Select
    lRow = Selection.Row    '選択範囲の先頭行を取得。
                            'これをD列の先頭として設定することで、
                            '選択範囲全体に設定することができる
                            
    '一旦条件付き書式をクリアする
    Selection.FormatConditions.Delete
    
    'D列のデータが「保留」の場合背景色を34(水色)に設定
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=TEXT($D" & lRow & ",""aaaa"")=""保留"""
    Selection.FormatConditions(1).Interior.ColorIndex = 34
    
    'D列のデータが「重要」の場合背景色を40(オレンジ)に設定
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=TEXT($D" & lRow & ",""aaaa"")=""重要"""
    Selection.FormatConditions(2).Interior.ColorIndex = 40
    
    'D列のデータが「完了」の場合背景色を15(灰色)に設定
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=TEXT($D" & lRow & ",""aaaa"")=""完了"""
    Selection.FormatConditions(3).Interior.ColorIndex = 15
End Sub

- 図 2 条件付き書式を設定した例 -

【条件付き書式の補足】
サンプルコード上の条件付き書式は見づらいですが、実際セルに設定されている条件は以下のようになります。
　=TEXT($D18,"aaaa")="保留"
この条件で注目してほしいのは「$」の位置です。これは、シート上でD列固定という意味を持っているので、この条件付き書式をコピーすると、Dは固定で行数のみ変動します。まぁ、プログラム上では固定である必要はないのですが・・・。
ちなみに、Dと18の間に「$」マークをもってくると18行固定になります。

以下に呼び出し元のサンプルを掲載しておきます。

【“選択範囲内の条件付書式を一括更新する”の続きを読む】

以前掲載した下記の記事でアクティブシート上のシェイプ一覧を取得するサンプルを紹介しました。

・作業を効率化させるExcel VBA Tips集:図形（シェイプ）の一覧を取得する - livedoor Blog（ブログ）

今回は、それを若干応用して、
アクティブシート上のシェイプ内に書かれたテキストを取得するサンプルです。
シェイプ内に書かれたテキストというのは、下図のように四角形の中に書かれているような文字列のことをさします。
その為、今回のサンプルでは、ラインなどのテキストを書き込めないものに関しては、エラーになるので、サンプルにあるようにシェイプの名前を判定して回避しています。

このサンプルを応用すれば、シェイプ内の文字列を検索できるようなマクロも可能です。



【“図形（シェイプ）内のテキストを取得する”の続きを読む】