最終更新日:2021-08-27スピルでVBAの何が変わったかOffice365にスピルが登場し、2020年1月にはXLOOKUPもリリリースされ、ますますエクセルが便利になってきています。スピルは、これまでにないくらい大幅な機能変更と言えるでしょう。スピルの一般的な説明については以下を参照してください。・スピル関連記事スピルの登場によってVBAがどのように変わったのか、スピルを利用して何ができるのか、これらについて考えてみたいと思います。Rangeオブジェクトに追加されたプロパティセルに数式が設定されているかどうかは、HasFormulaプロパティで判定します。セルの数式が配列数式かどうかは、HasArrayプロパティで判定します。以下は、スピルを判定するために追加されたプロパティです。HasSpill範囲内のいずれかのセルがスピル範囲に含まれる場合は、Trueが返されます。ない場合(全てのセルがスピル範囲外の場合)は、Falseが返されます。単一セルのRangeオブジェクトで使用するようにしてください。複数セルのRangeオブジェクトでは、中のセルの一つでもひとつでもスピル範囲に含まれている場合はTrueとなってしまいます。Areasが複数存在するRangeオブジェクトの場合はエラーとなります。SpillingToRangeセルの数式がスピルする場合は、スピル範囲を返します。ない場合は、エラーが返されます。スピルする数式を入れたセルでのみTrueが返ります。スピルによってこぼれた先のセルではFalseとなります。SpillParentセルがスピルのメンバーである場合は、スピルする数式を入れたセルを返します。ない場合は、エラーが返されます。Formula2Formula2Formula2LocalFormula2R1C1Formula2R1C1Local以上の4つがあります。Formulaとの違いは、スピルするかどうかの違いです。Formulaはスピルしません、「共通部分の参照」となります。これに対して、Formula2はスピルします。SubSpillingToRangeがスピル数式をいれたセルしか取得できないので、SpillParentでスピル数式を入れたセルを取得してからSpillingToRangeで範囲を取得しています。セルに数式を設定する場合VBAで配列を返すような数式をセルに設定することはあまり無いとは思いますが、意図せずそうなってしまう事はあるかもしれません。複数の結果を返す数式でも、使用するプロパティによってスピルする場合とスピルしない場合があります。以下では、見比べやすいように全て"B1"に設定する場合のVBAになります。スピルする数式の入れ方Formula2を使う事でスピルします。"=RC[-1]:R[2]C[-1]"スピルしない数式の入れ方Formulaはスピルしません。"=A1:A3"当然ではありますが、暗黙的なインターセクション演算子@を付ければスピルしません。"=@A1:A3"このページでは、当初Value,Value2でもスピルするように書いていました。その後スピルしないことが確認できて修正しています。(@インターセクションが自動付与されます)当初の勘違いなのか仕様変更なのか、記憶があいまいになっており定かではありません。VBAで設定した後には必ず実際のセルを確認するはずですので、スピルしていればその時点で気が付くとは思います。万一にもスピルさせたくない場合は、Formulaプロパティを使うと良いでしょう。1次元配列を返すユーザー定義関数1次元配列を返す関数Functionをシートでユーザー定義関数として使用すればスピルします。Function配列のデータ型は特に問いません。Stringこのように宣言しても構いません。本来は引数を指定していろいろな機能を実現します。※旧(スピルしない)エクセルでは、配列数式{=USERSPILL1()}となります。2次元配列を返すユーザー定義関数2次元配列を返す関数Functionをシートでユーザー定義関数として使用すればスピルします。Function配列のデータ型は特に問いません。Stringこのように宣言しても構いません。本来は引数を指定していろいろな機能を実現します。※旧(スピルしない)エクセルでは、配列数式{=USERSPILL2()}となります。JAG配列を返すユーザー定義関数JAG配列(配列の中に配列)でも、要素数が一定であればスピルします。Function配列の要素数が不定数となる本来のギザギザJAG配列では#VALUEとなります。※旧(スピルしない)エクセルでは、配列数式{=USERSPILL3()}となります。スピルのVBAでの活用についてスピルは、永続版のExcelでサポートされるのは次期バージョンからになりそうです。Excel2019ではサポートされることは無さそうです。したがって、実務で気にせずに使えるようになるのはまだまだ先でしょう。いえ、Office365も大分普及しているようですので、Excelファイルを共有する範囲内のPCが全てOffice365になっているのであればスピルや新関数を使う事に問題はありません。使い方を工夫すれば、強力な武器になると思います。見るTRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)無効な前方参照か、コンパイルされていない種類への参照です。Case)|VBA入門9.メッセージボックス(MsgBox関数)|VBA入門10.マクロとは?VBAとは?VBAでできること|VBA入門ホームマクロVBA応用編マクロVBA技術解説スピルでVBAの何が変わったかこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。アクセスランキングサイトマップ事業案内お問い合わせホームマクロVBA応用編マクロVBA技術解説スピルでVBAの何が変わったかこのサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。