カテゴリー: Excel/VBA/.net

なんだかんだで、Zoo Keeper対戦中。
文句言ってても、結局すきなんだろぉ～・・・えぇ好きです（ポッ）、と下らん一人芝居をしたところで、メモ。

‘———————————————————

まずは、Excelマクロのロックあれこれ。

Const LOCK_PW as String = "pw"
Dim wb as Workbook
Dim ws as Worksheet
Dim rng as Range

と仮設定して・・・

‘ブックのロック/ロック解除

wb.Protect LOCK_PW     'ロック
wb.UnProtect LOCK_PW   '解除

‘シートのロック/ロック解除

If Not ws.ProtectContents Then ws.Protect LOCK_PW , AllowFiltering:=True    'ロック
If ws.ProtectContents Then ws.UnProtect LOCK_PW   '解除

（ロックしてもオートフィルターは使えるようにしといたほうが無難）

‘セルのロック/ロック解除

rng.Locked = True     'ロック
rng.Locked = False    '解除

シートはロックされているかどうか確認するプロパティがあるので、ロックがかかっていなければロックをかけるようにしないと、Excel2013ではロック・ロック解除に時間がかかるので、無駄に時間を食うロジックになってしまう。
セルロックはセルのロックをかけてもシートロックかけてないと有効にはならないので注意。

‘———————————————————

‘セルが数式なのかどうか

If rng.HasFormula Then
    '数式です！
Else
    '数式じゃありません！
End If

シートコピーとかするとグラフとデータをまるっとコピーできるが、そういうわけにもいかないときもある。
例えば、グラフのデータが別シートにあるとき。
これは困ったものだ、どうしたものか・・・と悩んでいたら、

「図でコピーして図で保存しておけばよいではないか！」

と思い立った。
これなら、グラフの元データの場所を考える必要ないし、ピクチャ形式で保存しておけば、容量もさほどとらないのではと思い、早速マクロを組んでみる。

CopyPictureというプロシージャ（関数）を利用するのだが、貼り付け関数は、シート関数にしかないので、貼り付けたいセルをSelectしてから、シート関数で貼り付けるという、少々バカバカしいというか、わざわざSelectする手間がいるところが嫌なところだ。

とりあえず、組んで走らせてみると、想定通りグラフは図形として貼りついて、オホホホホーーーとか思っていたら、動作検証をしてくださっているお客様から、

「うまくいかないんですよね～」とメールがやってきた。

やってみると、本当にうまくいってない。
グラフのプロットエリアがどんどんずれていって、プロットエリアが小粒になってしまっておる。

なぜじゃ！ナゼにじゃ!!

私が触ってた時はうまくいってたのに・・・と思って色々試すと、うまくいくときといかないときがある。

こういうのが一番困るんだよ。

と思いながら眺めていたら、どうやらシートの表示（ズーム）に影響しているのだと分かった。
解像度とかの絡みなのだろうか。

とりあえず、コピー元シートをアクティブにして、
　ActiveWindow.zoom = 100
とした後で、貼り付け先シートに貼り付けてやると、プロットエリアがずれないで貼りつくようになった。

おそらく、コピー元と貼り付け先のズームを同じにしてやらないと、綺麗には貼りついてくれないのだろう。
やれやれ、まったく困ったものだ。

Excelマクロのお勉強。
WorkbookやFormといったオブジェクトにデフォルトで備わっているプロシージャ（メソッド）って、どんな順番で呼び出されるのよ？って思ったので調べてみた。
今更だけど・・・
ちなみにバージョンは2003。

ブックを開いたとき（起動時）

Workbook_Open
Workbook_Activate
Workbook_WindowActivate

※ブックをVisible=FalseにしたときはActivateプロシージャは走らないと思うので、基本はOpenプロシージャに処理を書く。

ブックを閉じるとき（終了時）

Workbook_BeforeClose
Workbook_WindowDeactivate
Workbook_Deactivate

※起動時と同様。

フォームロードしたとき

UserForm_Initialize

フォーム.Showしたとき

UserForm_Layout
UserForm_Activate

フォーム.Hideしたとき

なし

フォームアンロードしたとき

UserForm_QueryClose
UserForm_Terminate

QueryCloseとTerminateの違いは何？って思ったけど、QueryCloseはアンロードの起動処理で、Terminateはホントのアンロード処理という感じ。
つまり、アンロードの起動処理なのでアンロードするかどうかプロシージャ内で分岐できるのがQueryClose。CancelパラメータにTrueを設定するとアンロードを中止できる。
QueryCloseで何事もなければTerminateプロシージャが呼び出されて、アンロード処理の記載ができるようだ。

どこにでもTipsはあるが、メモ

Imports System.IO.StreamReader　を忘れずに。

'Shift-JISでpathのファイルを全量取り込んでClose
Dim sr As StreamReader = New StreamReader(path, System.Text.Encoding.GetEncoding("Shift-JIS"))
Dim buf As String = sr.ReadToEnd()
sr.Close()

'改行コードで分断
Dim buf_line() As String = csv_buf.Split(vbCrLf)
Dim buf_item() As String
Dim cnt As Integer = buf_line.Length

'空ファイルである場合終了
If line_cnt <= 0 Then
    Return False
End If

'csvを1行ずつ処理
Dim i As Integer = 0
For i = 1 To cnt - 1 Step 1
    buf_line(i) = buf_line(i).Replace(vbCr, "").Replace(vbLf, "").Trim()
    If buf_line(i) <> "" Then    'Length>0の方がいいかも
        buf_item = buf_line(i).Split(",")
    End If
Next i

CRCLでぶちってもLFが残るみたいなので、カンマでSplitする前に改行コードを全部Replaceさせた。
普通はCRだけででぶちって、LFは改行として使うのだろう。(セル内改行として必要な場合がある・・・)

全量一括で取り込んだ方が、速度としては早い。
(メモリは喰うので、大量の場合は1行ごとに取り込んだ方がよいのだろう)

OSにあった改行コードを追加する
[VB]
vbNewLine
[VB.net]
ControlChars.NewLine

カーソルが当たったときの処理はEnterに記載。

Sub TextBox1_Enter()
・・・・
End Sub

カーソルがはずれたときの処理はExitに記載。

Sub TextBox1_Exit()
・・・・
End Sub

だそうな。

休日を自動的に作成するマクロを作成中、ハッピーマンデーってどうやって求めるのかを考えた。

色々サイトを見たのだけれど、「結局どうやってやるのよ！」とか、「なんでこんなにこねくり回してるのよ！」とか、まぁとにかくあんまり参考になりそうなロジックがきれいに提示されているサイトがなかったので自分で作った。

引数に、年、月、週を設定すると、Date型の日付が返る仕組み

Private Function GetMondayDateFromYearMonthWeek(ByVal y As Integer, ByVal m As Integer, ByVal w As Integer) As Date

Dim t As Date

t = DateSerial(y, m, 1)
t = DateAdd(“d”, (8 – Weekday(t, vbMonday)) Mod 7, t) ‘1週目の月曜日
GetMondayDateFromYearMonthWeek = DateAdd(“d”, 7 * (w – 1), t)

End Function

例えば、GetMondayDateFromYearMonthWeek(2012,9,3)　とすると、戻り値は2012/9/17となる。

たぶんうまくいくと思うけど、2012年でしか試してない・・・

例えば、2003と2007両方インストールしていた場合、後でインストールしたバージョンで起動するようになっているので、常に2003で起動させたいというときは、以下のコマンドをスタートボタン＞ファイル名を指定して実行で実行させる。

“C:\Program Files\Microsoft Office\OFFICE11\excel.exe” /regserver

パスはインストール環境によって違うので注意。
OFFICE11ってのをOFFICE12とかに変えれば、常時2007で起動とかになるハズ。