Excelのマクロをコピーする

仕事で「Excelの関数が使えない」と相談を受けた。
下請から送られて来たExcelファイルを報告書にまとめようとしているのだが、計算結果が表示されないのだという。
そのExcelファイルを送ってもらうと、確かにセルに「=cngtext」という関数らしきものが入力されているのだが、計算結果は「#VALUE!」と表示されている。
第一、Excelに「cngtext」などという関数は存在しない。

無題50

雰囲気としては、左側の「C4」セルに入力された計算式の計算結果を表示しているらしい。
こりゃ後ろでカスタマイズした何かがあるな・・と思い、探ってみることに。

まず疑ったのは、「名前の定義」だ。
「=EVALUATE」に「cngtext」という名前をつけて保存してあるのなら、参照セルの数式の自動計算を行ってくれる。

セルに入力した数式を別のセルで計算する

無題51

「数式」タブの「名前の管理」を開いて見るが、「cngtext」などという名前は存在していない。
こりゃ、いよいよマクロの仕業だな・・・ということに。

しかし、よく見ると、送ってもらったExcelファイルの拡張子は「xis」だ。
これではマクロがいっしょに保存されていない。
もう一度オリジナルのファイルを送ってもらうと、やっぱり拡張子は「.xlsm」になっていた。
最近のユーザーは「拡張子を表示しない」設定にしているため、ファイルの違いに気づかないことが多い。

無題52

そこで、まとめたい「報告書」も、保存時にファイルの種類をExcel マクロ有効ブック(.xlsm)」にして保存しなければいけない。
※ 古い「.xls」ならマクロもいっしょに保存できます。 新しい「.xlsx」はマクロを保存できないため「.xlsm」で保存しなくてはいけません。

無題53

で、オリジナルのファイルを改めて開くと、「セキュリティの警告」が表示されているので分かる。

無題54
無題55

「コンテンツの有効化」を押して、マクロを有効にする。
さらに、マクロの編集が行えるようにするために、「ファイルメニュー」から「オプション」を開き・・・

WS000000_20160909082952f4a.jpg

リボンのユーザー設定」タグから「開発」にチェックを入れる。

WS000001_20160909083205e34.jpg

すると、「開発」タグが表示されるので、左端の「Visual Basic」を開く。
(この時、オリジナルファイルと、コピーしたいファイルの2つとも開いた状態にしておくこと)

WS000002_20160909083428f79.jpg

すると、オリジナルファイルの「標準モジュール」の中に、「Module 1」としてマクロがあることが確認できる。

無題56

よく見ると、「cngtext」として、加減乗除の記号をExcelの演算記号に置き換えて計算させるようにマクロが組まれていることが分かる。どうやら「cngtext」は「change text」を意味しているらしい。
一応、テキストで載せておく。

Sub sungwoo()

Function cngtext(strChr As String) As Double
Dim i As Integer
Dim j As Integer
Dim newStr As String
Dim strtemp As String

i = Len(strChr)
If i = 0 Then Exit Function
For j = 1 To i
strtemp = Mid(strChr, j, 1)
Select Case strtemp
Case "+", "-", "*", "/", "(", ")", "^", "."
newStr = newStr & strtemp

Case "+"
newStr = newStr & "+"

Case "ー", "-"
newStr = newStr & "-"

Case "×", "*", "x", "X"
newStr = newStr & "*"

Case "÷", "/"
newStr = newStr & "/"

Case "("
newStr = newStr & "("

Case ")"
newStr = newStr & ")"

Case "^"
newStr = newStr & "^"

Case Else
If IsNumeric(strtemp) Then
newStr = newStr & strtemp
End If
End Select
Next j

cngtext = Application.Evaluate(newStr)

End Function


これを、コピーしたい「報告書」のほうへもコピーする。

WS000003_201609090844431cc.jpg

左側のウィンドウで、「Module 1」をつまんで、「報告書」へドラッグする。

無題57

「報告書.xlsm」のほうにも同じマクロ「Module 1」がコピーされたことが確認できる。
保存」ボタンを押して、「ファイル」メニューから「終了して Microsoft Excelへ戻る」を選択する。

WS000004_20160909085113ead.jpg

Excelに戻ってもまだ自動計算は行われません。
「上書き保存」を押して、計算結果が表示されるか確認する。

WS000005_2016090908525537d.jpg

これでも計算結果が表示されないのなら、一旦Excelを閉じて、再度開き直す。

無題58

すると、自動計算の結果が表示されているはず。

ただし、CALS納品の場合、この「マクロ有効ブック(xlsm)」は認められていませんから、通常の「Excelファイル(.xls)」に変換しないといけない。
そのため、自動計算のセルをコピーして、「値のみを貼り付け」すれば良いような気がするのだが、コピーした時点で「cngtext」がコピーされてしまうので、値を貼り付けることはできない。
そこで一手間加え、自動計算セルの隣のセルに、自動計算セルの値を「=(セル番号)」で表示する。
このセルを「切り取り」して、自動計算セルに「値のみ貼り付け」すれば、計算結果だけを上書きすることができる。
これで、CALS納品のために「Excelファイル(.xls)」として保存することができる。

面倒だね。

Microsoft Office Home and Business 2016(最新)|オンラインコード版|Win対応
マイクロソフト (2015-09-30)
売り上げランキング: 13
関連記事
スポンサーサイト

comment

Secret

プロフィール

どんぶらこ

Author:どんぶらこ
南信州からどんぶらこ!
どんぶらこ HPへお立ち寄りください

来店者数
カレンダー
03 | 2017/04 | 05
- - - - - - 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 - - - - - -
最新記事
最新コメント
月別アーカイブ
最新トラックバック
カテゴリ
どんぶらこへのMail

名前:
メール:
件名:
本文:

記事検索
リンク
amazon
amazon
ProductCloud