vba autofilterに日付範囲を指定してみる。

日付でC列(3)をフィルターする場合、=(等しい)は表示形式の違いを吸収しないので、>=と<=1を使うのが良い。

autoFilter 3,">=2017/8/1",xlAnd,"<=2017/8/31

debian13+macbookpro11,3(retina Late2013)でbroadcomの無線lanをオフライン（internetに接続できない状態）で使用できるようした。

macbookpro11,3(Late2013)1にはUSBは２つしか挿せない。ひとつはDabian Linux live(non-free-firmware ありの trixieが必要だ)を挿す。そして、もう一つにはUSBマウスだ。broadcommの無線lanを動かすため幾つかのdebは、どうすれば良いのか？それは、DOS FATでフォーマットしたSDカードにDebian13のサイトから事前にロードしておくのが良い。

Debian13 Live(USB)を挿してoptionボタンを押しながら電源ボタンを押す。ジャーンと音楽が鳴ったら、EFI1でDebian13をブートする。 そして、eを押下し、bootのオプションとしてibt=offを指定し、ブートせよ。このパラメタの指定が必須です。これを指定しないと、sudo dmesg | grep wlで確認できるが、wlをロードした時にitb=offにせよというエラーが出て、wlのロードが失敗するのである。 以下が必要なパッケージだ。 cd /media/user/1*(mountでSDカードがマウントされたディレクトリを確認せよ)で、SDカードのディレクトリにいく。そして、必要なパッケージを順番に

sudo dpkg -iでインストールせよ。

1. broadcomm-sta-dkms_deb
2. pahole.deb
3. linux-kbuild*.deb
4. linux-headers*.deb

そして、wlのモジュールができたらsudo modprobe wlで無線lanのモジュールをロードせよ。 さすれば、nmcli devでそれらしい無線lanのデバイスが表示される。ここまでくるのにだいぶ、時間がかかった。ま、時間だけはたっぷりあるので、楽しいんだけどね。上手くできたときはね。linuxはいつもこうだ。 それから、いつものようにファンが回らず、本体が熱くなるので、mpfanも早めにインストールしたほうがいい。最近、アンディ・ウィアーの火星の人（上）を読んだ。原題はThe Martianだ。いいな。火星に取り残され次々起こる困難に襲われれるが諦めずに立ち向かうのがいいな。下も早く読まなきゃだ。
レンチンザワークラフト
大さじ1 酢　大さじ1 オリーブオイル　芥子　1mm
キャベツ2枚　2nmの千切り
刻んだロースハムのせる。軽くラップ
レンチン600w 1分

excelのPIVOT集計で計算の種類を累計にすると日付データがないところの累計が表示されない。それなら、VLOOKUPで表示されているところだけで済むようにすればいいじゃん。

excelのPIVOT集計で計算の種類を累計にすると日付データがないところの累計が表示されない。それなら、表示されているところだけで済むようにすればいいじゃん。PIVOTの参照したい日付をVLOOKUP(参照した日付,PIVOTの日付列,1,TRUE)とすればOKだ。これで、集計したい日のデータがなくても、集計したい日を超えない日（近似値）の累計が取得できる。マイクロソフトのバグがあってもあきらめないことが肝心である。

excelのPIVOT集計で計算の種類を累計にすると日付データがないところの累計が表示されない。累計と日付別の集計は止めてその日までの総計にすると良い。

excelのPIVOT集計で計算の種類を累計にすると日付データがないところの累計が表示されない。まず、計算の種類を累計とするのを止めて、計算なしに戻す。さらに、日付別の集計は止める。即ち、PIVOTの列からハズしておく。その代わりに集計したい日までの総計を表示する。これで、集計したい日のデータがなくても、集計したい日の以前のデータで総計が出るので、集計ができるようになる。ちなみに、PIVOTのオプションで「データがないアイテムを表示する」にすると、累計の表示が出てくることがあるが、例えば、２日連続で、データがないとデータが表示されない。これはマイクロソフトのバグかもしれないが、いずれにしても、PIVOTのオプションで「データがないアイテムを表示する」は、使えない。

accessクエリの必殺技：項目が全く同じテーブルをマージし、新テーブルを作るワンラインクエリ

項目が全く同じテーブルをマージし、新テーブルを作るワンラインクエリは、これだ。SQLビューにこのSQLをそのまま、貼り付けるだけでよい。データの重複を可とするため、UNION ALLで結合する。

select * into table3 from ( select * from table1 union all select * from table2 );

vbaでシートを特定列の内容で複数ファイルに分割するコードを書くときにとても大切なことがある。

それは、EXCELは複数の行を削除するのが苦手であるということだ。特に、非連続、即ち連続していない複数行を大量に削除する場合は、とても時間がかかる。どうすればよいのか？簡単である。飛び飛びにならないように、事前にソートすれば良い。即ち、並び替えしておけば良いのだ。大量の行をEXCELで扱うときの大原則になる。 例えば、シートに営業部署があると仮定する。シートを営業部署ごとに別のEXCELファイルにしたいというのは、よくあるケースだ。 基本的なロジックは、シートをAutoFilterでフィルターし、フィルターした可視セルのみ、新ブックへコピーするのが良い。 可視セルを使わない場合、フィルターした以外の部署を削除するという荒技もある。その荒技を使う場合、大量の行を削除することになる。大量の分散した行を消すと時間がかかる。 そういうケースでは、事前に部署名でソートしておくのだ。分散が解消され、削除がスルっとあっという間に完了してしまう。

vbaでピボットをフィルターするならタイムラインがいい

vbaでピボットに左上隅のフィルターフィールドの項目で日付によるフィルターをかけたところ、笑えるぐらいの遅さのため、断念した。左上隅のフィルターフィールドの項目をフィルターするには、ひとつひとつの項目を表示（項目.visible=True）するのか、非表示（false）とするのかの設定しなければならない。そして、このFalse或いはTrueを設定するのに恐ろしく時間がかかるためだ。一つやるのに時間がかかる。数秒かかる。数個の商品ぐらいならば、高速処理できるが、日付を複数、非表示にするといったケースでは全く使えない。本当に使えない。

    Dim i As Long
    Dim pt As PivotTable
    Dim pf As PivotField
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("商品")　　
        itemsToHide = Array("りんご", "そば", "ハンバーガー","ラーメン")
        pf.ClearAllFilters
        For i = LBound(itemsToHide) To UBound(itemsToHide)
            pf.PivotItems(itemsToHide(i)).Visible = False    'ItemsToHideの項目のみ非表示にする。
        Next i
    End With
    Set pt = Nothing

数多の試行錯誤で、辿り着いたのは、左上隅のフィルターでななく、タイムラインとPivotFilters.Add2による日付フィルターであった。こちらは、とても高速で処理してくれる。

Sub TestAddPivotFilter2()
    AddPivotFilter2("2026/01/01","2026/01/31")
End Sub
Sub AddPivotFilter2(sDate As String,eDate As String)
    Dim pt As PivotTable
    Dim pf As PivotField
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("日付")
        .ClearAllFilters 
        .PivotFilters.Add2 _
            Type:=xlDateBetween, _
            Value1:= sDate, Value2:= eDate
    End With
End Sub

あとは、ピボットテーブルの分析タブで、タイムラインを作成すればOKだ。sDate,eDateを変えるだけで自在に高速フィルターをかけることができる。 実は、空白の日付があると、それをフィルターすることができない。じゃ、どうするというと、例えば、遠い未来の日付にすることで、フィルターすることが可能となる。 空白の日付は、よく発生する。リアルワールドでは実にしばしば、発生する。何かの完了日とすれば、その処理が完了していないことを意味する空白の日付が誕生するわけだ。マイクロソフトではこの空白の日付がそのままでは、フィルターすることができない。ので、未来の日付にしておくわけだ。

vbaでPIVOTテーブルを操作すると、ピボットテーブルレポートの更新が完了するまでお待ちくださいと怒られた。さて、どうする？

　ピボットテーブルの更新がバックグラウンドで、勝手に行われている可能性がある。そこで、ピボットテーブルのプロパティを調べて、バックグラウンドで更新するというオプションにレ点（チェック）があったら、それをを外せば、勝手に更新されなくなるので、「更新が完了するまで待て」とは言われなくなるはずである。どうだろうか？

accessのテーブルの外部リンクを更新するにはリンクマネージャのポップアウト画面で、左下の「リンク先を更新するためのプロンプトを毎回表示する(A)のチェックボックスにチェック印を入れよ。

accessのテーブルの外部リンクを更新するにはリンクマネージャのポップアウト画面で、左下の「リンク先を更新するためのプロンプトを毎回表示する(A)のチェックボックスにチェック印を入れよ。

excel Cm+Bnという式でBn=空白の場合、式がerrorになる。SUM(Cm,Bn)にすればerrorにならず、Cmの値が取得できる。

excel Cm+Bnという式でBn=空白の場合、式がerrorになる。SUM(Cm,Bn)にすればerrorにならず、Cmの値が取得できる。 これを使えば、グラフを作るケースで重宝する。

macOSにはsqliteがデフォルトで入ってる。

macOSにはsqliteがデフォルトで入ってる。

vba debug のノウハウのあれこれ

• vbaのデバッグ・ウィンドウでの便利なコマンド
  • ?ActiveWorkBook.Name
  • ?ActiveSheet.Name
  • ?Sheets("Sheet_Name").CurrentReagion.address
• .copyすると、ActiveSheetが切り替わる。
• wb.closeするとひとつ前のActiveSheetになる。ちゃんと、スタックされている。
• 今どこシートがアクティブなのかを意識しないと、プログラムが暴走しだす。
• ちょいちょいDebug.printせよ。
• 「インデックスが有効な範囲にありません。」が出たら、シート名やブック名などのスペルが間違えている。スペルを確認せよ。
• 列の削除は、一遍にやる。Columns("A:A,C:C,E:E,F:J").Delete　そして、その前にフィルターは外しておけ。フィルターがかかっていると、怪奇現象を誘発する。
• 不要な列は非表示にしてコピペせよ。
• menuでの言い方　Excelブック(.xlsx) Excel97-2003ブック(.xls)
• vlookupでの列コピーは、application.vlookupを使う。参照シートをコピーし、VLOOKUP関数を埋め込むのではなく・・・なんでやろ？
• 「このブックには更新できないリンクが１つ以上含まれています。」というメッセージを抑止するにはopenする前にUpdateLinks:=Falseを指定する。openをApplication.DisplayAlerts = Falseとpplication.DisplayAlerts = trueで挟んでおくという手もある。

excel怪奇現象シリーズその１：「このブックには、ほかのデータソースへのリンクが含まれています。」でもどこで参照しているのか教えてくれない。

「このブックには、ほかのデータソースへのリンクが含まれています。」 でもどこで参照しているのか教えてくれない。 Cntl+Fキーで、ブック全体を［リンクの編集］ダイアログ ボックスの［リンク元］に書いてある文字列で検索しても該当のセルが出てこない。 先日、この怪奇現象に悩まされた。何時間も悩んでいたら、ふと、思い付いた。そうだこのセルには、リスト（いわゆるドロップダウンリストだ）を定義していたんだと。リストの定義で外部ファイルを参照していたのであった。 リストを定義した行を他のファイルからコピーすると、リスト（いわゆるドロップダウンリストだ）の定義もコピーされてしまい、そのリスト定義が外部ファイルの参照になるのだ。あーやれやれ。

excelは２万行を超えるとVLOOKUPがすべての結果を表示しなくなり、使いものにならなくなる。そうなったら、accessの出番。でもお金がかかるので、フリーのSQLのmysqlとかpostgreSQLかな。

excelのVLOOKUPをacccess,mysql,postgresqlでやるにはSELECT文で、テーブルAにはあるがテーブルBにはないものをwhere　B.フィールド名=Nullみたいな条件で抽出してやればよい。

vbaでツールを作る時に役に立つルール

• menuという名前のシートを作る：ツールが動作するためにパラメタ（入出力のフォルダまたはファイル、デバッグモードで走行するか否かといったオプション等々）を記述したり、マイフォルダやマイファイルの選択ボタンを配置できるようにしておく。
• マイフォルダボタンを作る：ボタンにはフォルダ選択というキャプションをつけておくと良い。このボタンをクリックすると、入力や出力のフォルダをmenuシート（GUI画面）で設定する形にできる。
• トレース機能を作る：モジュールの走行状況がわかるようなデバッグ情報を要所要所で採取しておく。エラーが起きた時に何があったのがわかるようにしておく。テキストファイルとして、ツールと同じフォルダに追書きモードで記録していく。トラブルが起きたら、参照し、その時点で不要なら削除する（クリア）。トレースファイルがなければ、新規に作るという構造にしておく。
• ステータスバーに進行状況を表示する：ステータスバーとは、EXCELの画面左隅の表示エリアである。ここにポイントとなる箇所で、ツールの進行状況を出力せよ。トレース情報を一部、出力するのもよいかもしれない。
  • Application.StatusBar = "表示する文字"
  • Application.StatusBar = False でリセットせよ。アプリ終了前に必ずにね。
• 処理終了時にmsgboxコマンドで処理終了というメッセージを出力すると良い。さらに、timeコマンドで開始時と終了時の時間差を所要時間として表示するのも良いだろう。

vbaの画面構成がグチャグチャに壊れたのを戻すにはどうしたいいのか

[vba画面]→[ツール]→[オプション]⇨[ドッキング]で、全てのウィンドウにチェックを入れるだけで良い。そして、表示された画面を自分好みの位置にする。

• イミデェートウィンドウ
• ローカルウィンドウ
• ウォッチウィンドウ
• プロジェクトエクスプローラ
• プロパティウィンドウ
• オブジェクトウィンドウ

米、再び姿を消したのか？令和７年は早いなと思っていたら・・

 　昨日、近くのスーパー万代に米を買いに行った。棚は空っぽ。買えなかった。今年は去年より米の姿がなくなるのが早かった。幸い、近くのスーパーたこ一で５Kgで6134円（税込）でゲットできたけど、気楽に買えない値段になっていた。近くの阪急オアシスも米の棚は空っぽだった。コスモスにはカルロース米が２つほど積んであった。日本は、関税交渉でトランプ大統領にあるだけ買うよと言えば良いのではと思う。後日、OASISを覗くと、４千円ぐらいの５kgの富山コシヒカリが沢山、あった。あるとこにはある。でも阪急にはなかったな。何が起きているんだろうか？そして、数日経った今日、阪急のお米屋を覗くと、米が並んでいた。米は姿を消していなかった。

vbaでチェック印（レ点）の文字があるかを判定しようとしたところ、？（環境依存文字）でるため、文字として表記できず、やむを得ず、16進数として判定して、回避したお話。

　チェック印（レ点）の文字は、EXCELのセルには書けるが、vbaのコードではとなり、記述できない。いわゆる、環境依存文字の一つのようだ。
　では、どうすれば、その文字を判定できるのか？
　一つの方法としては、チェック印（レ点）の文字は、16進数のコード2714(UNICODE)を判定すれば良いのではないかと考えた。

　If Hex(AscW(Range("A1").Value))=2714 then MsgBOX "GotIt"

ACCESSを起動してパラメタ /x "macro-name"で起動すると/xで指定したマクロを動かせるということに気がつくまでの話

　結論を先に言うと、ACCESSに/x　"マクロ名"という起動パラメタをつけて起動すると、ACCESSが立ち上がった直後に/xで指定したマクロを動かすことができる。
　例えば、バッチファイルで、start c:¥users¥username¥documents¥xxxxx.accdb /x "macro-name"でACCESSを起動すると/x "macro-name"で指定したマクロが動くわけだ。 　これは、Office 起動　スイッチでググり、ACCESSの起動スイッチの/xの説明に詳しく書いてある。
　ちなみにマクロ名をAutoExecにすると、/x AutoExecを付けなくてもACCESSを起動すると、AutoExecというマクロが動くのである。

ACCESSの起動スイッチ
　/x スイッチの前に指定した Access データベースを開いてから、指定したマクロを実行します。 データベースを開いたときにマクロを実行する別の方法は、AutoExec という名前のマクロを作成することです。
　マクロにはウイルスが含まれている可能性があるため、実行には注意が必要です。 実行する場合は、コンピューターで最新のウイルス対策ソフトウェアを実行する、セキュリティ センターを使用して、すべてのマクロ (デジタル署名されているものを除く) を無効にする、マクロの信頼できるソースのリストを保持するという予防策を講じてください。

　まず、マイクロソフトでは起動パラメタではなく、スイッチと呼んでいるので、ググりかたを「こむづかしく」している。なかなか先程のページに辿り着けない。
　どうすれば、ACCESSのマクロを起動時に呼び出せるのかが分かりにくいのだ。

スマホで見ると文字がはみ出していたhtmlをはみ出さないようにしてみた。preタグのstyleでoverflow: auto;を指定すればOKだ。

prettycodeを使わずにソースコードを格好良くしたのに、スマホで見ると文字がはみ出していたので、htmlを手直しして、はみ出さないようにしてみた。preタグのstyleでoverflow: auto;を指定すればOKだ。