EXCEL VBAとワークシート関数の混同は邪道なのでしょうか？

前任者が作成したEXCELファイルがあります。

VBAとユーザーフォームを作成して、ACCESSのようにまったくEXCELを知らない人でもルーティン作業ができるようになっています。
ただACCESSと違って、それを修正するのが大変です。
項目が1列又は1行増えるだけで、修正する箇所が何箇所も出てきます。

そこで少し作り変えようと思っているのですが、VBAと関数の混同は良くないのでしょうか？
①　Application.WorksheetFunction.sum　ではなく、
②　シート作業列にSUM関数を入れる
②の場合では、他のモジュールやフォームからも参照できます。
又、変更するたびに①を実行させる必要もありません。

VBAとシート上の関数の混同は不都合はあるのでしょうか？
教えて下さい。

No.1 回答者： GooUserラック 回答日時： 2017/06/30 12:20

ただ単にVBAの組み方が悪いだけだと思います。

感じとしては
・VBAでの集計時の最終行を固定されているのではないでしょうか？
→ 最終行を調べる方法はいろいろありますので、事前に調べて最終行を調べて変数にする。
・VBAでの集計範囲を固定されているのではないでしょうか？
→ 集計範囲をグローバル変数にしておいて１ヶ所を修正するだけで済むようにする。

この回答へのお礼

回答くださりありがとうございます。

質問の書き方が良くありませんでした。申し訳ありません。
SUMは一つの例です。

＞ただ単にVBAの組み方が悪いだけ
その修正のために
・「シート上に直接関数を入れ、計算」
・「その値をVBAでも使用（参照セルの値として）」
以上二つのことに不都合、又は良くない事があるのでしょうか？

前任者は以前IT関連で仕事をしていました。
私は独力で勉強してきました。
すべてVBAでと言われれば、それでも修正は可能です。
しかし、シート上に関数を入れれば簡単だという所でもVBAを使用しているので、疑問を持った次第です。

お礼日時：2017/06/30 12:52

No.2 ベストアンサー 回答者： jcctaira 回答日時： 2017/06/30 12:33

「VBAとワークシート関数の混同は邪道」ではないと思います。

ただ次の点を注意する必要があるかと思います。

１．一番大きいのは「処理速度」です。
　・セルに計算式を設定されていると、EXCELはセルに値を設定するごとに【再計算】が行われしまうことが、処理速度が大幅に低下
　　することがあります。
　　そのため、プログラムを実行する場合【自動計算】を一時的にストップさせています。
　　　Application.Calculation = xlManual
　　　※処理完了後は「Application.Calculation = xlAutomatic」にします。
　・ただ注意しないといけないのは「計算を手動」にした場合、関数で計算した値をプログラムで参照したい場合【再計算が必要】かも
　　知れません。そのときは一時的に再計算をした方が間違いないかと思います。
　　　Range("B12").Calculate　　…　特定セルだけ再計算
　　　Worksheets(1).Calculate 　…　特定シートだけ再計算
　　　Application.Calculate 　　　…　開いているすべてのブックを再計算

２．処理ロジック
　　上記１．のように「再計算」を都度行う必要がある場合、漏れなく（ミス無く）対応が必要です。
　　そのため、過去のプログラムを修正する場合はミスが無いようにしっかりとプログラム内容を理解することが求められます。
　　※しっかりテストすれば良いのかも知れませんが…

３．セルにプログラムで毎回計算式を設定するのなら…
　もしプログラムでセルに毎回計算式を設定するのなら「Application.WorksheetFunction」で計算した方が良いかも知れません。
　初めから計算式を設定するのなら「ユーザに式を触らせないようにシート保護」した方が良いかと思います。
　その場合、プログラムでセルに値を設定する場合、セルに書き込みできるように「シートの保護解除」等が必要になります。

◆VBAとワークシート関数の混同は邪道ではないですが、色々な問題点を意識して、プログラムを作成しないと思わぬ所で
　落とし穴があるかも知れませんので注意が必要ですね。

色々と大変だと思いますが頑張ってください。

この回答へのお礼

回答ありがとうございます。

邪道ではないとの回答にほっといたしました。
又、具体的コードを記入して教えてくださり大変感謝いたします。

1.処理速度について
テストでは問題ないようです。
【手動→再計算】は現実的ではないのです。
そこにこのプログラムの作り方の問題（コードではなく）があると思うのです。
値を1ヶ変更する度に、label.captionに表示された小計やら総計やらを再計算して表示させているのです。
そのため値を1ヶ変更する度に【手動→再計算】が必要になります。

エクセル・フォームを使用せず、シートで作成した方がずっと簡単なのですが、エクセルがまったくできない数人の為に作成したものです。
かゆい所に手が届くようなコードが至る所にあり、大変分かりすらいものとなっています。
しかし使用する方は、きれいなフォームで満足しています。

2.処理ロジック
3.シートの保護
もおっしゃるとおり、落とし穴にはまらぬよう気をつけます。
励ましまでいただき、本当にありがとうございます。

お礼日時：2017/07/01 10:54

No.3 回答者： hallo-2007 回答日時： 2017/07/01 09:27

邪道ではないですが

VBAのコードがシンプルになる様でよいですが
＞項目が1列又は1行増えるだけで、修正する箇所が何箇所も出てきます。
項目が一列ふえるのは、データシートの構成が悪い
行が増えるに対応できないはVBAのコードが悪い

この回答へのお礼

教えていただきありがとうございます。

＞VBAのコードがシンプルになる
はい、まったくその通りに感じます。
＞データシートの構成が悪い
こちらもその通りです。
社員データの下、数行空けて（後残り2行）顧客データがあります。
他にも多々。

邪道ではないとのこと安心しました。

お礼日時：2017/07/01 09:40