[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『TextBoxへの書き込みが途中で終わる』（みーとん）
Excel 2003 / Windows XP
いつもお世話になっています。 テキストボックスへセルの数式を書き込ませるマクロを書きました。 シートによって、途中で終わってしまうケースがあって原因を調べているのですが、まったくわかりません。 どなたか教えていただけないでしょうか。
【やりたいこと】 エクセルの101行目に式が入っているセルがあれば、同じ列の100行目の項目とセットでTextBoxへ書き込む。
【コード】 Sub supplement()
Range("A100").Select
Range(Selection, Selection.End(xlToRight)).Select
Cn = Selection.Columns.Count
txtNameA = ActiveSheet.Name & "_TEXT_A" 'テキストボックスにつける名前 ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 1.5, 600, 1211.25, 894#).Select '新規作成 Selection.Name = txtNameA 'テキストボックスに名前をつける
For ii = 1 To Cn
If Cells(101, ii).Formula <> "" Then
AAA = Cells(100, ii).Formula
BBB = Cells(101, ii).Formula
'各項目の式をテキストボックスに挿入
ActiveSheet.Shapes(txtNameA).Select
Selection.Characters.Text = Selection.Characters.Text & AAA & " : " & BBB & Chr(10) & Chr(10) '値のセット"
End If
Next
End Sub
【問題点】 式の入っているセルの末尾がAA101としたら、途中で抜けたりZ101の式までしか書き込まれていないシートがある。 それぞれのセルの書式設定は「標準」で特に変わりない。 また、これらをTextBoxではなくメッセージボックスで表示させると末尾のAA101までの式が表示されるため、TextBoxへの書き込み方法がまずいのかなぁ、、、と予測したのですが、どこがどうまずいのか、まったくわかりません。
どうぞよろしくお願い致します。
過去ログに [[20060801104239]] 『テキストボックスの文字数制限』（ぴあ）
ところで、たとえばA1に式があるかどうかは
Range("A1").Formula がTrueかFalseかで判断できる。
(ぶらっと)
>Range("A1").Formula がTrueかFalseかで判断できる。
これは、HasFormula ですね！！
これはこれとして・・・・。
図形のテキストボックスに長い文字列（255を越える）を設定したい時は、 255以下の文字列にして、小刻み設定していく ということですね！！
でも、そのぐるぐるループさせるコードを 直接 「supplementというプロシジャー内に記述する」 なんてことは、しないことですよ。
テキストボックスに長い文字列を設定するという処理は、いつか別のプログラムでも 出てくるかもしれませんよね？ ツールとして、作成しておいて、次回は、 そのツールを呼び出すだけで済むようなプログラムにしておいてください。
supplementとは、違う標準モジュールに
Option Explicit
Sub txtput(ByVal tbx As Shape, ByVal txt As String)
'指定されたテキストボックスに文字列を設定する
'tbx 文字列を設定するテキストボックスオブジェクト
'txt テキストボックスに設定する文字列（２５５以上でも可）
Const lim As Long = 255
Dim g0 As Long
Dim g1 As Long
Dim pstr As String
g0 = Len(txt)
g1 = 1
With tbx.TextFrame
.Characters.Text = ""
Do While g0 > 0
If g0 > lim Then
pstr = Mid(txt, g1, lim)
Else
pstr = Mid(txt, g1)
End If
.Characters(g1, Len(pstr)).Text = pstr
g0 = g0 - Len(pstr)
g1 = g1 + Len(pstr)
Loop
End With
End Sub
このように長い文字列設定用のプログラムを作っておくと・・・、
Sub smp1()
Dim shp As Shape
Dim mystr As String
Dim g0 As Long
For g0 = 1 To 1000
mystr = mystr & StrConv(g0, vbWide)
If g0 Mod 10 = 0 Then mystr = mystr & Chr(10)
Next
Set shp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 1.5, 100, 1211.25, 1500)
DoEvents
MsgBox "作成されたテキストボックスに長い文字列を設定します"
Call txtput(shp, mystr)
End Sub
呼び出すだけで文字列の設定が出来ます（バグがなければ）。
よって、このtxtputというプロシジャーを呼び出すようにsupplementを少し 変更すると・・・・、
Sub supplement()
Dim txtNameA As String
Dim shp As Shape
Dim mystr As String
Dim cn As Long
Dim ii As Long
Dim AAA As Variant
Dim BBB As Variant
Range("A100").Select
Range(Selection, Selection.End(xlToRight)).Select
cn = Selection.Columns.Count
txtNameA = ActiveSheet.Name & "_TEXT_A" 'テキストボックスにつける名前
Set shp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 1.5, 600, 1211.25, 894#) '新規作成
shp.Name = txtNameA 'テキストボックスに名前をつける
For ii = 1 To cn
If Cells(101, ii).Formula <> "" Then
AAA = Cells(100, ii).Formula
BBB = Cells(101, ii).Formula
mystr = mystr & AAA & " : " & BBB & Chr(10) & Chr(10) '値のセット"
End If
Next
Call txtput(shp, mystr)
End Sub
一例ですが、このようになりました。
これで試してうまくいくようなら・・・・、
最後にツールであるtxtputは、テキストボックスに長い文字列を設定する機能です。
みーとんさんが提示されたコードを見る限りでは、出てきませんが、 今回の処理で要る・要らないに関わらず、長い文字列を設定したのですから、 読み込むツールもいつか必要になってくる なんてことは想像に難くないですよね？
これは、
function txtget(byval tbx as shape) as string ・ ・ end function
こんなインターフェースで取得できると便利そうではないですか？ この中は、考えてみてください。
Shapeを辿って取得できる Characters というオブジェクトをよく調べてみると できると思います。
ichinose
ぶらっとさん
過去ログ・Formula判定についてご教示ありがとうございました。 「セルに何かが入っていたら～」なんてよりも確実ですね。
ichinoseさん
考え方とどのように各プロシジャーを配置すべきか、のみならず、プロシジャーまでご提示いただき 大変助かりました。
書いていただいたtxtputプロシジャーの .Characters(g1, Len(pstr)).Text = pstr でオブジェクトの定義エラーになりました。これから細かく検証します。
取得したテキストの読み込みは、当初は Selection.Characters.Text = Selection.Characters.Text & AAA & " : " & BBB & Chr(10) & Chr(10) '値のセット で、Formulaを見つける度にテキストボックスへ書かせておりました。 （だから、225文字を超えたら書き込まれなかったのですね、、、 T_T
問題点と解決方法は教えていただいたので、あとは、あとは、、、がんばるぞー。
とり急ぎ、お礼とご連絡まで。
（みーとん）
ichinoseさん
txtputプロシジャーの
.Characters(g1, Len(pstr)).Text = pstr
~~~~~~~~~~~~~~~
~~の部分を削除すると、うまくいきました。
自宅PCでは、F1機能がバカになっていてHELPが参照できない部分があるため
職場で再度検証します。
また、ichinoseさんが最後におっしゃっていた「読み込むツール」について 誤解していました。すみません。 そうですね。ぎっちりつまっているテキストボックスの情報を取り出すことも十分 ありえますよね。 合わせて検証します。
（みーとん）
一つ修正もあったので
txtput を以下のように修正してください
'===================================================================
Sub txtput(ByVal tbx As Shape, ByVal txt As String)
'指定されたテキストボックスに文字列を設定する
'tbx 文字列を設定するテキストボックスオブジェクト
'txt テキストボックスに設定する文字列（２５５以上でも可）
Const lim As Long = 255
Dim g0 As Long
Dim g1 As Long
Dim g2 As Long
Dim pstr As String
g0 = Len(txt)
g1 = 1
With tbx.TextFrame
With .Characters
Do While Len(.Text) > 0
.Text = ""
Loop
End With
Do While g0 > 0
If g0 > lim Then
pstr = Mid(txt, g1, lim)
Else
pstr = Mid(txt, g1)
End If
.Characters(g1, Len(pstr)).Text = pstr
g0 = g0 - Len(pstr)
g1 = g1 + Len(pstr)
Loop
End With
End Sub
それと、前回投稿の smp1というプロシジャーを新規ブックにて 試してみてください。これは作動しますか？ >~~の部分を削除すると、うまくいきました。 ここを削除するとまずいとおもいます
一つずつ解決しましょう。
数字が全角文字で
１２３４５６７９０ １１１２１３１４１５１６１７１８１９２０ ２１２２２３２４２５２６２７２８２９３０ ・ ・ ９９１９９２９９３９９４９９５９９６９９７９９８９９９１０００
と表示されれば正常作動です。
Excel2002ではうまく作動しています。
ichinose
ichinoseさん
再検証、ありがとうございます。
新規Bookにコピーしても結果が同じでした。 そして、確かに削除したらダメな場所なんですが・・・ うー、なぜだ。 削除しても定数を上げて1回で表示される文字数にすると結果は同じになるため、 そうしちゃいたい誘惑と戦っています。
まずは、下記理解で合っているか、確認させてください。
1. smp1では、変数mystr(1-1000)を2バイト文字に変換・10文字ごとに改行することを指示。 新規テキストボックスを作成し変数shpとする。
2. smp1からtxtputプロシジャーの呼び出し その際、smp1で使用していた変数で獲得した値をそれぞれ shp=tbx / mystr=txt として引き渡す。
3. txtputプロシジャーでは、テキストボックスの文字列（あれば）を削除し 定数(lim)ごとにtxtを区切り、テキストボックスへ代入する。 （削除した箇所がなければ、最後の区切りのみが代入される）
自宅PCはExcel2007でした。慣れておらず使いにくいです。
> 一つずつ解決しましょう。 申し訳ありません。お言葉に甘えさせていただきます。暫しお付き合いをお願い致します。
（みーとん）
>新規Bookにコピーしても結果が同じでした。
VBAコードも同じ、扱う入力データも同じ、でも、 Excel2002では正常に作動し、Excel2007ではエラーになる。
この結果だけみれば、Excelバージョンの違いが原因の結果の違いか？ ということが頭に浮かびます。
あれ、当初の投稿では、 >Excel 2003 / Windows XP
と仰っていましたが、↑この環境ではいかがですか？
こうなると、私からの質問
上記で記述したように、私とみーとんさんで同期をとるために問題の コードは、smp1とtxtputという二つのプロシジャーに限定しましょう。
よって、実際には、smp1を実行した結果が正常に作動するか否かです。
みーとんさんが抱えているコードは最後にしましょう。
１ このsmp1がエラーになるという結果は、Excel2003,2007で同じ結果ですか？
２ みーとんさんの環境で作動するsmp1とtxtxputのコードを改めて全部投稿してください。
> txtputプロシジャーの >.Characters(g1, Len(pstr)).Text = pstr > ~~~~~~~~~~~~~~~ >~~の部分を削除すると、うまくいきました。
これは、 .Characters.Text = pstr ということですよね？
でも、↑このコードで作動する というのは、どうしても解せません。これだと、せっかく分割しても上書きですね！！
.Characters(g1).Text = pstr
私の環境でも↑これでは、正常に作動しました。
もしかしたら Excel2007では、小細工無しで 255以上も入力可能なのか？ それでも .Characters.Text = pstr これは、納得いきませんが・・・。
とりあえず、１，２の疑問点にお答えください。
私は、2003,2007を持っていないので、これらのバージョンでは検証できませんので ご了承ください。
ichinose
追加
txtputというプロシジャー内でTextframeからではなく、 旧図形オブジェクトからテキストを設定すると、どうでしょうか？
Sub txtput(ByVal tbx As Shape, ByVal txt As String)
'指定されたテキストボックスに文字列を設定する
'tbx 文字列を設定するテキストボックスオブジェクト
'txt テキストボックスに設定する文字列（２５５以上でも可）
Const lim As Long = 255
Dim g0 As Long
Dim g1 As Long
Dim g2 As Long
Dim pstr As String
g0 = Len(txt)
g1 = 1
With tbx.DrawingObject '古い図形オブジェクトからテキスト設定
With .Characters
Do While Len(.Text) > 0
.Text = ""
Loop
End With
Do While g0 > 0
If g0 > lim Then
pstr = Mid(txt, g1, lim)
Else
pstr = Mid(txt, g1)
End If
.Characters(g1, Len(pstr)).Text = pstr
g0 = g0 - Len(pstr)
g1 = g1 + Len(pstr)
Loop
End With
End Sub
↑これにtxtputを差し替えて、Excel2003、2007でsmp1を実行してみてください。
試せることは、全部試してみましょうよ。
ichinose
ichinoseさん
早朝より、痛み入ります・・・ また、自宅と職場の環境の違いをご連絡せずに申し訳ありませんでした。 職場：Excel 2003 / Windows XP 自宅：Excel 2007 / Windows VISTA
>１ このsmp1がエラーになるという結果は、Excel2003,2007で同じ結果ですか？ 職場環境では、ご提示いただいたコードで動きました！ ということは、2007だから？ それとも、私の自宅PCがおかしい・・・？
>２ みーとんさんの環境で作動するsmp1とtxtxputのコードを改めて全部投稿してください。
下記は、2007環境で一部削除で作動したコードです。
---------------------------------
Option Explicit
Sub txtput(ByVal tbx As Shape, ByVal txt As String)
'指定されたテキストボックスに文字列を設定する
'tbx 文字列を設定するテキストボックスオブジェクト
'txt テキストボックスに設定する文字列（２５５以上でも可）
Const lim As Long = 10000
Dim g0 As Long
Dim g1 As Long
Dim g2 As Long
Dim pstr As String
g0 = Len(txt)
g1 = 1
With tbx.TextFrame
With .Characters
Do While Len(.Text) > 0
.Text = ""
Loop
End With
Do While g0 > 0
If g0 > lim Then
pstr = Mid(txt, g1, lim)
Else
pstr = Mid(txt, g1)
End If
' .Characters.Text = pstr
.Characters(g1, Len(pstr)).Text = pstr
g0 = g0 - Len(pstr)
g1 = g1 + Len(pstr)
Loop
End With
End Sub
----------------------------------------------------------------------
Sub smp1()
Dim shp As Shape
Dim mystr As String
Dim g0 As Long
For g0 = 1 To 1000
mystr = mystr & StrConv(g0, vbWide)
If g0 Mod 10 = 0 Then mystr = mystr & Chr(10)
Next
Set shp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 1.5, 100, 1211.25, 1500)
DoEvents
MsgBox "作成されたテキストボックスに長い文字列を設定します"
Call txtput(shp, mystr)
End Sub
----------------------------------------------
環境の違いであれば、実際に使用するのは職場ですので、これにて解決となりました。 なんだか、しょぼい結末で申し訳ありません・・・・ 休日～早朝まで、本当にありがとうございました。
わ、追加が！！ そろそろ就業時間なので、後ほどご連絡いたします。
(みーとん)
私ももう出かけなければならないのですが、・・。
> Const lim As Long = 10000 ここも変更したのですね！！ それなら、2007で作動するのは納得です。 2007では、おそらく2003以前で問題になっていた事象は、解決しているのだと 思います（最終的な限界は、あるのでしょうが、意識しなくてもよいように 改善されているのでしょうね）。
>環境の違いであれば、実際に使用するのは職場ですので、これにて解決となりました。 いいえ、仕事が増えたと思いますよ！！ 最初の方の投稿で申し上げましたが、
txtput及び、txtgetというのは、汎用ツールです。 (txtgetも是非考えて投稿してください）。
2002でも2003でも2007でも本来は、作動するように作っておかなければなりません。 今のままだと、2003ではtxtputは、うまく作動するが2007ではエラーになる という現象は、何ら変わっていません。将来会社でExcelを入れ替えたとたんに このプログラムは作動しなくなってしまいます。
この場合、最後に投稿したtxtputの動作の結果待ちですが、 バージョンによって、コードを Excelのバージョンによって txtputの中身を分岐するというコードを書かなければなりません。
Excelのバージョン情報は、 Application.Version これで得られるのでこれを使って分岐する方法を考えてください。
ichinose
ichinoseさん
ハードルが高くなってます (^_^;
1. 旧図形オブジェクトからテキストを設定するプロシジャー（txtput2と呼ぶ）は 2003では問題なく動作しましたが、2007では同じ箇所でエラーになります。 「TextBoxクラスのCharactersプロパティを取得できません。」
2. .Characters(g1).Text = pstr も、2007ではエラーになりました。
3. 2007で「Application.Version」をF1で引こうとすると、HTMLのソース画面のようになり きちんとしたHELPになりません。 ためしに、 MsgBox Application.Version で得られた結果は「12.0」。 公称(2007)ではなく、ソフトウェアのversionだと推察しましたが、ということは 2003はver.9-10, 2007はver.11-12、、、のようになる可能性が？
Excelのバージョンごとに分岐するのが得策か、いずれのバージョンでも動くような 最大公約数を探ることが可能なのか。 とてもとても、私の力量では・・・（恐）
(みーとん)
Googleで application.version excel なんて検索文字を入力し検索させると、
先頭行に
http://officetanaka.net/excel/vba/tips/tips77.htm
こんなサイトが引っかかってきます（有名サイトです）。
ここでバージョンでの分岐をどのようにすればよいか説明しています。 ここを参考にしてみては？
ichinose
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.