重宝した Excel関数 TXT化/数式化 マクロ

Excelの広場

セルに書いてある関数式を 移動じゃなくてコピペしたいとき、参照がずれたりうまくいかないときがありますよね。

そんなときは、数式バーに書かれている関数式を 一つづつ、C & V でコピペするのですが、数が 100 とかあったらやれません..

下記表は Sheet2 から Sheet5 までのセルを参照しています。

修正したくて、Sheet を 削除 なんかしたら セル値 参照先 のリンクがなくなる為、下記表のように エラー になってしまいます。

それを回避するには、修正する前に 他シートを参照しているセルの数式を ただの文字 (以下 TXT)に 修正する必要があるのですが、

TXT 変換しなければいけないセルが、これも 100 とか、数あった場合、やれません。

そこで!選択セルを TXT にしたり、Re TXT にしたり する マクロ を作りました。

実務ですごく役に立ったので 紹介いたします

Max
マクロ、VBA、VBE 、不明な方は、
下記を合わせてお読み下さいだワン!
Excel マクロの VBA を 書く VBE の 準備

Excelの広場 Max 最初に、「Excel マクロ の VBA を 書く VBE」って ? な方は、下記記事を合わせてお読みくださいだワン! 「Excel マクロ・VBA・VBE ってなぁに?」記 ...

続きを見る

【TEX化 マクロ VBA】

Sub 選択セルTXT ( )

    Dim A As Range
    Dim tate As Long
    Dim yoko As Long
    Dim str1 As String

    Speeding_up = True '画面更新無、イベント無効、計算手動

    For Each A In Selection

        tate = A.Row
        yoko = A.Column

        str1 = Cells(tate, yoko).Formula
        Cells(tate, yoko).Value = "'" & str1

    Next

    Speeding_up = False '描画再開、イベント有効、自動計算

End Sub

【Re TEX化 マクロ VBA】

Sub Re選択セルTXT ( )

    Dim A As Range
    Dim tate As Long
    Dim yoko As Long
    Dim str1 As String
    Dim str2 As String

    Speeding_up = True '画面更新無、イベント無効、計算手動

    For Each A In Selection

        tate = A.Row
        yoko = A.Column

        str1 = Cells(tate, yoko).Value
        str2 = Mid(str1, 1)
       
        Cells(tate, yoko).Value = str2
   
    Next

    Speeding_up = False '描画再開、イベント有効、自動計算

End Sub

数式をTXT化する【選択セルTXT マクロ解説】

最初に 引数 データー型 宣言 を書きます。

Dim A As Range
Dim tate As Long
Dim yoko As Long
Dim str1 As String

選択セルTXT マクロでは、
引数を 4データー型を 3種類 使いました。

データ型に関しては、
下記を合わせてお読み下さい。

Excel マクロ に出てくる 変数 とか、宣言のDimとか、データ型って何よ。

Excelの広場 最初にまず?となるのが、Dimで変数を宣言することでしょう。数学で X とか Y とかでてきましたよね。 例えば、 X = 5 Y = 10 とか.. 日本語は難しい言い方をして X ...

続きを見る

Max
データ型 Range の格納範囲が書いてないけど、

Range は、オブジェクト型 だワン!

VBA 処理の 高速化。

Speeding_up = True

マクロ、
選択セルTXT /Re選択セルTXT ともに、

データー型 宣言 の 後、
最初にこの命令を書きます。

最初に VBA 処理の 高速化 から始まります。

高速化する手法は、色々あります。
VBA 高速化三点セット をマクロ化しました。

ポイント

Speeding_up 変数を True にすれば、高速 になり False にすれば、初期値 に戻ります。

高速化に関しては、
下記を合わせてお読み下さい。

Excel マクロ VBA の 高速化

Excelの広場 Excel VBA を高速化するマクロを紹介します。 このまま、コピペ して使って頂ければ OK です。 Maxマクロ とか VBA に関して?な方は、 下記記事をお読みくださいだワ ...

続きを見る

選択している全ての セル範囲 を For で 命令を回し処理を実行しよう!

下記を日本語でいうと、

For Each A In Selection

Next

選択範囲内 の セル を、
順次 引数A に代入し、

Next まで はさまれている 命令文 を
実行しなさい!という意味です。

なんか日本語がしっくりこないですね..

図で表すと..

For Next に 挟まれている命令1つめ、【選択抽出されているセル の 位置 を知る】

For Next で選択している
セルA が選ばれました。

下記は、選択セルA の 縦方向 Row
横方向 Column を 変数 tate,yokoに
代入しなさい!という命令です。

tate = A.Row
yoko = A.Column

(例)D5 が選択されている場合、
tate = 5  yoko = 4 になります。

変数 tate と yoko の入れ物は、
-2,147,483,648 ~ 2,147,483,647
あれば十分なので、

Long(長整数型)にしました。

For Next に 挟まれている命令 2つめ、【選択セル の 数式を取得 Formula

例えば、
D5 セルが選択されていたとします。

Formula とは、
日本語で、 と言う意味ですよね。

下記命令は、上から5つめ、
左から4つめ、(D5)に書いてある を 変数 str1 に代入しなさい!

という命令になります。

str1 = Cells(tate, yoko).Formula

注意ポイント

セルが 未記入(空白)だった場合、空白文字 として 取り込まれます。

変数 str1 の入れ物には、
文字 が入ってくるので String にしました。

str1 に取り込んだ数式の頭に ’ をつけ 文字 にします。

セルに Sheet2 から Sheet5 の A1 セル 値を足す計算式が入っているとします。

この状態で Sheet4削除するとリンク先がなくなる為、エラー になってしまいます。

      

それを防ぐため、シート削除する前に 変数str1 に格納されている数式の頭に ’ をつけ 文字 にするわけです。

式の頭に ' (アポストロフィ,右引用符)をつけるには、下記のように 文字合成(&で繋ぐ)します。

アポストロフィ で文字(txt)化 した 式 を読込んだセルにもどします。

 

ここまでの処理を選択されている セルが 終わるまで繰り返します。

TXT 文字を 数式に戻す。【Re選択セルTXT マクロ解説

Dim A As Range
Dim tate As Long
Dim yoko As Long
Dim str1 As String
Dim str2 As String

Re選択セルTXT マクロでは、
引数を 5データー型を 3種類 使いました。

Max
下記 データー型 宣言 以下 コードは、選択セルTXT】同じだワン

Speeding_up = True '画面更新無、イベント無効、計算手

    For Each A In Selection

        tate = A.Row
        yoko = A.Column

ドングリ爺さん
その通りじゃ!選択セルTXT とは、 変数 yoko 以降の のコードが違うだけです。

str1 = Cells(tate, yoko).Value
str2 = Mid(str1, 1)

ste1 と str2 コード処理の内容 (処理 フロー)

処理は、変数 str1 に 選択セルの内容を格納し、変数 str2 に 頭の一文字(アポストロフィ)を除いた内容を 変数 str2 に格納しています。

ste1 と str2 コード処理の内容 (具体的な動き)

選択セルに
'=Sheet2!A1+Sheet3!A1+Sheet4!A1+Sheet5!A1 が格納されているとします。

処理結果、
変数 str2 は、先頭の アポストロフィ を除いた内容が格納されます。

選択セルTXT、Re選択セルTXT マクロの最終処理

選択セルTXT、Re選択セルTXT マクロ も
これら 二つの処理が違うだけで 選択セルが 無くなるまで処理を繰り返します。

全ての処理が終わったら、

Speeding_up = False を実行し、

初期状態
(画面更新有、イベント有効、計算自動)に戻し 、

マクロを終了 します。

 

 

 

 

スポンサーリンク

-Excel の広場

Copyright© ドングリ爺のblog , 2021 All Rights Reserved Powered by AFFINGER5.