VBA 変数に格納できる文字数に制限が？

質問

いつもお世話になります。

excel VBAで、エクセルの行を本文としたメールを作成するマクロを作りたいと考えています。
直接メーラーにデータを渡すプログラムは難しく、またサンダーバードなので対応しているか
分からないため、エクセル関数のハイパーリンクを使ってクリックでメーラーが立ち上がり
本文が入力されている...というものを作っています。

現在のコードは以下です。

’範囲内のセル本文を改行つきで「linktext」へ格納する
linktext = ""
Do Until Sheets("sheet2").Range("B" & cnt).Value = "adress"
linktext = linktext & "A" & cnt
linktext = linktext & "&"
linktext = linktext & """%0a"""
linktext = linktext & ","
cnt = cnt + 1
Loop

'ハイパーリンクとしてセルへセット
finaltext = "=HYPERLINK("""
finaltext = finaltext & "mailto:"""
finaltext = finaltext & "&"""
finaltext = finaltext & meruado
finaltext = finaltext & """&"
finaltext = finaltext & """?subject="""
finaltext = finaltext & "&"""
finaltext = finaltext & kenmei
finaltext = finaltext & """&"
finaltext = finaltext & """&body=""" & "&CONCATENATE(A12),"
finaltext = finaltext & linktext
finaltext = finaltext & "),"
finaltext = finaltext & """"
finaltext = finaltext & meruado
finaltext = finaltext & """" & ")"
Sheets("sheet2").Range("A1").Formula = finaltext

見よう見まねで作ったので、分割されていてすみません。
上記マクロを走らせ、できた関数は以下のようになります。
=HYPERLINK("mailto:"&"○○@hotmail.com"&"?subject="&""&"&body="&CONCATENATE(A10&"%0a",A11&"%0a",A12&"%0a",A13&"%0a",A14&"%0a"・・・以下続いていく)

で、ここで問題が。
上記の関数が非常に長くなった場合、変数「linktext」「finaltext」への格納が途中で切れてしまうようなのです。
具体的には、おしりが「A35」になるあたりで止まっており、lenで調べると、609で止まっているようです。

もっと多くの文字数を一つの変数に格納することはできますでしょうか？
また、さらにそれを使ってエクセルのセルにハイパーリンクをセットし、クリックして正しいメール本文が
立ち上がるようにすることはできるのでしょうか？

回答No.1

hogya · ベストアンサー

文字列型変数(String)に格納できるサイズは最大2GB（20億文字ぐらい）ぐらいですので6百いくつということはないです。
変数の定義に問題（固定長で宣言してあるとか）がなければ、プログラムのバグか元のデータのせいではないでしょうか。例えば、

Do Until Sheets("sheet2").Range("B" & cnt).Value = "adress"

の条件に該当して途中でループを抜けているとか。
あと、直接は関係ないですが、例え希望の文字数が入ったとしてハイパーリンクの文字数制限に引っかかったりしませんか？

お礼

ハイパーリンクの制限に引っかかりました...
別の方法を探しますが、教えていただいた事はそちらにも活かせそうです！

ありがとうございました。

回答No.2

MARU4812

とりあえず VB(VBA) の変数には問題なく5000文字入れられるテスト
Sub test()
　　Dim finaltext As String
　　Dim i As Long
　　For i = 0 To 5000 - 1
　　　　finaltext = finaltext & "a"
　　Next
　　MsgBox Len(finaltext)
End Sub

Excel の仕様の方に最大 256 文字とか 512 文字とかたくさん
あるので、Formula か HYPERLINK の制限に引っ掛かっているのだと
思われます。そういう長い文字列を使わなくても良いように工夫
しましょう。

お礼

ハイパーリンクの制限に引っかかりました...
別の方法を探しますが、教えていただいた事はそちらにも活かせそうです！

ありがとうございました。