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

VBAを100本の練習問題で鍛えます
最終更新日:2021-02-22

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


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


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

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。
マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。


出題

出題ツイートへのリンク

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

マクロ VBA 100本ノック


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


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


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

マクロ VBA 100本ノック


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


頂いた回答

解説

キーボードから入力できる記号はそんなに多くないので、列挙して置換可能です。
名前の先頭に使えない文字は、
・数字(全角も)
・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
58 :
59 ;
60 <
61 =
62 >
63 ?
64 @
65 A~Z
90
91 [
92 \
93 ]
94 ^
95 _
96 `
97 a~z
122
123 {
124 |
125 }
126 ~

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

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

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

マクロ VBA 100本ノック

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

マクロ VBA 100本ノック

そこで、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入門
名前定義をマクロVBAで扱う場合の解説になります、名前定義は、複数セル範囲や単一セルに対して名前を付けることで、そのセル範囲を参照する時に名前で参照できるようにするものです。名前で参照できることで、セル位置(行位置、列位置)を固定値で指定しなくて済むようになります。
マクロでShift_JIS文字コードか判定する
環境依存文字・機種依存文字をチェックしたいという話は時々聞くことなのですが、何をもってして判別するかという事が実はとても難しい問題になります、①②もIMEでは[環境依存]と表示されますが、通常これが問題になる事はないでしょう。そもそもコンピューターの中は2進数だけで表現されているもので、コンピューターの文字とは、
Byte配列と文字コード関数について
マクロVBAのデータ型にByte型がありますが、使う機会はかなり限られています。バイト型は、8ビット(1バイト)の変数で、0~255の範囲の単精度の正の数値が格納されます。1バイトで使う事もなくはありませんが、Byte型配列として使われることがしばしばあります。




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

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


新着記事NEW ・・・新着記事一覧を見る

PropertyのSetはLetでも良い|VBA技術解説(2021-03-31)
エクセル麻雀ミニゲーム|VBAサンプル集(2021-03-09)
VBA100本ノック 100本目:WEBから100本ノックのリストを取得|VBA練習問題(2021-03-03)
VBA100本ノック 魔球編:2桁の最小公倍数|VBA練習問題(2021-02-02)
Select Caseでの短絡評価(ショートサーキット)の使い方|VBA技術解説(2021-01-03)
VBA100本ノック 迷宮編:巡回セル問題|VBA練習問題(2020-12-31)
VBA100本ノック 魔球編:閉領域の塗り潰し|VBA練習問題(2020-12-16)
VBA100本ノック 魔球編:組み合わせ問題|VBA練習問題(2020-12-02)
将棋とプログラミングについて~そこには型がある~|エクセル雑感(2020-11-22)
VBA100本ノック 1本目:セルのコピー|VBA練習問題(2020-10-19)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Excelショートカットキー一覧|Excelリファレンス
6.繰り返し処理(For Next)|VBA入門
7.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
8.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
9.セルに文字を入れるとは(Range,Value)|VBA入門
10.とにかく書いてみよう(Sub,End Sub)|VBA入門




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


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



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