=ConcatenateRangeText(範囲)の数式の更新

お世話になります。

CQ686セルに下記数式が入っています。
=ConcatenateRangeText(CP685:CP695)&ConcatenateRangeText(CV685:CV695)

なおCP685:CP695にも数式が入っています。

もともとの入力を変更すると、CP685:CP695までは自動計算され反映されるのですが、CQ686セルは更新されません。

対策として、CQ686セルに「Ctrl」+「F」で「=」を「＝」に置き換えをすると更新されます。
もしくは、CQ686セルをダブルクリックしてエンターをすると更新されます。

ただ、前者を下記マクロで実行するとなぜかCQ686セルが「#VALUE」になります。

CQ686セルだけ数式が自動で更新されない理由も分かりませんし、手動だと出来るのにマクロだとエラーになる理由も分かりません。
ご存知の方いらしたらアドバイスをお願いします。

Sub 数式更新()
Range( _
"E631:E849,K631:K849,Q631:Q849,W631:W849,AC631:AC849,AI631:AI849,AO631:AO849,AU631:AU849,BA631:BA849,BG631:BG849,BM631:BM849,BS631:BS849,BY631:BY849,CE631:CE849" _
).Select
Selection.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range( _
"CK631:CK849,CQ631:CQ849,CW631:CW849,DC631:DC849,DI631:DI849,DO631:DO849,DU631:DU849,EA631:EA849,EG631:EG849,EM631:EM849,ES631:ES849,EY631:EY849,FE631:FE849,FK631:FK849,FQ631:FQ849,FW631:FW849,GC631:GC849" _
).Select
Selection.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

質問者からの補足コメント

• 

  OSはWindows10で、エクセルは2013です。

    補足日時：2016/08/17 08:06

• 

  もともと検索して探してきた関数だったのですが、確認したらユーザー定義関数でした。

  No.1の回答に寄せられた補足コメントです。 補足日時：2016/08/17 15:03

• 

  正確にはCQ686には
  =ConcatenateRangeText(CP685:CP695)&ConcatenateRangeText(CV685:CV695)&ConcatenateRangeText(DB685:DB695)&ConcatenateRangeText(DH685:DH695)&ConcatenateRangeText(DN685:DN695)&ConcatenateRangeText(DT685:DT695)&ConcatenateRangeText(DZ685:DZ695)と入れてあります。
  そして、CP685には=CN$619で日付を参照、
  CP686からCP695には
  =IF(OR(CN687="",COUNTIF(CN687,"【*】"),COUNTIF(CN686,"【*】")),CN686,CN686&"→")
  が入っており、他のセルも関数だらけです。

  No.3の回答に寄せられた補足コメントです。 補足日時：2016/08/18 13:22

• 

  他のシートに入力したら全ての関数は再計算されるのですが、=ConcatenateRangeText(の関数だけが再計算されず、考えたのが同じ半角の「=」を検索して置き換えるという方法でした。Ctrl+Fだと置き換えられるのですが、これをマクロの記録でやるとエラーになるという事です。

    補足日時：2016/08/18 13:27

• 

  Replaceを使ったのは、単純に「マクロの記録」でCtrl+F→同じ半角=を検索して置き換え→記録の終了を行った結果でした。
  =ConcatenateRangeText(の長い数式は11行ごとに20セル×6列ごとに31セルあります。
  これはファイルの作り方自体に問題があるのでしょうか。
  やりたいのは一週間ごとの「日付-お客様-時間-お客様-時間、日付-お客様-時間…」を1か月分、20人分作ることです。
  上のマクロはとても軽快に動き、そのセルにとって最高の結果を表示してくれました。
  知識不足ですみません。

  No.4の回答に寄せられた補足コメントです。 補足日時：2016/08/18 18:04

• 

  最初の一文は、マクロをわざわざ作成していただき、それが思うように動き単純に感激したのを文章にしただけでしたので、不快に思われたら申し訳ありません。
  ただ、この関数を620個も使っている事で成立させている私のエクセルブックに問題があり、一から別の方法で作り直す必要があると痛感し、途方に暮れているところです(笑)
  
  まさに「インストラクチャーのネタ帳」から拝借した関数でした。
  
  今回の質問で、WindFallerさんにはとても親身になって何度も回答していただき感謝しています。
  （回答がつかないと思っていたので）
  ただ、目的はおっしゃるとおり複数のセルを一つのセル内に詰め込む事です。
  これはエクセルでする事に無理があるのでしょうか？

  No.5の回答に寄せられた補足コメントです。 補足日時：2016/08/19 13:13

No.5 ベストアンサー 回答者： WindFaller 回答日時： 2016/08/19 12:45

>上のマクロはとても軽快に動き、そのセルにとって最高の結果を表示してくれました。

たぶん、私のアドバイスは不要だとおっしゃっているのだ思いますが、満足して使われているとおっしゃるからには、私には、それ以上、何もいう資格はありません。

ただ、読者の方たちもいらっしゃいますから、もう少し、注釈を述べさせていただくと、

>他のシートに入力したら全ての関数は再計算されるのですが、
>=ConcatenateRangeText(の関数だけが再計算されず、考えたのが同じ半角の
>「=」を検索して置き換えるという方法でした。
>Ctrl+Fだと置き換えられるのですが、これをマクロの記録でやるとエラーになるという事です。

「インストラクターのネタ帳」からの'ConcatenateRangeText'で参考にしたものでしょうね。

このユーザー定義関数そのものに、私は疑問を感じています。ユーザー定義関数の中に、動的な働きを求めることは可能ですが、起動するための動力が必要です。そのきっかけを持ってくるのが、Volatileメソッドですが、このユーザー定義関数は物理的な時間を伴い、スピードとして遅すぎるのではないかと、感じるわけです。

さしずめ、レーシングカーで、そこらで売られている中古車のパーツを使うようなものだと思います。バランスが悪すぎるのです。それは、一般関数の100倍から1000倍、遅いからなのです。

それで、高速で走らせるのをやめて、ユーザー定義関数をやめて、これをプロシージャの中に組み入れててしまうことなのです。Volatileは、高速性能に耐えられるもの以外は、悪い結果を導いてしまいます。

記録マクロは、VBAプログラミングとはまったく違うものです。記録マクロがどうであっても、初歩的な所から新たに学ばないと、とてもVBA自体は扱えるものではありません。

>=ConcatenateRangeText(の長い数式は11行ごとに20セル×6列ごとに31セルあります。

それを繋げ合わせて、ひとつのセルの貼り付けることの意味がどういうことになるのか、ブックがハングしないのが不思議なぐらいです。
たぶん、一気にピークにならないように、Excelのメモリの保護機能が働いているのだろうと思います。

いずれ、時が来たら、また、私のアドバイスは可能になるかもしれません。お役に立てずにすみません。

この回答へのお礼

丁寧な回答をありがとうございました。

お礼日時：2016/08/21 13:23

No.4 回答者： WindFaller 回答日時： 2016/08/18 16:31

VBAで作ったユーザー定義関数は、よほど洗練されていないと、とても、一般の組み込み関数のようには使えません。

ふつうは、COMアドインにしたりします。

>=ConcatenateRangeText(の関数だけが再計算されず、考えたのが同じ半角の「=」を検索して置き換えるという方法でした。

それがどうして、全体的に、Replace で、＝を置換することになったのかユーザー定義関数を、広い範囲に置いたら、まず、ハング状態になって使えなくなると思います。再計算なら、Calculate メソッドです。

それに、補足で書かれた数式で、全角「＝」を置換すると、答えがでるようになるという理屈は良くわからないけれども、そこは、ユーザー定義関数ではなく、マクロコードの中で行うべきですね。

ConcatenateRangeTextというユーザー定義関数自体、そんなに広い範囲の値(77セル）の結果を、ひとつのセルに詰め込んでよいものなのか、私には疑問を持っています。一つのセルに、1,024文字しか表示できないはずです。

元の数式は消して、そこはマクロか、サブルーチン・マクロにして

Sub ConcatinateRanges()
Dim r As Range
Dim buf As String

For Each r In Range("CP685:CP695, CV685:CV695, DB685:DB695," & _
　"DH685:DH695, DN685:DN695,DT685:DT695, DZ685:DZ695").Areas
　r.Activate
buf = buf & ConcatenateRangeText(r)
Next r
Range("CQ686").Value = buf　　'"'" & buf '数値の場合は、接頭辞をつける
End Sub

***
もしも、サブルーチンするなら、

'命令：
sConcatinateRanges Range("CQ686"), Range("CP685"), 11, 6, 7

Sub sConcatinateRanges(tCell As Range, stCell As Range, _
　H As Variant, W As Variant, Cnt As Variant)
'tCell-書き込むセル,stCell-最初のセル, H-高さ,W-幅, Cnt-回数
Dim col As Long
Dim i As Long
Dim buf As String
For i = 1 To Cnt
　 buf = buf & ConcatenateRangeText(stCell.Offset(, col).Resize(H))
　 col = col + W
Next
　tCell.Value = buf 'or "'" & buf
End Sub

'//

また、再計算の手動と自動を交互に使うように、マクロ・ボタンでも置けばよいかもしれません。

少し、難しいことを書いているかもしれませんね。

No.3 回答者： WindFaller 回答日時： 2016/08/18 11:11

ちょっと割り込み失礼します。

前回から、この質問は読んでいましたが、あまりに、領域を広すぎて、ちゃんとしたアドバイスをあげられない状態になってしまったと思っています。

前回からの解説からすると、エラーは、Range内のパラメータは、255文字までで、それを超えてしまったからですが、「名前の定義」でまとめてしまえば、短いコードでも済みます。しかし、ふつう、マクロにはVBAマクロらしい書き方というものが存在します。

今回については、書かれてあるマクロの意味が書かれていません。全角[＝] を半角[=] にするということは、一体どんな意味があるのでしょうか？

再計算のためなら、再計算のメソッドがあります。
Worksheets("Sheet1").UsedRange.Calculate

最初、文字列として置かれている数式を、数式にさせることではないかと思いました。しかし、試してみましたが、そのような事象は起こりません。

また、ここの掲示板では、質問の中のReplace関数の
What:="=", Replacement:="=" は同じ半角にしか見えませんが、
パラメータのMatchByte:=Falseにすれば、それは、検索側の「=」は、半角・全角どちらでも、取得できます。コードについては、まとめて下記に書いています。

次に、

=ConcatenateRangeText(CP685:CP695)
これで出力するのは、文字列ですか？数値ですか？
数値なら、おそらく指数になってしまうはずです。

これほど、大きなデータを扱うブックには(どこのシートであっても）、このようなユーザー定義関数は置けません。一般の組み込み関数の100倍～1000倍も計算が遅いわけで、再計算を繰り返されたら、溜まったものではありません。
もし、セル上に数式を置きたいようでしたら、今回のような負荷のかかるユーザー定義関数をやめて、一般の関数にしたほうがよいです。
Volatile メソッドを置くと、そのセルとは関係のない計算信号＝Calculateイベントが走るたびに反応しますから、通常は入れません。引数が変化すれば、再計算します。

>下記マクロで実行するとなぜかCQ686セルが「#VALUE」

計算が追いつかないのだと思います。

>CQ686セルに下記数式が入っています。
>=ConcatenateRangeText(CP685:CP695)&ConcatenateRangeText(CV685:CV695)
これは、マクロの実行時に、マクロの中でサブルーチンとして計算させて、その場所CQ686に出力させればよいわけです。

Range("CQ686").Value = ConcatenateRangeText(Range("CP685:CP695"))
Range("CQ686").Value = Range("CQ686").Value　& ConcatenateRangeText(Range("CV685:CV695"))

のようにします。

また、今回のように、他の場所にも数式がある場合、
マクロの実行の際に、手動・自動計算を切り替えるようにします。

手動・自動計算の場合は、
Application.Calculation = xlCalculationManual '手動
　-マクロ実行-
Application.Calculation =xlCalculationAutomatic　'戻す

とします。

質問内容とはズレがあるとは思いますが、私の発言によるコードサンプルです。ただ、お返事の内容によっては大幅にそのコードが変わる可能性があります。

'//
Sub TestReplace()
　Dim i As Long
　Application.Calculation = xlCalculationManual '手動
　With ActiveSheet
　　For i = 5 To 185 Step 6
　　　.Range(.Cells(625, i), .Cells(843, i)) _
　　　.Replace What:=" =", Replacement:="=", LookAt:=xlPart, _
　　　SearchOrder:=xlByRows, MatchByte:=False
　　Next
　　.Range("CQ686").Value = ConcatenateRangeText(.Range("CP685:CP695"))
　　.Range("CQ686").Value = .Range("CQ686").Value & _
　　ConcatenateRangeText(.Range("CV685:CV695"))
　End With
　Application.Calculation = xlCalculationAutomatic '戻す
End Sub

No.2 回答者： siffon9 回答日時： 2016/08/17 19:54

ConcatenateRangeTextのFunction定義の最初に

Application.Volatile　が記述されていますか？

https://msdn.microsoft.com/ja-jp/library/office/ …

No.1 回答者： palebluedot 回答日時： 2016/08/17 13:46

ユーザー定義関数ですよね？