Excel HYPERLINK関数
マクロを極力使わずにワークシート関数で勝負の仕事をやっていたので，関数つながりでもうひとつ。
日報みたいなものを共有機能を使ってやりたいという話。
最初のシート作成はマクロでやりますが，日々の運用ではマクロを使いません。
でも，３ヶ月分くらいの日報をだーーーーーっと一つのファイル内に作ります。どうやって，今日や明日というページをさくっと表示するか・・
と悩み，HYPERLINK関数を使いました。
=HYPERLINK("#" & TEXT(NOW()+1,"mmdd")&"!a1","明日のページ")
=HYPERLINK("#" & TEXT(NOW(),"mmdd")&"!a1","今日のページ")
各日付のシート名 ( 4/25なら 0425 )みたいなルールでシート名をマクロで付けています。
HYPERLLINK関数内の補足ですが，
・同一ブック内なので#をつけます。
・日付のシート名はNOWを利用し，それをTEXT関数で思った書式にすることで解消しています。
これだけでその日その日に応じたハイパーリンクになるようでした。マクロじゃなくてもちゃんと飛べるようになる。
実際運用してみないとわからないわけですが，少し試した感じではちゃんと動作しているみたい。
便利な関数だなぁ。
Excel Row関数とColumn関数とVLOOKUP
仕事で，今までなんで思いつかなかったのか，びっくりしたことがありました。
ほんとに あれぇ?という感じ。
時間はないので手短に書きますが，
VLoopup関数はとても便利で，VBAでやらないときはとても感謝する関数ですが，その列を与えるときに直接値を与えてしまうため，数式のコピーがうまくできなくて，いつももやっとしていました。
第３引数の列のところです。直接値を入れてしまうもんだから，横にコピーしても数値をいちいちどうにかして変えないといけないんですよね。
今まではどこかにその列情報を書いておいてそこを参照するとか，置換を使うとかしか思いつかずに，VLOOKUPはとても便利だけどここだけはめんどくさいよなぁと思っていました。
なんででしょう，今日はふっとColumnと打ち込んでみたくなって(VBAのせいとしか思えないけど)打ち込んでみたら
普通にそれらしきワークシート関数がありまして，
期待通りの答えを返します。
=VLOOKUP($G$12,$A$1:$C$15,COLUMN(B1),FALSE)
Columnの中身を相対参照にしておけば，コピペと一緒にきちんとずれていってくれますし，足したり引いたりしてずらすとか，掛け算等を併用すればどうにでも値は操作できる。
なんでこんな簡単なこと思いつかなかったのか，愕然としつつ，これでだいぶ楽になるやんとラッキーな気分です。
ちなみにROW関数もあるので， ワークシートの INDEX関数やMATCH関数，OFFSET関数と併用してやれば大概のことはやれますね。
今まで直打ちしていた列や行の引数もコピペに対応させれるように書けますね。
とても地味で小さな発見でしたが，今後とても楽になるなーと思う出会いでした（ΦωΦ）
ちなみに引数を省略すれば，そのセルの行や列を返してくれるようです。これも応用しがいのある挙動。
もっと早く知っておくべきでした(;´▽｀A``
Powerpoint VBA パワーポイントで元素記号タイピング
元素記号を２０番目まで覚える。
これは，とても頭が痛い悩みなんですが，化学を学ぶ上でどうしても避けるわけにはいかない内容です。
自分が高校生の頃よりは少しましなのは，
www.youtube.com
以前NHKでエレメントハンターという番組がありました。そのテーマになっているこの曲があるので導入はしやすい。
でも，とにかく覚えないといけない，という現実は変わりません。
どうしようかなぁ，自分にできる手立てはないかなぁと思い，思いついたのはタイピング。
まだまだプロトタイプですが，ちょっと作ってみました。
時間を測定してタイムアタックをするとかしないと何度もやる気は起きません。
タイムアタック機能を作れば，一部の子にはたぶん闘争心が生まれますので，一部の子には役に立つものになる気がしますし，
単純にタイピングの訓練の場が今の高校生には不足していますので，その一つとしても意義が無くもない。
はたまた別のアプローチとして３拓問題を作って，何度も繰り返すことで定着を図るとか，いろんな手はありますね。。
やっぱブログにネタとして書こうとすると，書きながらいろいろなアイディアが出ます。
なんとか今年も書かないと!
時間を作るよう努力しようと思っています。
コード
Side1 テキストボックスのChangeイベントを使います。
Option Explicit Private Sub TextBox1_Change() Call 元素チェック End Sub
標準モジュール
Option Explicit Public 原子番号 As Long Public 元素(1 To 20) As String, 位置(1 To 20) As String Public 元素記号表 As Table Const TypingText As String = "H_11,He_18,Li_21,Be_22,B_23,C_24,N_25,O_26" & _ ",F_27,Ne_28,Na_31,Mg_32,Al_33,Si_34,P_35,S_36,Cl_37,Ar_38,K_41,Ca_42" Sub 元素記号タイピング() Dim TargetSlide As Slide: Set TargetSlide = ActivePresentation.Slides(1) Set 元素記号表 = TargetSlide.Shapes("元素記号表").Table Dim i As Long For i = 1 To 20 元素(i) = Split(Split(TypingText, ",")(i - 1), "_")(0) 位置(i) = Split(Split(TypingText, ",")(i - 1), "_")(1) Next Slide1.TextBox1.Text = "" 原子番号 = 1 Dim c As Cell For i = 1 To 4 For Each c In 元素記号表.Rows(i).Cells c.Shape.Fill.Visible = msoFalse Next Next End Sub Sub 元素チェック() If 原子番号 = 0 Or 原子番号 = 21 Then Call 元素記号タイピング Exit Sub End If Dim 元素長さ As Long 元素長さ = Len(元素(原子番号)) Dim 行 As Long, 列 As Long 行 = CLng(Left(位置(原子番号), 1)) 列 = CLng(Right(位置(原子番号), 1)) If Right(Slide1.TextBox1.Text, 元素長さ) = 元素(原子番号) Then 元素記号表.Cell(行, 列).Shape.Fill.ForeColor.RGB = vbYellow 原子番号 = 原子番号 + 1 End If If 原子番号 = 21 Then MsgBox ("おつかれさま!") End If End Sub
動画
まぁ今の段階では，極めてまじめでやる気がある子しかやってくれないものなので，もちょいシステム側をきちんとしないといけません。
時間を計る機能は絶対(シツコイデスネ)
いろいろと腑に落ちないことと，やり方がわからないこと，改善すべきことがあります。
一つ目は ActiveXのテキストボックスにフォーカスを自動的に持っていく方法がわかりません。あるのかな。。?これができないとちょいつらい
二つ目は 今は元素記号が大文字１文字か，大文字と小文字のセットだよというのにこだわり，きちんと大文字にしないと認識しませんが，これだとめんどい。
シフト押す回数が多く，タイムアタックさせるとするなら，やる気をなくす要素になります。これはLike演算子で比較させて小文字でもなんとかなるようにすべきかなぁ。
三つめは腑に落ちないこと。タイピングスタートのボタンを押してもリセット処理がされないことがあります。なんでかよくわかりません。
四つ目は時間をどうやろうかなぁですね。タイマー作ったときにやったAPIのSetTimerとか持ってこないといけないかなぁと思っています。
まぁ何はともあれ，形にはしてみたいですね。。
数人でも食いつくものが作れれば，それで追加教材としては十分ですから(;´▽｀A``
Powerpoint VBA epsファイルをパワーポイントに貼り付けるマクロ
今日は，4月に入って初めて空が明るいうちに帰れました。
家でも仕事もしているんですが，急ぎの仕事から本当に久しぶりに開放された気分。
新しい職場で，少しずつペースと要領をつかみ始めている気がします。
（ΦωΦ）
昨年度作ったPowerpoint VBAで作った原子模型やタイマーとかは，授業でちょこっと使うのにとても役に立っています。
熱運動も比較的早い段階で使いそうなので，
手直しをしておかないと。
科学と人間生活という科目が新設されてからずいぶん経つんですが，わたしはその科目を始めて担当できる状況になりました。
手探り状態ですが，楽しくやれそうな感じです。。
さて，問題は著作権。とにかく素材は限られています。
商用等の使用も可という素材が使い勝手はいいわけなので，そういうのも含めて日々自転車操業で教材作成に取り組みつつ，長期に困らないものを作ろうと，素材をひたすら探す。
教育上であれば無料で使えますという素材もまぁあるのでその手助けも得ながらですが，難しい時もあります。
ただ，今日は著作権者の方と電話でやり取りをして，ずいぶん楽になることがありました。
コミュニケーションは大事ですね。ほんと，痛感させられます。
今年度はなんとかなりそうな算段がつくほどの素材のあてが確保でき，気持ちも少し大きくなったとこでした。。。詳しく書くのはあまりよくないと思うので，
同じような境遇の方は，きちんと著作権者の方とお話をされると突破口があるかも，とだけ書かせていただきます。
さて，そんな中，
こちらのプレミアム会員に加入しました。どうも自分にとってメリットはありそうだという判断です。豊富なイラストがあり，eps形式とかで取れるものが多々。
イラストレーター形式のものもとても有用なんですが，イラストレーターで開いて立ち上げたりとちょっと面倒。epsで取れるのが楽です。
epsで取れると何が楽なのかといいますと，
パワーポイントに直接貼り付けれて，グループ化の分解から辿っていくと，パワーポイント等である程度いじれる状態まで分解できます。
色を変えたりとかが最低限できる。
頂点編集もできることが多いです。
なのでとてもありがたいんですね。ベクトルデータなので，拡大等にも便利です。
ただ，Windowsの標準機能でプレビューが見れないので，
パワーポイントにぺたぺた貼っていけば使いやすいよなーということで，
スライド１枚１枚にフォルダ内のepsファイルを貼り付ける，というマクロを作りました。
短いマクロですが，なかなか自分には便利です。
Option Explicit Sub 画像挿入() Dim strFileName As String Dim SlideNo As Long strFileName = Dir(ActivePresentation.Path & "\", vbNormal) ' ファイルが見つからなくなるまで繰り返す Do While strFileName <> "" '各ファイルに行う作業---------------------- If strFileName Like "*.eps" Then '<> ActivePresentation.Name Then SlideNo = ActivePresentation.Slides.Count ActivePresentation.Slides.Add SlideNo + 1, ppLayoutBlank Dim TargetSlide As Slide: Set TargetSlide = ActivePresentation.Slides(SlideNo + 1) TargetSlide.Shapes.AddPicture ActivePresentation.Path & "\" & strFileName, msoFalse, msoTrue, 0, 50 With TargetSlide.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 200, 50) .TextFrame.TextRange.Text = strFileName End With End If '------------------------------------------- ' 次のファイル名を取得 strFileName = Dir() Loop End Sub
これだけ。。
epsファイルがたくさんあるとこに，このマクロ入りのパワーポイントファイルを入れて，マクロを動かすと，
こんな感じです。
こんな風にばらばらにできるし，
パワーポイントはスライドショーの機能の一つにサムネイル表示が2013くらいからあるので，簡易ビュアーにもなりますし，
フリーソフトが簡単には入れれないような職場ではマクロとの併用でepsファイルビュアー的な使い方できるなーと思いました。
Powerpoint VBA 道や線路を描くマクロ
皆さんのブログは楽しく眺めさせてもらっているんですが，とにかく時間が無い毎日を送っているところで，まったく自分では書けていませんでした。
暇だけではありませんね，ネタもすぐにはポンと出てこない。
とりあえず，半端ない忙しさの毎日ですので，仕事を家でも職場でもやっている毎日。
嫌いならブラック極まりないですね。
好きなことなので，どこまでが趣味でどこまでが仕事か判別しにくいところ。
ストレスフリーではあります。（ΦωΦ）
本題に。。
数年間使われ続けていた地図を含む資料を作り直して印刷屋さんにお願いする，という仕事も同時並行で来まして，
すげーめんどくさいなぁと思いながらも，その地図を使い続けるのが気持ち的に許せない。
海岸線などはトレースしていけばある程度描いてくれるので，それでまぁできました。
学校での使用については問題ないと書かれていたので，国土地理院さんの地図を利用させていただいて，Illustratorである程度トレース。
さて，道路全部描くとうざいので，通学に使われている道だけを描かないといけないんですが，道って少なくとも二本線くらいでは描かないと，というところです。
電車の路線はもちょいめんどくさいですよね。。
そこで，それをなんとかするマクロを作ってみました。すごい単純ですが，私にはとてもいいものとなりました。（ΦωΦ）
時間が無いので雑ですみませんが，結果とコードを載せます。
元絵です
曲線でなぞって
マクロで修正
こんなマクロです。
Option Explicit Sub MakeRoad() Dim roadOutline As Shape Dim roadInline As Shape Set roadOutline = ActiveWindow.Selection.ShapeRange(1) Set roadInline = roadOutline.Duplicate(1) roadOutline.Line.Weight = 4.5 roadOutline.Line.ForeColor.RGB = vbBlack roadInline.Line.Weight = 2.25 roadInline.Line.ForeColor.RGB = vbWhite roadInline.Left = roadInline.Left - 12 roadInline.Top = roadInline.Top - 12 Dim ActiveSlide As Slide: Set ActiveSlide = ActiveWindow.View.Slide ActiveSlide.Shapes.Range(Array(SIndex(roadOutline), SIndex(roadInline))).Group End Sub Sub MakeRailroad() Dim roadOutline As Shape Dim roadInline As Shape Set roadOutline = ActiveWindow.Selection.ShapeRange(1) Set roadInline = roadOutline.Duplicate(1) roadOutline.Line.Weight = 4.5 roadOutline.Line.ForeColor.RGB = vbBlack roadInline.Line.Weight = 2.25 roadInline.Line.ForeColor.RGB = vbWhite roadInline.Line.DashStyle = msoLineLongDash roadInline.Left = roadInline.Left - 12 roadInline.Top = roadInline.Top - 12 Dim ActiveSlide As Slide: Set ActiveSlide = ActiveWindow.View.Slide ActiveSlide.Shapes.Range(Array(SIndex(roadOutline), SIndex(roadInline))).Group End Sub Sub Edit2ndItemNode() Dim targetShape As Shape: Set targetShape = ActiveWindow.Selection.ShapeRange(1).GroupItems(1) targetShape.Select End Sub Function SIndex(ByVal targetShape As PowerPoint.Shape) As Long Dim TargetSlide As Slide: Set TargetSlide = ActivePresentation.Slides(targetShape.Parent.SlideIndex) If targetShape.Child = msoTrue Then '完全に真似させてもらった。グループ内図形の場合は親を返す Let SIndex = SIndex(targetShape.ParentGroup) Exit Function End If Dim db As Object: Set db = CreateObject("Scripting.Dictionary") Dim s As Shape Dim i As Long: i = 1 For Each s In TargetSlide.Shapes db(s.Id) = i i = i + 1 Next Let SIndex = db.Item(targetShape.Id) End Function
MakeRoadというのが，一本線を 道のような二重線にします。
MakeRailroadというのが，一本線を 路線のようにします。白の点線のっけてるだけです。
ちょこっと編集したいというときに，二本の線を重ねていますので，下の線を選ぶのが大変です。
その時のために，下の黒線(グループ内のインデックスでは１となります。)を選ぶマクロがEidt2ndItemNodeです。
選ぶとこまではできますが，編集状態にするやり方はわからないので，キーボードにあるメニュー出すキーを押して頂点の編集を選べば下の線が編集できます。
完全に自分用マクロです(´▽`) '`,、'`,、
こんなものが役に立つ人がいるのかわかりませんが，とりあえず目新しいことが書けるネタだったので書きました。
今年度安定して記事が書けるまでとりあえず仕事がんばらないとなぁ( ´ー｀)ﾌｩｰ．．．
Duplicate後の図形のずれって私が前扱ったときは15だったと思うんですが，今回数値を書き出させると12でした。
何に依存するのかなぁ。
もし，きちんと図形が重ならなくてだめやんとなった方は，
roadInline.Left = roadInline.Left - 12 roadInline.Top = roadInline.Top - 12
イミディエイトウィンドウでずれの数値を確認して，上記部分をいじってもらえば，どうにかなると思われます。
連続数字を表に入れるときのメモ
データを文字列を介していろいろな形に加工するという作業をよくやります。
わかってしまえば，たやすい事なんですが，思いつかないとしばらく悩まないといけないポイントがあり，
見事にはまった後解決した記憶がありましたので，たぶん多くの方にはつまんないことですが，メモとして書きます。
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
↓ 5列の表にしたい
| 1 | 2 | 3 | 4 | 5 |
| 6 | 7 | 8 | 9 | 10 |
| 11 | 12 | 13 | 14 | 15 |
こういうことをやりたいことがあるんです。けっこう頻繁に。。
商と余りを使えばいい。
とても簡単なんですが，元の数字をそのまま商と余りで処理しようとすると，意外に難しいことになります。
| 1 | 2 | 3 | 4 | |
| 5 | 6 | 7 | 8 | 9 |
| 10 | 11 | 12 | 13 | 14 |
| 15 |
わたしみたいにあんまり物を考えない人はごり押しします。If文でごりごり書き出したり，
do loopなどでカウンタを条件に合わせてリセットすることで対処したり。。
(;´▽｀A``ホントコマッタモノデス。
さて，引っ張りすぎですね。答えは簡単
0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
0から始まる数値なら，5で割り算をした商と余りを用いれば
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | |
| 商 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 |
| 余り | 0 | 1 | 2 | 3 | 4 | 0 | 1 | 2 | 3 | 4 | 0 | 1 | 2 | 3 | 4 |
きれいにできあがります。
気づいた時の自分に対するあきれようはすごいですが，しばらくするとすぐ忘れるモノデス(;´▽｀A``
さて，パワーポイントのVBAをだいぶ忘れているので，思い出すためにパワーポイントのVBAコードで試します。
パワーポイントのスライド１に３つの表をこのSSのように並べます。それぞれに付けた名前はマクロで使用します。
場所や大きさは適当ですが，列数は守る必要があります。または，コードのほうを好きに変えるのもいいですね。
標準モジュールはこちら
Sub test() Dim TargetSlide As Slide: Set TargetSlide = ActiveWindow.View.Slide Dim SourceTable As Table: Set SourceTable = TargetSlide.Shapes("元表").Table Dim PasteTable As Table: Set PasteTable = TargetSlide.Shapes("貼付表5列").Table Dim PasteTable2 As Table: Set PasteTable2 = TargetSlide.Shapes("貼付表3列").Table Dim DataCollection As Collection: Set DataCollection = New Collection Dim r As Cell For Each r In SourceTable.Rows(1).Cells DataCollection.Add r.Shape.TextFrame.TextRange.Text Next Dim i As Long, tmp As String For i = 0 To DataCollection.Count - 1 tmp = DataCollection(i + 1) PasteTable.Cell(Int(i / 5) + 1, i Mod 5 + 1).Shape.TextFrame.TextRange = tmp Next For i = 0 To DataCollection.Count - 1 tmp = DataCollection(i + 1) PasteTable2.Cell(Int(i / 3) + 1, i Mod 3 + 1).Shape.TextFrame.TextRange = tmp Next End Sub
実行すると，
このとおりです。
コードの補足を少しします。
Dim TargetSlide As Slide: Set TargetSlide = ActiveWindow.View.Slide
対象のスライドからオブジェクトの指定が始まることがほとんどなので，対象のスライドをオブジェクト変数に入れます。
いろいろな方法がありますが，アクティブなスライドを捕まえる書き方の一つです。imihitoさんから教えていただいたヤツデス。
Dim SourceTable As Table: Set SourceTable = TargetSlide.Shapes("元表").Table Dim PasteTable As Table: Set PasteTable = TargetSlide.Shapes("貼付表5列").Table Dim PasteTable2 As Table: Set PasteTable2 = TargetSlide.Shapes("貼付表3列").Table
表に名前がついているように感じますが，パワーポイントの場合いろいろなものはShapeオブジェクトとして扱われます。
各Shapeオブジェクトの中のTable のように指定するわけです。
Dim DataCollection As Collection: Set DataCollection = New Collection
要素数を決めないで値を放り込むのに使えるということで，Collectionを使いました。配列を使ってもいいかもしれません。
Dim r As Cell For Each r In SourceTable.Rows(1).Cells DataCollection.Add r.Shape.TextFrame.TextRange.Text Next
パワーポイントの表については，For Eachステートメントはとても使いにくいんですが，
Rowオブジェクト，Columnオブジェクト内にはCellsコレクションがあるので，そこを利用すれば強引にFor Eachが使えます。
二回 For Eachを使えば表全体を扱えるということですね。
Dim i As Long, tmp As String For i = 0 To DataCollection.Count - 1 tmp = DataCollection(i + 1) PasteTable.Cell(Int(i / 5) + 1, i Mod 5 + 1).Shape.TextFrame.TextRange.Text = tmp Next For i = 0 To DataCollection.Count - 1 tmp = DataCollection(i + 1) PasteTable2.Cell(Int(i / 3) + 1, i Mod 3 + 1).Shape.TextFrame.TextRange.Text = tmp Next
それぞれの表に流し込んでいます。
表のセルは (1,1) ～ なので， 0を利用していることから1を足して利用しています。
Int(i/5)で ５で割った商 ， i mod 5 で５で割った余り という意味です。
ちなみに表の各セルのテキストは
Cell(行,列).Shape.TextFrame.TextRange.Text
と階層が深いのはパワポVBAの仕様なのであきらめるしかありません。。
以上でした（ΦωΦ）
再利用可能な自分用ライブラリを作るために(自分ルール)
VBAでささっと自分で作った部品を読み込ませて使えると，プログラミングできる人がほとんどいない職場ではなんとなく仕事ができる雰囲気になるので，それを実現してみたい。
まとまらないものは，ブログに書きなぐって載せて置いたら検索でちょっと調整して使える。
成熟したものは，テキストファイルでいつも読み取れるものに入れておけば，再利用できるようになる。
なんとか目指したい。
クラスモジュールはカプセル化すれば外部に無用な影響は及ぼしにくいでしょうから楽ですね。
でも，クラスモジュールは便利で楽しいですが，なんでもかんでもクラスモジュールにするのは仰々しい。
問題は標準モジュールの使いまわしか。
標準モジュールでちゃちゃっとすませたいこととかありますしね。テンプレート的な標準モジュールを部品として用意しておいて，それを直接編集できるように考えるのもいい。
そのあたりを考えると，一番気になるのは変数のかぶり。
プライベート変数とスコープが広い変数がかぶると具合がわるいです。
普段はカウンタの変数もi,j,k,・・・でいいですが，繰り返し使うやつには少し配慮したいものです。
基本的に私はスネーク記法というのを自然には用いないので，逆にテンプレート的なものにはスネーク記法で書いておけば被らなくなるのかな。
パスカル記法 … UserNameのように単語の先頭を大文字にする
キャメル記法 … userNameのように最初の単語だけ先頭小文字で2つ目以降の単語は先頭を大文字にする
スネーク記法 … user_nameのように小文字の単語をアンダーバーで区切る
大文字記法 … USER_NAMEのように大文字の単語をアンダーバーで区切る
t-homさんのVBA コーディングガイドライン より引用
自分ルールでは，
使いまわす標準モジュールの変数はスネーク記法!
というのを守って，標準モジュールはbasファイル，クラスモジュールはclsファイルで保存していつも持ち歩くようにすればなんとなくやれそうですね。
OneDriveが使える環境でなら，そこに貯めておけば便利だな。職場では使えないんだよなぁ。セキュリティ上しかた無いけど残念です。
OneDriveが使えないところではどうするかはまた考えるとして，とりあえずスタートを切って修正していけばいいですね。
そろそろ，スマートにコードの再利用ができる人になりたい。
見つからないからささっと作ってしまえ，というのも楽しくていいんですが，効率も目指さないとですね・・（ΦωΦ）
今年度はこの方向性で，パワーポイントのVBAも書き直したり再調整したりしつつ，ブログも書いていきたい。
教材にはパワーポイントVBA,校務にはエクセルVBA，ワードVBAがとても便利なことが多いので，やっぱ３つともやっていくことになりそうですね。
勇者ヨシヒコの メレブさんの
「おれは、この呪文に*** と名付けたよ」
というのを想像しながら，モジュールに名前をつけつつライブラリをためていきたいものです。
勇者ヨシヒコまた見たいなぁ( ´ー｀)
