スピルのゴーストの範囲を選択するVBA
ツイッターで出題したVBAに関する問題です。
数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。
スピルについての詳細は以下を参照してください。
スピルについて
2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。
スピルのゴースト（あふれた先）の範囲だけを選択するVBAを作成するお題です。
出題ツイート
https://twitter.com/yamaoka_ss/status/1716718194067730828
指定シートの全セルを対象として、
スピルのゴースト（あふれた先）の範囲を選択するVBAを作成してください。
※ゴーストとはスピル数式を入れたセルは除きます
解答ツイート
スピルのゴーストの範囲を選択するVBAコード
使い道があるかは分かりませんが、一応関数にしておきました。
HasSpill
SpillingToRange
この2つのプロパティが分かればVBA自体は簡単だと思います。
スピルで追加されたプロパティについては以下を参照してください。
Function getGhost(ByVal ws As Worksheet) As Range
On Error Resume Next
Dim rngCal As Range
Set rngCal = ws.Cells.SpecialCells(xlCellTypeFormulas)
If rngCal Is Nothing Then Exit Function
On Error GoTo 0
Dim rng As Range, rngSpill As Range
Dim rngY As Range, rngX As Range
For Each rng In rngCal
Set rngY = Nothing: Set rngX = Nothing
If rng.HasSpill Then
Set rngSpill = rng.SpillingToRange
If rngSpill.Rows.Count > 1 Then
Set rngY = rngSpill.Resize(rngSpill.Rows.Count - 1).Offset(1)
End If
If rngSpill.Columns.Count > 1 Then
Set rngX = rngSpill.Resize(, rngSpill.Columns.Count - 1).Offset(, 1)
End If
Set getGhost = UnionRange(UnionRange(getGhost, rngY), rngX)
End If
Next
End Function
Function UnionRange(ByVal rng1 As Range, rng2 As Range) As Range
If Not rng1 Is Nothing And Not rng2 Is Nothing Then
Set UnionRange = Union(rng1, rng2): Exit Function
End If
If Not rng1 Is Nothing Then
Set UnionRange = rng1: Exit Function
End If
Set UnionRange = rng2: Exit Function
End Function
使い道があるかは分かりませんが、一応関数にしておきました。
SpillingToRange
この2つのプロパティが分かればVBA自体は簡単だと思います。
スピルで追加されたプロパティについては以下を参照してください。
スピルでVBAの何が変わったか
Office365にスピルが登場し、2020年1月にはXLOOKUPもリリリースされ、ますますエクセルが便利になってきています。スピルは、これまでにないくらい大幅な機能変更と言えるでしょう。スピルの一般的な説明については以下を参照してください。
使い方は、シートのオブジェクトを引数に指定するだけです。
上記VBAの要点としては、いかに速い処理を意識するかにあります。
SpecialCells(xlCellTypeFormulas)
これで数式のセルだけの参照で済むようにして、
HasSpill
これでスピル数式の判定、
SpillingToRange
これでスピル範囲を取得。
ここで、SpillingToRangeの範囲を1つずつ処理してしまうと処理時間がかかってしまいます。
例えば列全体にスピル数式が及んでいるような場合ですね。
そこで、SpillingToRangeの範囲を右および下にずらしてゴーストの範囲を取得しています。
右や下にずらす前には1つサイズを小さくする必要もありますね。
後はこれらの範囲をUnionしていけば、目的のゴースト範囲が取得できます。
Sub test()
Dim rng As Range
Set rng = getGhost(ActiveSheet)
If Not rng Is Nothing Then rng.Select
End Sub
SpecialCells(xlCellTypeFormulas)
これで数式のセルだけの参照で済むようにして、
HasSpill
これでスピル数式の判定、
SpillingToRange
これでスピル範囲を取得。
例えば列全体にスピル数式が及んでいるような場合ですね。
そこで、SpillingToRangeの範囲を右および下にずらしてゴーストの範囲を取得しています。
右や下にずらす前には1つサイズを小さくする必要もありますね。
後はこれらの範囲をUnionしていけば、目的のゴースト範囲が取得できます。
同じテーマ「ツイッター出題回答 」の記事
ツイッターで出したエクセルの問題です。誤字脱字は気を付けてもなかなか… 今回はエクセル関数で意図的に脱字を作ろうという問題です。関数は組み合わせかた次第です。出題ツイート 出題ツイートへのリンク 【エクセル問題】※関数は組み合わせだ編 脱字メーカー A列の文字列から1字消した全パターンを出力して下ください。
リーグ表に対戦番号を振るツイッターで出したエクセルの問題です。総当たりリーグ戦の表に試合番号を振る問題です。出題の主旨としては、これをスピル一発でやってみましょうと言うものです。出題ツイート ツイートが不調?で2回同じツイートをしました。
数列内の連続偶数のみ順序を入れ替えるツイッターで出題したエクセル数式問題です。数列があり、その中の偶数の連続部分だけを順番を入れ替えるという問題です。元は他のアカウントで出された問題なのですが、改めて私から出だししたものになります。出題ツイート 一応数式が作れたので出題します。
ランクによりボイントを付ける（同順位はポイントを分割）ツイッターで出題したエクセル数式問題です。点数一覧の順位に応じたポイントを付けます。同順位がある場合は、同順位でポイントを分割します。出題ツイート 【エクセル問題】 A列の点数の順位に応じたポイントをB列に付けます。
記号を繰り返してグラフ作成（10単位で折り返す）ツイッターで出題したエクセル数式問題です。記号を繰り返してグラフにする数式の紹介は良く見かけます。それでは、記号を10個単位で折り返してグラフ化する数式を作成してください。出題ツイート 【エクセル問題】 記号を繰り返してグラフにするのは良くありますよね。
Excel関数の引数を省略した場合についてツイッターで出題したエクセル数式問題です。引数を省略した時の挙動についての問題です。問題の回答選択肢が間違っていたりして申し訳ありません。引数の省略については説明することも少ないので、記録として残しておくことにしました。
シートコピー後のアクティブシートは何かツイッターで出題したVBAに関する選択問題です。シートコピーした後に、変数にActiveSheetを入れます。その変数に入っるシートは何かと言う問題です。つまり、シートコピー直後のActiveSheetは何か? と言う問題です。
ByRef引数の型が一致しません。ツイッターで出題したVBAに関する問題です。ちょっと変わったエラーメッセージに関する問題です。ただし今回は、出題というより勝手に連続ツイートで出題と解説をしたツイートの保存記録になります。出題ツイート https://twitter.com/yamaoka_ss/status/1704787902432264666…
シートのActiveXチェックボックスの指定方法ツイッターで出題したVBAに関する問題です。シートに挿入した、ActiveXのチェックボックスを標準モジュールから指定する場合のVBAの書き方を問う問題です。出題ツイート https://twitter.com/yamaoka_ss/status/1705130181965598906 【VBA問題】 シート（オブ…
列全体を指定する時のRangeとColumnsの違いツイッターで出題したVBAに関する問題です。列全体を指定する書き方に、Range Columns この2通りがあります。そこで、この2通りの書き方の違いは何かを問う選択問題です。出題ツイート https://twitter.com/yamaoka_ss/status/1705178543683174475 【VBA…
スピルのゴーストの範囲を選択するVBA新着記事NEW ・・・新着記事一覧を見る
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
累計を求める数式あれこれ｜エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE｜エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。｜エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。