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


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
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入門
・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本目:テーブルの扱いと年齢計算


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

TOROW関数(配列を横1行の配列にして返す)|エクセル入門(2022-10-31)
TOCOL関数(配列を縦1列の配列にして返す)|エクセル入門(2022-10-31)
CHOOSECOLS関数(配列から複数の指定された列を返す)|エクセル入門(2022-10-29)
CHOOSEROWS関数(配列から複数の指定された行を返す)|エクセル入門(2022-10-29)
WorksheetFunctionの効率的な使い方とスピル新関数の利用|VBA入門(2022-10-27)
VSTACK関数(配列を縦方向に順に追加・結合)|エクセル入門(2022-10-25)
HSTACK関数(配列を横方向に順に追加・結合)|エクセル入門(2022-10-25)
LAMBDA以降の新関数の問題と解説(配列操作関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題集|エクセル入門(2022-10-24)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.並べ替え(Sort)|VBA入門
8.マクロって何?VBAって何?|VBA入門
9.エクセルVBAでのシート指定方法|VBA技術解説
10.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄




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


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



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