VBAの勉強を始めてみた

色々試しています。

数字を計算可能な数値に変換する

ここに外部アプリケーションが出力したEXCELファイルがあります。

f:id:kouten0430:20170823001304j:plain

 

SUM関数で数値を合計をしようとしたのですが、画像のように計算結果が「0」になってしまいました・・・・・・。

f:id:kouten0430:20170823001427j:plain

 

結論からいうとこれはセル内にカンマが存在し文字列扱いとなっているためです。書式設定で「数値」や「通貨」などにしても計算対象になってくれません。

数値が文字列となってしまう原因は他にもありえます。
・冒頭にシングルクオートが入っている
・なんらかの原因で改行コードが入ってしまっている 等々

 

数字が入った列を選択した状態で、区切り位置→区切り位置指定ウィザード1/3で完了することで数字を計算可能な数値にすることができます。

f:id:kouten0430:20170823001558j:plain

f:id:kouten0430:20170823002441j:plain

 

・・・・・・が、区切り位置指定ウィザードは1列ずつしか処理することができないため対象の列が多いと手間と時間がかかります。

そこでマクロの登場です。文字列となってしまっている数字の入ったセルを複数選択し、まとめて数値に変換します。シングルクオート、カンマ、余白、改行を取り除き、最後にVal関数で数値変換しています(ダメ押し)。

 

***************************************

Sub 数字を数値に変換()
   
    Dim myRange As Range

    For Each myRange In Selection.SpecialCells(xlCellTypeVisible)
        If myRange.Value <> "" And TypeName(myRange.Value) <> "Date" Then
        'セルの値が空白,日付の場合は処理をしない
        '数字以外はVal関数が0を返すので選択しないで下さい
            myRange.Value = Trim(myRange.Value)
            myRange.Value = Replace(myRange.Value, vbLf, "")
            myRange.Value = Replace(myRange.Value, vbCrLf, "")
            myRange.Value = Replace(myRange.Value, "'", "")
            myRange.Value = Replace(myRange.Value, ",", "")
            myRange.Value = Val(myRange.Value)
        End If
    Next myRange
   
End Sub

***************************************
※SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。標準モジュールにコードを貼り付けてマクロを使用する方法はこちら

数式を相対参照にする

今回は絶対参照になっている数式を相対参照にします。
数式内のセル参照を絶対参照⇔相対参照に変更するには・・・・・・数式内の変更したい部分にカーソルを合わせてF4キーを押せば、以下のように順番に切り替わっていきます。
 
行列とも絶対参照→行のみ絶対参照→列のみ絶対参照→行列とも相対参照(に戻る)

 

でも、変更したい数式が何十個、何百個とあったらこんなことを手動でいちいちやってられません。マクロで一括変更しちゃいましょう。

構文や、引数については過去記事を参照下さい。

 

以下はApplication.ConvertFormulaメソッドを使った簡単なプログラムです(選択範囲内の数式を行列とも相対参照にします)

 

***************************************

Sub 数式を行列とも相対参照にする()
    Dim myRange As Range

    For Each myRange In Selection.SpecialCells(xlCellTypeVisible)
        If myRange.Value <> "" Then   'セルの値が空白の場合は処理をしない
            myRange.Formula = Application.ConvertFormula(Formula:=myRange.Formula, _
            FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
        End If
    Next myRange
   
End Sub

***************************************
※SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。標準モジュールにコードを貼り付けてマクロを使用する方法はこちら

数式を列のみ絶対参照にする

数式内のセル参照を絶対参照に変更するには・・・・・・数式内の変更したい部分にカーソルを合わせてF4キーを押せば、以下のように順番に切り替わっていきます。

 

行列とも絶対参照→行のみ絶対参照→列のみ絶対参照→行列とも相対参照(に戻る)

 

でも、変更したい数式が何十個、何百個とあったらこんなことを手動でいちいちやってられません。マクロで一括変更しちゃいましょう。

構文や、引数については過去記事を参照下さい。

 

以下はApplication.ConvertFormulaメソッドを使った簡単なプログラムです(選択範囲内の数式を列のみ絶対参照にします)

 

***************************************

Sub 数式を列のみ絶対参照にする()
    Dim myRange As Range

    For Each myRange In Selection.SpecialCells(xlCellTypeVisible)
        If myRange.Value <> "" Then   'セルの値が空白の場合は処理をしない
            myRange.Formula = Application.ConvertFormula(Formula:=myRange.Formula, _
            FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
        End If
    Next myRange
   
End Sub

***************************************
※SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。標準モジュールにコードを貼り付けてマクロを使用する方法はこちら

数式を行のみ絶対参照にする

数式内のセル参照を絶対参照に変更するには・・・・・・数式内の変更したい部分にカーソルを合わせてF4キーを押せば、以下のように順番に切り替わっていきます。

 

行列とも絶対参照→行のみ絶対参照→列のみ絶対参照→行列とも相対参照(に戻る)

 

でも、変更したい数式が何十個、何百個とあったらこんなことを手動でいちいちやってられません。マクロで一括変更しちゃいましょう。

構文や、引数については過去記事を参照下さい。

 

以下はApplication.ConvertFormulaメソッドを使った簡単なプログラムです(選択範囲内の数式を行のみ絶対参照にします)

 

***************************************

Sub 数式を行のみ絶対参照にする()
    Dim myRange As Range

    For Each myRange In Selection.SpecialCells(xlCellTypeVisible)
        If myRange.Value <> "" Then   'セルの値が空白の場合は処理をしない
            myRange.Formula = Application.ConvertFormula(Formula:=myRange.Formula, _
            FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
        End If
    Next myRange
   
End Sub

***************************************
※SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。標準モジュールにコードを貼り付けてマクロを使用する方法はこちら

数式を絶対参照にする

数式内のセル参照を絶対参照に変更するには・・・・・・絶対参照にしたい部分にカーソルを合わせてF4キーを押せば、下記のように順番に切り替わっていきます。

 

f:id:kouten0430:20170820124350j:plain

行列とも絶対参照

 

f:id:kouten0430:20170820124416j:plain

行のみ絶対参照

 

f:id:kouten0430:20170820124434j:plain

列のみ絶対参照

 

f:id:kouten0430:20170820124452j:plain

行列とも相対参照(に戻る)

 

でも、変更したい数式が何十個、何百個とあったらこんなことを手動でいちいちやってられません。

VBAなら一瞬でできる方法があるだろうと、調べてみたら・・・・・・ありました。

 

Application.ConvertFormula メソッド

 

という、数式を相対参照⇔絶対参照に切り替えてくれる気の利いたメソッドがあるそうです。ここまで用意するなら、いっそのことEXCELの標準機能にしたらいいんじゃないかと思うのは自分だけでしょうか・・・・・・?_(:3 」∠ )_

 

構文
Application.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)

 

引数
Formula(必須)     ・・・・・・変換対象となる数式。または数式の入ったセル等
FromReferenceStyle(必須)・・・・・・変換前の数式の参照形式。A1形式かR1C1形式かを

                定数で指定する(大半はA1形式かと思います)
ToReferenceStyle(省略可)・・・・・・変換後の数式の参照形式。A1形式かR1C1形式かを

                定数で指定する(A1形式で問題ないかと。省略で無

                変換)                
ToAbsolute(省略可)   ・・・・・・変換後の数式を絶対参照か相対参照かを定数で指定

                する。(省略で無変換)
RelativeTo(省略可)   ・・・・・・相対参照の基点となるセルを指定する。(たぶんほ

                とんど使わないでしょう)

 

引数に使用する定数
xlA1:A1形式
xlR1C1:R1C1形式
xlAbsolute:行列とも絶対参照
xlAbsRowRelColumn:行のみ絶対参照
xlRelRowAbsColumn:列のみ絶対参照
xlRelative:行列とも相対参照

 

以下はApplication.ConvertFormulaメソッドを使った簡単なプログラムです(選択範囲内の数式を行列とも絶対参照にします)

 

***************************************

Sub 数式を行列とも絶対参照にする()
    Dim myRange As Range

    For Each myRange In Selection.SpecialCells(xlCellTypeVisible)
        If myRange.Value <> "" Then   'セルの値が空白の場合は処理をしない
            myRange.Formula = Application.ConvertFormula(Formula:=myRange.Formula, _
            FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
        End If
    Next myRange
   
End Sub

***************************************
※SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。標準モジュールにコードを貼り付けてマクロを使用する方法はこちら

標準モジュールにVBAのコードを貼り付けてマクロを使用するには

当ブログを含め、ネット上にはあまたのVBAコードが記載されたWebページが存在します。そこで見つけたお気に入りのマクロをコピー&ペーストし、自分のパソコン(EXCEL)で使用するには、どうすればいいのか?
今回はVBEの起動から、標準モジュールへのコードの貼り付け、マクロの実行までを順番に説明したいと思います。

 

まずVBEとは何か?VBEとはVisual Basic Edtorの略で、VBAのコードでプログラミングを行うためのアプリケーションです。EXCELから起動することができ、プログラムの作成、管理、デバッグ等を行うことができます。

 

EXCELのデフォルトではVBEを起動するためのボタンが非表示になっているので、まずはVBEを起動できるように設定変更しましょう。リボンのタブ内のどこでもいいので右クリックして「リボンのユーザー設定」をクリックします。(ファイル-オプション-リボンのユーザー設定からでも同じように入れます)

 

f:id:kouten0430:20170819140923j:plain

リボンのユーザー設定を開いたら、「開発」にチェックを入れてOKを押します。

 

f:id:kouten0430:20170819141013j:plain

リボンに開発タブが追加されます。開発タブの中には、マクロに関する様々なツールが用意されており、その中のひとつにVBEがあります。※画像の「Visual Basic」のボタンがVBEを起動するためのボタンです。

 

さっそくVBEを起動してみましょう。最初は標準モジュールが無い状態なので

f:id:kouten0430:20170819141139j:plain

f:id:kouten0430:20170819141212j:plain

挿入→標準モジュールをクリックし、標準モジュールを挿入します。
標準モジュールとは何??他にもモジュールがあるの??と思われるかも知れませんが、Webページからコピー&ペーストしたマクロを取りあえず使いたい!という目的であれば、特に知らなくても大丈夫なのでここでは割愛します。

 

f:id:kouten0430:20170819141307j:plain

標準モジュールを挿入したら、標準モジュールのコードウィンドウが表示されます。まだプログラムが何もなく真っ白な状態です。

 

f:id:kouten0430:20170819141403j:plain

コードウィンドウに、SubからEnd Subまでのコードを貼り付けます。

 

f:id:kouten0430:20170819141458j:plain

複数のコードを貼り付けたい場合は、End Subの後に改行し、SubからEnd Subまでを同じように貼り付けします。このSubからEnd Subまでをプロシージャーといい、ざっくり言うとこれが一つのマクロの単位になります。

 

さて、これで標準モジュールへのVBAコードの貼り付けが完了しました。VBEを閉じて(右上の「×」を押す)、EXCELの画面に戻りましょう。

 

f:id:kouten0430:20170819141621j:plain

さきほど標準モジュールに貼り付けたマクロを実行するには、開発タブから「マクロ」のボタンを押します。

 

f:id:kouten0430:20170819141705j:plain

すると、マクロの一覧が表示される(マクロの名前はプロシージャー名)ので、目的のマクロを選択して「実行」を押します。これで、SubからEnd Subまでの間に記述されたコードの内容が実行される(マクロが実行される)ことになります。

 

ただし、VBEで記述したマクロはBook単位で保存されるので(例えばBook1.xlsxとか)、該当のBookを開いている間しかマクロを実行することができません。すべてのBookで共通してマクロを実行できるようにするには「個人用マクロブック」にマクロを保存する必要があります。

 

個人用マクロブックの作成方法はこちら 

kouten0430.hatenablog.com

 

マクロをリボンに登録する方法はこちら 

kouten0430.hatenablog.com

 

マクロをクイックアクセスツールバーに登録する方法はこちら 

kouten0430.hatenablog.com

 

右からn文字目の前に改行を入れる

右から数えてn文字目の前に改行を入れます。
対象となるセルを選択した状態でマクロを実行して下さい。

 

f:id:kouten0430:20170810134450j:plain

試しに「5」を指定します。

 

f:id:kouten0430:20170810134520j:plain

右から数えて5文字目の前に改行が入りました。

 

複数のセルの特定の位置に改行を入れたいけど、対象となるセルが多すぎて一つ一つ手作業でなんてやってらんないよー!という場面があればきっと役にたつと思います。

左から・・・Verとは別に、右から・・・Verをあえて準備したのは、場合によっては右から数えたほうが都合の良い場面があるからです。左からの文字数が不揃いで、右からの文字数が一定であるような場面です。(例えば下記のように「改行したい文字」の前、つまりすべての対象に右から数えて7文字目の前で改行を入れたい場合)

 12345改行したい文字
 123改行したい文字
 123456改行したい文字
 1234改行したい文字
 1234567改行したい文字

まぁ、でもそんな場面が無ければ無用の長物です。軽く読み流して下さい。

 

***************************************

Sub 右からn文字目の前に改行を入れる()
    '右から数えてn文字目の前にセル内改行を入れます
    Dim myRange As Range
    Dim nr As Variant
   
    nr = Application.InputBox(Prompt:="右から何文字目の前に改行を入れますか?", Type:=1)
        If TypeName(nr) = "Boolean" Then
            Exit Sub
        End If
   
    For Each myRange In Selection.SpecialCells(xlCellTypeVisible)
        If myRange.Value <> "" And TypeName(myRange.Value) <> "Date" Then   'セルの値が空白,日付の場合は処理をしない
            myRange.Value = Application.WorksheetFunction.Replace(myRange.Value, Len(myRange.Value) - nr + 1, 0, vbLf)
        End If
    Next myRange
   
End Sub

***************************************
※SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。