Excelの新関数には便利なものがたくさんありますが、
- XLOOKUPはExcel2021以降のみ使用可能
- TEXTAFTER/BEFOREはExcel2024以降のみ使用可能
などバージョンによって制限があり、
使えない関数があるファイルを開くと#NAME?エラーになってしまいます。
一応エクセルの基本機能として「互換性チェック」の機能がありますが、
どの関数が該当しているかがわからないなど、痒い所に手が届きません。
この互換性エラーを365ユーザー側で検知するための、
使用している新関数リストを出力するマクロを紹介します。
実行型の便利マクロですので、
Excel起動時に裏で開かれる「個人用マクロブック」などに搭載して使ってください。
ショートカットキーに登録したり、ツールバーやリボンにボタン配置すると便利です。
マクロの仕様
検証したいブックを選択(Active)にした状態でこのマクロを実行すると、
このようなリストが新規ブックに出力されます。
※ 同一数式は一番上のセルだけを出力する仕様になっています。
アドレス欄に元ブックへのハイパーリンクを設置してありますので、
実際の数式を修正に行くときにご活用ください。
ソースコード
Option Explicit ' 出力シートの列アドレス Private Enum CNo新関数リスト シート名 = 1 セルアドレス 関数 必要Ver 数式 End Enum ' 新関数リストの出力 Sub バージョン別の新関数使用セルリストを作成する() ' アクティブブックに実行 Dim wb対象ブック As Workbook Set wb対象ブック = ActiveWorkbook ' 関数リストは新規ブックに出力 Dim ws出力シート As Worksheet Set ws出力シート = Workbooks.Add.Worksheets(1) ' 新関数リスト（Key:関数名/Item:必要Ver） Dim Dic新関数 As Dictionary Set Dic新関数 = GetDictionary新関数リスト ' 対象ブックの全シートのうち数式のあるセルのみを走査 Dim R_出力 As Long: R_出力 = 4 Dim ws As Worksheet For Each ws In wb対象ブック.Worksheets ' 行→列の順にループ(同一数式スキップ用) Dim R As Long, C As Long For C = 1 To ws.UsedRange.Columns.Count + ws.UsedRange.Column - 1 For R = 1 To ws.UsedRange.Rows.Count + ws.UsedRange.Row - 1 Dim 検証セル As Range: Set 検証セル = ws.Cells(R, C) If 検証セル.HasFormula Then ' ひとつ前(上)のセルと同一数式の場合は書き出さない Dim ひとつ前のR1C1数式 As String If 検証セル.FormulaR1C1 <> ひとつ前のR1C1数式 Then Dim 検証数式 As String: 検証数式 = UCase(検証セル.Formula) ' すべての新関数をループ Dim 新関数 For Each 新関数 In Dic新関数 ' 新関数があればセルに書き出し If InStr(検証数式, 新関数) > 0 Then ws出力シート.Cells(R_出力, CNo新関数リスト.シート名) = ws.Name ws出力シート.Cells(R_出力, CNo新関数リスト.セルアドレス) = 検証セル.Address(0, 0) ws出力シート.Cells(R_出力, CNo新関数リスト.関数) = 新関数 ws出力シート.Cells(R_出力, CNo新関数リスト.必要Ver) = Dic新関数(新関数) ws出力シート.Cells(R_出力, CNo新関数リスト.数式) = Mid(検証数式, 2) ' ハイパーリンクの設置（検証ブック未保存要にエラースキップ On Error Resume Next ws出力シート.Cells(R_出力, CNo新関数リスト.セルアドレス).Hyperlinks.Add _ Anchor:=ws出力シート.Cells(R_出力, CNo新関数リスト.セルアドレス), _ Address:=wb対象ブック.FullName, _ SubAddress:=検証セル.Address(0, 0, external:=True), _ TextToDisplay:=検証セル.Address(0, 0) On Error GoTo 0 ひとつ前のR1C1数式 = 検証セル.FormulaR1C1 R_出力 = R_出力 + 1 End If Next ' すべての新関数をループ End If ' ひとつ前(上)のセルと同一数式の場合は書き出さない End If Next Next Next ' 対象ブックの全シートのうち数式のあるセルのみを走査 ' 出力シートの見出しと書式の設定 With ws出力シート .Name = "新関数リスト" .Range("A3:E3").Value = Split("シート名,アドレス,関数名,必要Ver,数式", ",") .Columns(CNo新関数リスト.セルアドレス).HorizontalAlignment = xlCenter .Columns(CNo新関数リスト.必要Ver).HorizontalAlignment = xlCenter .Rows(3).HorizontalAlignment = xlCenter .Rows(3).VerticalAlignment = xlTop .Rows(3).RowHeight = 27 .UsedRange.EntireColumn.AutoFit .Rows(3).AutoFilter .Range("A1").Value = "検証ブック名" .Range("B1").Value = wb対象ブック.Name .Range("B1").HorizontalAlignment = xlLeft ws出力シート.Activate .Range("A4").Select ActiveWindow.FreezePanes = True End With End Sub ' 新関数リスト（Key:関数名/Item:必要Ver） Function GetDictionary新関数リスト() As Dictionary Static Dic新関数リスト As Dictionary If Dic新関数リスト Is Nothing Then Set Dic新関数リスト = New Dictionary Dic新関数リスト.Add "IFS", 2019 Dic新関数リスト.Add "SWITCH", 2019 Dic新関数リスト.Add "TEXTJOIN", 2019 Dic新関数リスト.Add "CONCAT", 2019 Dic新関数リスト.Add "MINIFS", 2019 Dic新関数リスト.Add "MAXIFS", 2019 Dic新関数リスト.Add "XLOOKUP", 2021 Dic新関数リスト.Add "XMATCH", 2021 Dic新関数リスト.Add "FILTER", 2021 Dic新関数リスト.Add "LET", 2021 Dic新関数リスト.Add "SORT", 2021 Dic新関数リスト.Add "SORTBY", 2021 Dic新関数リスト.Add "UNIQUE", 2021 Dic新関数リスト.Add "RANDARRAY", 2021 Dic新関数リスト.Add "SEQUENCE", 2021 Dic新関数リスト.Add "ARRAYTOTEXT", 2021 Dic新関数リスト.Add "VALUETOTEXT", 2021 Dic新関数リスト.Add "VSTACK", 2024 Dic新関数リスト.Add "HSTACK", 2024 Dic新関数リスト.Add "TAKE", 2024 Dic新関数リスト.Add "DROP", 2024 Dic新関数リスト.Add "CHOOSEROWS", 2024 Dic新関数リスト.Add "CHOOSECOLS", 2024 Dic新関数リスト.Add "EXPAND", 2024 Dic新関数リスト.Add "TOCOL", 2024 Dic新関数リスト.Add "TOROW", 2024 Dic新関数リスト.Add "WRAPCOLS", 2024 Dic新関数リスト.Add "WRAPROWS", 2024 Dic新関数リスト.Add "TEXTAFTER", 2024 Dic新関数リスト.Add "TEXTBEFORE", 2024 Dic新関数リスト.Add "TEXTSPLIT", 2024 Dic新関数リスト.Add "LAMBDA", 2024 Dic新関数リスト.Add "ISOMITTED", 2024 Dic新関数リスト.Add "MAP", 2024 Dic新関数リスト.Add "REDUCE", 2024 Dic新関数リスト.Add "SCAN", 2024 Dic新関数リスト.Add "BYROW", 2024 Dic新関数リスト.Add "BYCOL", 2024 Dic新関数リスト.Add "MAKEARRAY", 2024 Dic新関数リスト.Add "IMAGE", 2024 Dic新関数リスト.Add "PY", 365 Dic新関数リスト.Add "GROUPEBY", 365 Dic新関数リスト.Add "PIVOTBY", 365 Dic新関数リスト.Add "PERCENTOF", 365 Dic新関数リスト.Add "TRANSLATE", 365 Dic新関数リスト.Add "DETECTLANGUAGE", 365 Dic新関数リスト.Add "REGEXREPLACE", 365 Dic新関数リスト.Add "REGEXTEXT", 365 Dic新関数リスト.Add "TRIMRANGE", 365 End If Set GetDictionary新関数リスト = Dic新関数リスト End Function
コードの解説
対象ブックのすべてのシート・セルをループして、
数式がある(.HasFormulaがTrueの)セルの内容を書き出すマクロです。
使っているコードは基本的なプロパティがほとんどのため、
コメントを読めば各処理の内容はつかめるかなと思います。
ロジックの根幹で使用する「新関数のリスト」は、
Keyに関数名、ItemにバージョンをいれたDictionaryを用いました。
2021でNGのものだけ表示したいなどマクロをカスタマイズする場合は、
このDictionaryの中身を編集してご利用ください。
なお、本マクロはDictionaryを使用しているマクロのため、
Scripting Runtimeの参照設定が必要です。
Scripting Runtimeの参照設定については、
下記の記事を参照ください。
www.limecode.jp
職場のPCのバージョンがバラバラだと、
数式の互換性管理が大変になります。
旧バージョンにファイルを送る機会が多い方は、
このマクロを活用して効率的に数式を検証して下さい。
