安さ気ままに時には無料をめざし

​

エクセルで株価をリアルタイム監視していて、「上限を超えたら」「下限を割ったら」「取得単価を上回ったら」といったタイミングでWindowsの通知(トースト通知)を鳴らして知らせてくれる仕組みを、VBAだけで作ってみました。

株価のリアルタイム取得には、私の場合は楽天証券の「楽天RSS」を使っています。楽天RSSはExcelのセルに数式として株価を差し込んでくれるアドインで、証券会社が無料で提供しているツールとしては広く使われているものです。ただ、今回の通知の仕組み自体は楽天RSS専用というわけではなく、「セルの値がリアルタイムで自動更新される数式(RTD関数など)」であれば、他の証券会社のツールでも同じ考え方で応用できるはずです。

外部ツールやアドインを使わず、Excel VBA + Windows標準のPowerShellだけで完結させているので、同じように「エクセルで条件に合ったら通知したい」という方の参考になればと思い、作る過程で躓いたポイントも含めてまとめます。

全体の仕組み

やっていることはシンプルで、大きく3つのパーツに分かれています。

監視処理：シートの数値を定期的にチェックし、条件(上限超え・下限割れ・取得単価超えなど)に合致しているか判定する

通知処理：条件に合致したらWindowsの「トースト通知」(画面右下に出るポップアップ)を表示する

重複防止：一度通知したら再度同じ条件で通知が連発しないように状態を記憶する

なぜ「Windowsのトースト通知」なのか

Excelで通知を出す方法としては、MsgBoxでポップアップを出したり、セルの色を変えたり、Application.StatusBarにメッセージを表示したりと、VBA単体で完結する方法もいくつかあります。ただ、これらはすべてExcelの画面自体がフォアグラウンドに出ていないと気づけないという共通の弱点があります。他の作業をしていて別のウィンドウに切り替えていたり、Excelを最小化していたりすると、画面内に出ている通知には全く気づけません。

株価監視のように「いつ条件に合致するかわからないので、他の作業をしながら放置しておきたい」という使い方をする場合、これでは実用性がありませんでした。

そこで採用したのが、Windows標準のトースト通知(画面右下に出る、あのポップアップ)です。これはExcelのウィンドウではなくOS側の仕組みとして表示されるため、

Excelを最小化していても

他のアプリを全画面で使っていても

Excelがバックグラウンドにあっても

画面の右下にきちんとポップアップが出てきます。さらに音も鳴らせるので、画面を見ていない状態でも気づきやすくなります。この「Excelの表示状態に依存しない」という点が、MsgBoxなどのVBA標準の通知方法とは違う、今回の仕組みの一番のポイントです。

1. 通知を出す部分(トースト通知)

VBAには標準でWindowsのトースト通知を出す機能はないので、PowerShellを裏で呼び出して実現しています。

Option Explicit

' 監視銘柄の価格合致時の通知

Sub ShowToastNotification(ByVal Title As String, ByVal Message As String)

    Dim psContent As String

    Dim encodedCommand As String

    Dim psExePath As String

    psContent = "$AppId = '{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\WindowsPowerShell\v1.0\powershell.exe'" & vbCrLf & _

                "[Windows.UI.Notifications.ToastNotificationManager, Windows.UI.Notifications, ContentType = WindowsRuntime] | Out-Null" & vbCrLf & _

                "[Windows.UI.Notifications.ToastNotification, Windows.UI.Notifications, ContentType = WindowsRuntime] | Out-Null" & vbCrLf & _

                "[Windows.Data.Xml.Dom.XmlDocument, Windows.Data.Xml.Dom, ContentType = WindowsRuntime] | Out-Null" & vbCrLf & _

                "$template = @""" & vbCrLf & _

                "<toast duration='long'>" & vbCrLf & _

                "  <visual>" & vbCrLf & _

                "    <binding template='ToastGeneric'>" & vbCrLf & _

                "      <text>" & Title & "</text>" & vbCrLf & _

                "      <text>" & Message & "</text>" & vbCrLf & _

                "    </binding>" & vbCrLf & _

                "  </visual>" & vbCrLf & _

                "  <audio src='ms-winsoundevent:Notification.Looping.Alarm2' loop='false'/>" & vbCrLf & _

                "</toast>" & vbCrLf & _

                """@" & vbCrLf & _

                "$xml = New-Object Windows.Data.Xml.Dom.XmlDocument" & vbCrLf & _

                "$xml.LoadXml($template)" & vbCrLf & _

                "$toast = New-Object Windows.UI.Notifications.ToastNotification $xml" & vbCrLf & _

                "[Windows.UI.Notifications.ToastNotificationManager]::CreateToastNotifier($AppId).Show($toast)"

    encodedCommand = Base64EncodeUTF16LE(psContent)

    psExePath = Environ("WINDIR") & "\System32\WindowsPowerShell\v1.0\powershell.exe"

    Shell psExePath & " -WindowStyle Hidden -NoProfile -ExecutionPolicy Bypass -EncodedCommand " & encodedCommand, vbHide

End Sub

' 文字列をUTF-16LEのバイト列に変換してBase64エンコードする

' (PowerShellの -EncodedCommand は「UTF-16LE + Base64」形式が仕様のため必須)

Private Function Base64EncodeUTF16LE(ByVal sText As String) As String

    Dim oXML As Object

    Dim oNode As Object

    Set oXML = CreateObject("MSXML2.DOMDocument")

    Set oNode = oXML.createElement("b64")

    oNode.DataType = "bin.base64"

    oNode.nodeTypedValue = StrConvToBytes(sText)

    Base64EncodeUTF16LE = oNode.Text

    Set oNode = Nothing

    Set oXML = Nothing

End Function

' VBA文字列(内部的にUTF-16LE)を1文字ずつバイト配列に展開するヘルパー

Private Function StrConvToBytes(ByVal sText As String) As Byte()

    Dim i As Long

    Dim charCode As Long

    Dim bytes() As Byte

    ReDim bytes(0 To Len(sText) * 2 - 1)

    For i = 1 To Len(sText)

        charCode = AscW(Mid(sText, i, 1))

        If charCode < 0 Then charCode = charCode + 65536  ' サロゲートペア等の負数対策

        bytes((i - 1) * 2) = charCode And &HFF

        bytes((i - 1) * 2 + 1) = (charCode \ 256) And &HFF

    Next i

    StrConvToBytes = bytes

End Function

copy

ShowToastNotification("タイトル", "本文") という形で呼び出すだけで、Windows標準のトースト通知が表示されます。この部分は汎用パーツなので、どんな条件で呼び出しても使い回せます。

2. 監視処理(条件判定)

シート側では、Worksheet_Calculateイベントを使って、楽天RSSの関数で値が更新されるたびに全行をチェックする、というやり方にしています。

Option Explicit

' 行ごとの通知状態を保持する配列 (0=未通知, 1=上限超え通知済み, 2=下限割れ通知済み)

Private notifyState(2 To 400) As Integer

' 取得単価(J列)がG列を上回った通知の状態を保持する配列 (0=未通知, 1=通知済み)

Private notifyStateCost(2 To 400) As Integer

Private Sub Worksheet_Calculate()

    Call CheckAllRows

End Sub

' 手動でのセル書き換え(数式を介さない変更)を検知する

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim watchRange As Range

    Set watchRange = Application.Intersect(Target, Me.Range("G2:G400,J2:J400"))

    If Not watchRange Is Nothing Then

        Call CheckAllRows

    End If

End Sub

Private Sub CheckAllRows()

    Dim r As Long

    Dim currentVal As Variant

    Dim upperVal As Variant

    Dim lowerVal As Variant

    Dim costVal As Variant      ' J列 取得単価

    Dim baseVal As Variant      ' G列 比較対象値

    Dim code As String

    Dim name As String

    Dim msg As String

    For r = 2 To 400

        currentVal = Me.Cells(r, "E").Value  ' E列: 現在値

        upperVal = Me.Cells(r, "C").Value    ' C列: 上限

        lowerVal = Me.Cells(r, "D").Value    ' D列: 下限

        If IsEmpty(Me.Cells(r, "E").Value) Or IsEmpty(Me.Cells(r, "C").Value) Or IsEmpty(Me.Cells(r, "D").Value) Then

            GoTo ContinueLoop

        End If

        If IsError(currentVal) Or IsError(upperVal) Or IsError(lowerVal) Then

            GoTo ContinueLoop

        End If

        If Not IsNumeric(currentVal) Or Not IsNumeric(upperVal) Or Not IsNumeric(lowerVal) Then

            GoTo ContinueLoop

        End If

        If currentVal = 0 Then

            GoTo ContinueLoop

        End If

        code = CStr(Me.Cells(r, "A").Value)  ' A列: 銘柄コード

        name = CStr(Me.Cells(r, "B").Value)  ' B列: 銘柄名

        If currentVal > upperVal Then

            If notifyState(r) <> 1 Then

                msg = code & "  " & name & vbCrLf & "現在値: " & currentVal & " (上限 " & upperVal & " を超過)"

                Call ShowToastNotification("上限超過アラート", msg)

                notifyState(r) = 1

            End If

        ElseIf currentVal < lowerVal Then

            If notifyState(r) <> 2 Then

                msg = code & "  " & name & vbCrLf & "現在値: " & currentVal & " (下限 " & lowerVal & " を下回り)"

                Call ShowToastNotification("下限割れアラート", msg)

                notifyState(r) = 2

            End If

        Else

            notifyState(r) = 0  ' 範囲内に戻ったら通知状態をリセット

        End If

        ' 取得単価(J列)がG列の値を上回ったら通知

        costVal = Me.Cells(r, "J").Value

        baseVal = Me.Cells(r, "G").Value

        If IsError(costVal) Or IsError(baseVal) Then GoTo ContinueLoop

        If IsEmpty(Me.Cells(r, "J").Value) Or IsEmpty(Me.Cells(r, "G").Value) Then GoTo ContinueLoop

        If Not IsNumeric(costVal) Or Not IsNumeric(baseVal) Then GoTo ContinueLoop

        If costVal > baseVal Then

            If notifyStateCost(r) <> 1 Then

                msg = code & "  " & name & vbCrLf & "取得単価: " & costVal & " が基準値 " & baseVal & " を上回りました"

                Call ShowToastNotification("取得単価超過アラート", msg)

                notifyStateCost(r) = 1

            End If

        Else

            notifyStateCost(r) = 0

        End If

ContinueLoop:

    Next r

End Sub

copy

ポイントは、条件ごとに別々の状態管理配列(notifyState、notifyStateCost)を持たせていることです。こうすることで「上限超過」と「取得単価超過」が同じ行で同時に起きても、それぞれ独立してきちんと通知されます。

躓いたポイント・注意点まとめ

実際に動かしてみるまでわからなかった落とし穴がいくつもあったので、同じことをやろうとする方は事前に知っておくと時間の節約になるかと思います。

① Worksheet_Calculateは「数式の再計算」でしか発火しない

これが一番のハマりどころでした。株価のように楽天RSSの関数で自動更新される列はWorksheet_Calculateで検知できますが、セルに直接数値を手入力しただけでは発火しません。数式を介さないただの値の書き換えは「再計算」として扱われないためです。

手入力の列(取得単価など)も監視したい場合は、Worksheet_Changeイベントを別途用意し、Application.Intersectで対象範囲かどうかを判定する必要があります。

② 楽天RSSが返す#N/Aでループが丸ごと停止する

楽天RSSの関数は通信状況によって一時的に#N/Aのようなエラー値を返すことがあります。これを何も考えずに数値と比較すると「型が一致しません(実行時エラー13)」が発生し、その回のループ処理全体が止まって以降の行が一切判定されなくなるという厄介な症状が出ました。

対策は単純で、比較の前に必ずIsError()でエラー値かどうかを弾いてからIsNumeric()でチェックすることです。

If IsError(currentVal) Or IsError(upperVal) Or IsError(lowerVal) Then

    GoTo ContinueLoop

End If

If Not IsNumeric(currentVal) Or Not IsNumeric(upperVal) Or Not IsNumeric(lowerVal) Then

    GoTo ContinueLoop

End If

copy

③ Shell関数で「実行時エラー53(ファイルが見つかりません)」が出る

PowerShellをShell "powershell.exe ..."のように相対パスで呼び出すと、Excelのカレントディレクトリ(OneDriveの同期フォルダなど)の状態によっては実行ファイルが見つからずエラー53になることがありました。

Environ("WINDIR")でシステムディレクトリを取得し、フルパスで指定することで解消しました。

psExePath = Environ("WINDIR") & "\System32\WindowsPowerShell\v1.0\powershell.exe"

copy

④ PowerShellへのコマンド受け渡しはUTF-16LE + Base64が必須

トースト通知の中身(タイトルや本文)に日本語を含める場合、-EncodedCommandパラメータを使うのが安定していますが、これは仕様上「UTF-16LEでエンコードした文字列をBase64化したもの」しか受け付けません。単純にShift-JISやUTF-8でエンコードすると文字化けしたり実行に失敗したりします。

VBAの文字列は内部的にUTF-16LEなので、1文字ずつAscWで文字コードを取り出し、下位バイト・上位バイトの順にバイト配列へ詰め直すことで対応しました(サロゲートペアで負の値になるケースの補正も必要です)。

⑤ 通知の重複を防ぐには「状態」を記憶させる

Worksheet_Calculateは値が更新されるたびに何度も呼ばれるため、何も対策しないと条件を満たしている間ずっと通知が鳴り続けてしまいます。行ごとに「通知済みかどうか」を配列で保持し、

条件を満たした瞬間だけ通知して状態を1(または2)にする

条件から外れたら状態を0に戻す(次回また条件を満たしたら再通知できるようにする)

という形にすることで、「一度だけ通知→範囲に戻ったらリセット→また外れたら再通知」という自然な挙動になります。条件が複数ある場合は、条件ごとに別の状態配列を用意するのがおすすめです(1つの配列で使い回すと、片方の条件のリセットがもう片方の通知状態まで巻き込んで消してしまうことがあります)。

まとめ

Excel VBA単体でも、PowerShell経由でWindowsのネイティブなトースト通知を鳴らすことができる

MsgBoxなどのExcel内の通知と違い、最小化中やバックグラウンドでも画面右下に表示されるため気づき逃しにくい

リアルタイム更新される数式列はWorksheet_Calculate、手入力の列はWorksheet_Changeで監視する必要がある(発火条件が違う)

外部関数のエラー値(#N/Aなど)は必ずIsErrorで弾いてから数値比較する

通知の重複防止には行ごと・条件ごとの状態管理配列を用意する

同じように「エクセルで指定した値になったら知らせてほしい」という方の参考になれば幸いです。

この記事はExcel/VBAの実装メモを元にまとめたものです。実際にご自身の環境で使う際は、シート名・セル位置・列の意味などをご自身のブックに合わせて調整してください。

​


​
光触媒より便利な『CT触媒フェイクグリーン エクリア』（ 卓上 ミニ 消臭 空気清浄 防臭 防汚 抗菌 おしゃれ リアル インテリア ガラス瓶 多肉植物 寄せ植え トイレ 玄関 造花 人工観葉植物 置物 アーティフィシャルグリーン プレゼント おすすめ ) ​

​​​