1

アクティブセル領域の書式を文字列に統一する

1

2

3

4
5
6
7
8
9
10
11
12

13

Sub mystring()

Dim myrange As Range
Dim mycell As String

Worksheets(1).Range("A5").CurrentRegion.ShrinkToFit = True

For Each myrange In Worksheets(1).Range("A5").CurrentRegion
If VarType(myrange) = vbDate Then
mycell = myrange.Text
myrange.NumberFormatLocal = "@"
myrange = mycell
Else
myrange.NumberFormatLocal = "@"
End If
Next

End Sub

1
2
3
4

5
6
7
8
9
10
11
12
13

mystringという名のプロシージャを記述します。
変数を宣言します。
ワークシート(1)のセルA5を含むアクティブセル領域を選択し、「全体を縮小して配置する」に設定します。
ワークシート(1)のセルA5を含むアクティブセル領域を選択し、For Each・・・Nextステートメントで、範囲内の全てのセルをオブジェクト変数myrangeに順番に格納して、5～11の処理を繰り返します。
もし現在のmyrangeのデータ型が日付型だったら、
文字列型変数mycellにmyrange.Text（セルに表示されたままの値）を代入します。
myrangeのセルの書式をNumberFormatLocaプロパティで、文字列（@）に設定します。
myrangeにmycellの値（myrange.Text）を代入します。
myrangeのデータ型が日付型でなければ、
myrangeのセルの書式を、NumberFormatLocaプロパティで、文字列（@）に設定します。
5で開始したIfステートメントを終了します。
4に戻ります。
プロシージャの記述を終了します。

まずは全てのセルの書式を文字列型に統一します。
ワークシート上で操作する場合、A5:E15の範囲を選択し、ツールバーの「書式」→「セル」→「文字列」でOKをクリックするか、選択した範囲上で「右クリック」→「セルの書式設定」→「表示形式」→「文字列」でOKをクリックしても同じ結果になります。

下は実際に上の方法（手処理）でセルA5:E15を文字列に変換した結果になります。

【結果として】
A・Ｂ列はもともと文字列型なので、特に問題はありません。
C・D列は書式設定で修飾されたものが無くなり、入力した値そのままで表示されていますが、数値としては問題ありません。
E列は日付がシリアル値に変換されているので、メインフレームとのやり取りでは日付と分かる表示形式に設定した方がよいでしょう。
■シリアル値
エクセルでは日付はシリアル値という連続した値で管理されています。（例：2008年5月12日→39580）
Windows版Excelは、1900年日付システム（1900年1月1日を1とする。）Macintosh版Excelでは、1904年日付システム（1904年1月1日を1とする）が使用されています。

■VarType関数（変数の内部処理形式を表す値を返します）
VarType(varname)・・・引数 varname には、任意の変数を指定します。
Visual Basicでは、「xl」で始まるExcel VBAの組み込み定数とは別に、「vb」で始まる組み込み定数も多数用意されています。このVarType関数もその内の一つになります。
VarType関数の定数

定数	値	内容
vbEmpty	0	Empty 値 (未初期化)
vbNull	1	Null 値 (無効な値)
vbInteger	2	整数型
vbLong	3	長整数型 (Long)
vbSingle	4	単精度浮動小数点数型 (Single)
vbDouble	5	倍精度浮動小数点数型 (Double)
vbCurrency	6	通貨型 (Currency)
vbDate	7	日付型 (Date)
vbString	8	文字列型
vbObject	9	オートメーションオブジェクト
vbError	10	エラー型
vbBoolean	11	ブール型 (Boolean)
vbVariant	12	バリアント型 (Variant) (バリアント型配列にのみ使用)
vbDataObject	13	データアクセスオブジェクト
vbDecimal	14	10進型（Decimal）
vbByte	17	バイト型（Byte）
vbUserDefinedType	36	ユーザー定義型を含むバリアント型
vbArray	8192	配列 (Array)

※上のSub mystring() の 5 で　"If VarType(myrange) = vbDate Then" の箇所は"If VarType(myrange) = 7 Then"と書き換える事もできます。
そして、そのステートメントでデータ内に「Date型」があった場合、それを見たままの状態で文字列型に変換する内容になっています。↓
■プロシージャmystringを実行した結果

※プロシージャ「mystring」の3（.ShrinkToFit = True）で「全体を縮小して配置する」としているのは、同じくプロシージャの6（mycell = myrange.Text）でTextプロパティを使用している為です。Textプロパティはセルに表示されている通りの値を取得しますが、万一セル幅が短い場合に表示される、「#####」のような記号もそのまま取得してしまいますので、それを防止する為の措置です。尚、全てのセルも見たままの文字列型にしたい、という事でしたら、以下のようにプロシージャを変更して下さい。

Sub mystring_all()

Dim myrange As Range
Dim mycell As String

　Worksheets(1).Range("A5").CurrentRegion.ShrinkToFit = True
　　　For Each myrange In Worksheets(1).Range("A5").CurrentRegion
　　　　mycell = myrange.Text
　　　　myrange.NumberFormatLocal = "@"
　　　　myrange = mycell
　　　Next
End Sub

■プロシージャmystring_allを実行した結果※今回は使用しません。

▲Top

2

各列の最大バイト数を求める

1

2

3

4
5
6

7

8
9
10
11
12

13

14

15

16

Sub mymax()

Dim i As Integer, j As Integer, c As Integer, n As Integer

c = Range("A5").CurrentRegion.Columns.Count

For n = 1 To c
Cells(5, n).Select
j = 0

Do Until ActiveCell.Value = ""

i = LenB(StrConv(ActiveCell, vbFromUnicode))
If j <= i Then
j = i
End If
ActiveCell.Offset(1).Select

Loop

Cells(3, n) = j

Next n

End Sub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

mymaxという名のプロシージャを記述します。
変数を宣言します。
CurrentRegionプロパティを使い、セルA5を含むアクティブセル領域を選択し、その列数を変数cに代入します。
For ・・・Nextステートメントでカウンタ変数が1からc(この場合5)になるまで5～14の処理を繰り返します。
セル（5,n）を選択します。
変数 j を初期化します。
Do Until・・・Loopステートメントでアクティブセルが空白になるまで、8～12の処理を繰り返します。
変数 i にアクティブセルのバイト数を代入します。
もし、変数 j が変数 i の値以下だったら、
変数 j に変数 i の値（アクティブセルのバイト数）を代入します。
9で開始したIfステートメントを終了します。
Offsetプロパティで、アクティブセルを1つ下に移動します。
7に戻ります。
セル（3,n）にjの値を代入します。※3行目=その列の最大バイト数を表示する行。
4に戻ります。
プロシージャの記述を終了します。

各列を、For ・・・Nextステートメント（列方向）と、Do Until・・・Loopステートメント（行方向）の組み合わせで列の先頭から順番にLenB関数で文字列の長さを取得し、アクティブセルとその下のセルの値の大きい方を、変数 j に代入して行きます。結果的に最後に残った値をその列の3行目に代入します。（最大値）

■Len／LenB 関数（文字列の長さを取得する）

関数名	ワークシート	VBA
Len(String)	半角/全角の区別無く全ての文字を1文字分として計算	半角/全角の区別無く全ての文字を1文字分として計算
LenB(String)	半角文字を1バイト、全角文字を2バイトで計算	半角/全角ともに2バイトで計算

※Excel2000以降のVBAでは、文字列はUnicodeで処理される為、半角も全角も、1文字は2バイトで処理されてしまいます。
これを回避し、「半角文字を1バイト、全角文字を2バイト」で計算する為に、プロシージャ8の方法とっています。

■StrConv関数（文字の種類を変換する）
　　StrConv（String,Conversion,LCID）
　　String・・・変換する文字列
　　Conversion・・・変換の種類をVbStrConvクラスの定数から指定します
　　LCID・・・国別情識別子（LCID）を指定します。（省略可）
VbStrConvクラスの定数

定数	値	内容
vbUpperCase	1	文字列を大文字に変換
vbLowerCase	2	文字列を小文字に変換
vbProperCase	3	文字列の各単語の先頭の文字を大文字に変換
vbWide	4	文字列内の半角文字（1バイト）を全角文字（2バイト）に変換　※注1
vbNarrow	8	文字列内の全角文字（2バイト）を半角文字（1バイト）に変換　※注1
vbKatakana	16	文字列内のひらがなをカタカナに変換　※注2
vbHiragana	32	文字列内のカタカナをひらがなに変換　※注2
vbUnicode	64	システム規定のコードページを使って文字列をUnicodeに変換
vbFromUnicode	128	文字列をUnicodeからシステム規定のコードページに変換

※注1：国別情報の設定が中国、韓国、日本の場合に適用
※注2：国別情報の設定が日本の場合に適用

■Evaluate関数を使い文字列数を取得する
Evaluate関数は、式（計算式として評価されるフィールド値を含む）を評価しますので、計算式を文字列で書いて、その演算を行うことも出来ます。
例として、プロシージャ「mymax」の8のステートメントを以下のように書き換えることもできます。
i = Evaluate("LenB(""" & ActiveCell & """)")

■プロシージャmymaxを実行した結果

▲Top

3

指定された文字数にする（固定長化）

1

2

3

4
5
6

7

8
9
10
11
12
13
14

15
16

17

18

Sub myfixed()

Dim j As Integer, c As Integer, n As Integer, s As Integer
Dim mycnt As Integer

c = Range("A5").CurrentRegion.Columns.Count

For n = 1 To c
j = Cells(1, n).Value
s = Cells(2, n).Value

Cells(5, n).Select

Do Until ActiveCell.Value = ""
mycnt = j - LenB(StrConv(ActiveCell, vbFromUnicode))
If s <> 1 Then
ActiveCell.Value = ActiveCell.Value & Space(mycnt)
Else
ActiveCell.Value = Space(mycnt) & ActiveCell.Value
End If

ActiveCell.Offset(1).Select
Loop

Next n

End Sub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

myfixedという名のプロシージャを記述します。
変数を宣言します。
CurrentRegionプロパティを使い、セルA5を含むアクティブセル領域を選択し、その列数を変数cに代入します。
For ・・・Nextステートメントでカウンタ変数が1からc(この場合5)になるまで4～18の処理を繰り返します。
変数 j にセル1,nの値を代入します。
変数 s にセル2,nの値を代入します。
セル5,nを選択します。
Do Until・・・Loopステートメントでアクティブセルが空白になるまで、8～17の処理を繰り返します。
変数 mycnt に変数 j からアクティブセルのバイト数を引いた数を代入します。
もし、変数 s が1でなければ、
アクティブセルは、変数mycntの分だけスペースを後ろに追加します。
変数 s が1ならば、
アクティブセルは、変数mycntの分だけスペースを前に追加します。
10で開始したIｆステートメントを終了します。
Offsetプロパティで、アクティブセルを1つ下に移動します。
8に戻ります。
4に戻ります。
プロシージャの記述を終了します。

2、で取得した各列の最大バイト数（3行目）を参考に、最終的に何バイトにするのか決めます。
今回はA～Eの各列を各々10・14・6・12・18バイトの計60バイトに設定します。
実際にプロシージャを実行する前に、1行目に希望するバイト数を入力し、必要があれば2行目に「1」を入力する事により、スペースを前後どちらに入れるか選択できます。

■Space関数（指定した数だけスペースを追加する）
Space（Number）
Number・・・追加するスペースの数を指定します。

■プロシージャmyfixedを実行した結果

F列は5行目から15行目までのA～E列を、Lenb関数でバイト数を算出し、それぞれを合計しました。
全て10+14+6+12+18＝60バイトで揃っています。

▲Top

4

テキストファイルに書き出す

1

2

3
4

5

6
7

8
9

10
11
12

13

14

15

Sub myoutput()

Dim c As Integer, j As Integer
Dim i As Long, n As Long
Dim myr As String, str As String

c = Range("A5").CurrentRegion.Columns.Count
n = Range("A65536").End(xlUp).Row

Open "c:\data\kotei.txt" For Output As #1

For i = 5 To n
For j = 1 To c

myr = Cells(i, j)
str = str & myr

Next j
Print #1, str
str = ""

Next i

Close #1

End Sub

1
2
3
4

5
6
7
8
9
10
11

12
13
14

15

myoutputという名のプロシージャを記述します。
変数を宣言します。
CurrentRegionプロパティを使い、セルA5を含むアクティブセル領域を選択し、その列数を変数cに代入します。
Endプロパティを使い最下行（A列65536行目）を基準として、そこからデータの入力されている上端のセルを参照しその、行番号を変数nに代入します。（この場合15）
Openステートメントを使いテキストファイル（c:\data\kotei.txt）を出力モードで開き、ファイル番号1を割り当てます。
カウンタ変数 i が5から n になるまで、7～12の処理を繰り返します。
カウンタ変数 j が1から c になるまで、8・9の処理を繰り返します。
文字型変数myrに、セル( i , j ) の値を代入します。（最初はセル(5,1)の"商品番号"）
文字型変数strに、str & myr の連結した文字列を代入していきます。
7に戻ります。
Print #ステートメントを使い、5のOpenステートメントでOutput（出力モード）で開かれたテキストファイルにワークシートの内容を行単位で書き込みます。
変数strを初期化します。
6行目に戻ります。
Closeステートメントを使い、Openステートメントで開いたファイルの入出力を終了し、ファイルを閉じ、ファイル番号を開放します。
プロシージャの記述を終了します。

3、で固定長化したワークシート上のデータを、Cドライブのdataフォルダにあるkotei.txtファイルとして出力します。
■Openステートメント（テキストファイルをパソコン内部で開く）
Open Pathname For Mode As #Filenumber
　Pathname・・・開きたいテキストファイルのファイル名を指定します。ドライブ名、フォルダ名を含めて指定できます。
　Mode・・・開いたファイルの処理内容をキーワードによって指定します。

キーワード	処理内容
Append	追加モード
Binary	バイナリモード
Input	入力モード
Output	出力モード
Random	ランダムアクセスモード[規定値]

#Filenumber・・・開いたファイルに番号を割り当てます。1～511の値を指定します。

■Printステートメント（ワークシートの内容を文書形式でテキストファイルに書き込む）
Print #Filelist, Outputlist
　Filelist・・・Openステートメントで割り当てたファイル番号を指定します。
　Outputlist・・・テキストファイルに書き込む内容が入力されているセルを指定します。
※上のプロシージャの8・9は、変数myrに"＆（アンド）"演算子で列方向（この場合1～5列）に連結されたセルの値を代入し、完了したら、Printステートメントでテキストファイルに書き込みます。その後、変数myrを初期化して、6に戻る内容になっています。
一番最初はセル（5、1）（5、2）（5、3）（5、4）（5、5）の「商品番号商品名単価Test Data 日付」になります。

■Closeステートメント（パソコン内部に開いたテキストファイルを閉じる）
Close #Filenumber
　Filenumber・・・Openステートメントで割り当てたファイル番号を指定します。

■#Filenumber
Filenumberを指定する時、使用中の番号を重複して指定するとエラーになります。その場合、最初から番号を指定せず、番号をFreeFile関数で取得すれば、コンピュータ側で現在使用できる番号を返してきます。
変数（例えばmyfno）を整数型（Integer）で宣言し、Openステートメントの前にmyfno = FreeFile のステートメントを追加します。そして、現在記述されている、#1の部分を #myfno に変更すればOKです。

■実際にkotei.txtに書き出したファイルをメモ帳を使って開いてみました。