VBA100本ノック 46本目：名前定義に使える文字

公開日：2020-12-14　最終更新日：2021-02-22

VBA100本ノック 46本目：名前定義に使える文字

名前定義に使えない文字を変換して名前定義を設定する問題です。

ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。

マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。

出題

#VBA100本ノック 46本目
1行目の見出しをそのセルの名前定義(ブック範囲)に設定してください。
・記号や空白は"_"に置換
・先頭使用不可文字の場合は先頭に"_"を補う
エラー回避できない場合はイミディエイトに出力
※見出しは重複しないように入力
※既存の名前定義は無視して良い
※シートは任意

以下は見出しの参考データです。
取引先 (コード)
取引先（名称）
2020年:売上
＜消費税%＞
ABC123
⑳は20の丸付き
㉚は30の丸付き
𩹉と𩸽は魚です

ちょっと問題文が良くなかったですね。
下にデータ部をくっつけてしまったので分かりづらくしてしまいました。
1行目だけのデータで考えてください。
1行目のセル値を、1行目のそのセルの名前定義に設定するという事です。
手動で名前定義する時に自動変換されるものを自力実装するという事です。

画像を載せるときに、下が寂しいのでデータ部を追加したのが間違いでした。
完全に蛇足でした。

サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_46.xlsm
https://excel-ubara.com/vba100sample/VBA100_46.zip

VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。

他の人の回答および解説を見て、書いたVBAを見直してみましょう。

頂いた回答

出題ツイートの引用ツイートでの回答

解説

キーボードから入力できる記号はそんなに多くないので、列挙して置換可能です。
名前の先頭に使えない文字は、
・数字（全角も）
・Unicodeの一部（サロゲートペア以外にも使えないものがある）
先頭に使えない文字を限定するのは困難なので、エラーになったら先頭に"_"を付けています。


  
    
      Option Explicit


Sub VBA100_46_02()

　　Dim ws As Worksheet: Set ws = ActiveSheet

　　Dim i As Long, maxCol As Long

　　With ws

　　　　maxCol = ws.Cells(1, .Columns.Count).End(xlToLeft).Column

　　　　.Rows(1).Insert

　　　　.Rows(1).Copy Destination:=Rows(1)

　　　　For i = 1 To maxCol

　　　　　　If .Cells(1, i) <> "" Then

　　　　　　　　.Range(ws.Cells(1, i), .Cells(2, i)).CreateNames Top:=True

　　　　　　End If

　　　　Next

　　　　.Rows(1).Delete

　　End With

End Sub


Sub VBA100_46_01()

　　Dim ws As Worksheet: Set ws = ActiveSheet

　　Dim i As Long, isErr As Boolean

　　For i = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

　　　　If ws.Cells(1, i) <> "" Then

　　　　　　isErr = isErr Or addName(ws.Cells(1, i))

　　　　End If

　　Next

　　If isErr Then MsgBox "名前定義に登録できない名前がありました。"

End Sub


Function addName(ByVal aRng As Range) As Boolean

　　addName = False

　　On Error Resume Next

　　aRng.Name = createName(aRng)

　　If Err.Number = 0 Then Exit Function

　　Err.Clear

　　aRng.Name = "_" & createName(aRng)

　　If Err.Number = 0 Then Exit Function

　　Debug.Print aRng.Value & " → " & Err.Description

　　addName = True

End Function


Function createName(ByVal aStr As String) As String

      　　Const cSymbol = " !""#$%&'()*+,-./:;<=>?@[\]^`{|}~"

　　aStr = WorksheetFunction.Clean(aStr)

　　Dim i As Long

　　For i = 1 To Len(aStr)

　　　　If InStr(cSymbol, Mid(aStr, i, 1)) > 0 Or _

　　　　　 InStr(StrConv(cSymbol, vbWide), Mid(aStr, i, 1)) > 0 Then

　　　　　　Mid(aStr, i, 1) = "_"

　　　　End If

　　Next

　　If IsNumeric(Left(aStr, 1)) Then aStr = "_" & aStr

　　createName = aStr

      End Function

Clean関数で見えない文字を消しています。
今回の主題は、記号とは何か文字コードはどんなものがあるかという事になります。
名前定義を使ってこれらを認識してみましょうという問題でした。
記事補足にはエクセル機能の「選択範囲から作成」を使ったコードを掲載しました。

補足

簡単に文字コードを掲載します。

文字コーザ	文字
0	Null
1	制御文字印字されない
31	制御文字印字されない
32	スペース
33	!
34	"
35	#
36	$
37	%
38	&
39	'
40	(
41	)
42	*
43	+
44	,
45	-
46	.
47	/
48	0～9
57	0～9
58	:
59	;
60	<
61	=
62	>
63	?
64	@
65	A～Z
90	A～Z
91	[
92	\
93	]
94	^
95	_
96	`
97	a～z
122	a～z
123	{
124	\|
125	}
126	~

Unicodeで名前の先頭に使えない文字

サロゲートぺアは使えないようです。
その他、㉚も使えないのですが、使える文字も結構多くあり、どのような文字が使えないのかその決まりまでは私の方では把握できていません。

先のVBAではClean関数で制御文字を排除していますが、そのままでも名前定義の時点で削除されるようです。
また、セルアドレスと同じ名前になってしまうものも名前定義に使えません。
別途判定しても良いのですが、先のVBAではどうせエラー判定が必要なので、そこで一緒にやれば良いという事にしました。

エクセルには、名前定義を一括で作成する機能があります。

この機能を使ったVBAが以下になります。
この機能は複数範囲を選択して、範囲内の上下左右のいずれかのセル値を名前定義にするものです。

そこで、1行挿入して無理やり複数範囲にして、その後に余分な行を削除しています。


  
    
      Sub VBA100_46_02()

　　Dim ws As Worksheet

　　Set ws = ActiveSheet

　　With ws

　　　　.Rows(1).Insert

　　　　.Rows(2).Copy Destination:=Rows(1)

      　　　　.Range("A1").CurrentRegion.Resize(2).CreateNames Top:=True,
      Left:=False, Bottom:=False, Right:=False

　　　　.Rows(1).Delete

　　End With

End Sub

サイト内関連ページ

第92回．名前定義（Names）｜VBA入門

・Namesコレクション・Nameオブジェクト・RangeオブジェクトのNameプロパティ・名前定義を使ったRangeの書き方・シートコピー時は名前定義に注意・名前定義の実践例

マクロでShift_JIS文字コードか判定する

・文字コードについて・UnicodeにあってShift_JISにない文字の具体例・シート構成・配列数式での判定・Shift_JIS文字コードか判定するマクロVBA全コード・最後に

Byte配列と文字コード関数について

・文字列をByte配列に入れる・文字コードについて・文字列をByte配列に入れて、文字列に戻す・文字列をByte配列に入れて、自力で文字列に戻す・文字列をSJISに変換してからByte配列にいれて、自力で文字列に戻す・最後に

同じテーマ「VBA100本ノック」の記事

43本目：CSV出力
 44本目：全テーブル一覧作成
 45本目：テーブルに列追加
46本目：名前定義に使える文字
47本目：Window操作
 48本目：配列と数値型
 49本目：条件付き書式の判定
 50本目：トリボナッチ数列
 51本目：シート一覧と印刷ページ数
 52本目：複数シートの一括印刷
 53本目：テーブルの扱いと年齢計算

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
マクロVBA入門編
VBA100本ノック
46本目：名前定義に使える文字

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA練習問題
VBA100本ノック 46本目：名前定義に使える文字