Excel & VBA
Visual Basic for Application主にExcel日付時間まで入っているセル(10/10 10:10)と、日付のみのセル(10/10)が同じと判定するには
INT( )で可能。日付は整数のシリアル値であるが、時間は小数扱いである。なので整数化してやれば日付のみ取り出せる
UBound、LBoundの引数が2つ取れるの忘れがち
配列の添字の最大最小を調べるためのもの。1つ目は配列を入れる。までは普通。2つ目に次元をintegerで入れると調べる次元を指定できる。2次元配列で縦横それぞれ調べたければ
numCol = UBound(a,1) - LBound(a,1)
numRow = UBound(a,2) - LBound(a,2)
でいい。
多次元配列がややこしい
これはもうひどいトラップ。「真の多次元配列(矩形配列)」と「ジャグ配列(不規則配列)」というものの違いに気づかねばならない。ジャグ配列というのは配列の要素が配列になってる「配列の配列」のやつ。例えば3x100の配列として、ジャグ配列だと、arr(0)で長さ100の1次元配列として扱える。要素までの記述はarr(0)(1)みたいな感じ。
これが「真の多次元配列」になっていると、それが出来ない。要素までの記述は、arr(0,1)のようになり、arr(0)としてのアクセスは拒否られる。1次元配列としての扱いができなくなる。
arr = Array(WorksheetFunction.Transpose(L.ListColumns("RNo").DataBodyRange.Value), _
WorksheetFunction.Transpose(L.ListColumns("IN/Roll").DataBodyRange.Value), _
WorksheetFunction.Transpose(L.ListColumns("Take").DataBodyRange.Value) )
として2次元配列を作った。この時点ではarrは各要素に配列を持った配列、つまり「ジャグ配列」になってる。各要素に使ってるTransposeはRangeを1次元配列化するため。
これに対して、配列の縦横を入れ替える Transpose() を2回使って、もとに戻してみる。
arrL = WorksheetFunction.Transpose(arrL)
arrL = WorksheetFunction.Transpose(arrL)
2つを比較。
Array直後
ローカルウィンドウで見ると、arr(0)(1)のようになってる。これはジャグ配列。arr(0)で1次元配列まるごと取得するのも可。
Transpose後
Transposeを2回した後、真の多次元配列になってarr(1,1)のようなアクセスに変わる。こうなると○番目の要素を配列で抜き出すというのは不可能になる。あと1ベースの配列に変わる。
GASに渡すときにUTF-8にする
しないと日本語が消える
'UTF-8に
Dim objSC As Object
Dim utf8 As String
Set objSC = CreateObject("ScriptControl")
objSC.Language = "Jscript"
utf8 = objSC.CodeObject.encodeURIComponent(total)
Set objSC = Nothing
パクってきたから詳細はわからんが。VBA内でjavascript使えるようにしてんのか?
画像に式が書ける。けど注意
VBAじゃなくてExcelやけど。
一旦なんでもいいので画像をセルに配置。その画像自身に式が書ける。
式で指定したセルがそのまま画像として表示される。何かしらのアクションで参照が変わるような式をかけば表示内容が変わっていく、というようなのも可。
だが、そういうとき少し注意しないといけない?
選択によって画像の表示内容が変わる、といったような場合。
=INDEX(リテイクテーブル[Thumbs],MATCH(15,リテイクテーブル[RNo]))
みたいな式を画像に書きたい。でも関数(ここではINDEXやMATCH)を使うとエラーになる。
=$A$1
みたいにダイレクトに指定する表記は問題ない。
エラーメッセージを見ると、「セル参照」「名前の定義」が使えるようである。
画像の式に関数を入れたときのエラーメッセージ
なので、式を「名前の定義」として一旦保存しておく。名前を参照すれば関数が使えて、複雑な式でも可能。ネットで検索してもみんなそうしてる。でもなぜかうまく行かなかった。「参照が正しくありません」と言われる。
いろいろ試してみた結果。要は一回、名前定義を使った式、
=なまえ
みたいな式をエラー出さずに通してやる必要があるっぽい?その後、式の中身を書き換えてやれば問題なかった。
名前の定義を作成する。その式は例えば「=$A$1」のように関数を使わずシンプルに参照するようなものにしておく。ここでは仮に名前を「なまえ」とする。
画像の式に「=なまえ」を入力する。参照できてるハズ。
「なまえ」で定義してる式の内容を書き換える。
そしたらなぜかうまくいく
なぞ。
エラーにならない式で1回画像に適用させる
適用後で式の中身を書き換えてもエラーにならない
Sheet.Copy はシート非表示にしてたらダメ
マクロでSheetをコピーする時、非表示にしてるとエラーになる。例えば、コピーの元シートを非表示にしていて、シート上に配置したボタンクリックでそれをコピーするような場合。
もっと言うと「#00」として元を非表示にして隠しておき、シート上のボタンクリックでこれを複製したい時。
マクロの中で一度表示してから、コピー、再度非表示にする。
RetakeSheet.Visible = xlSheetVisible
RetakeSheet.Copy after:=Sheets(Sheets.count)
RetakeSheet.Visible = xlSheetVeryHidden
こんな感じ。
「マクロが無効になっている可能性があります。」と出て、エラー、強制終了など起こる。問題なく開けるPCもある。
正確な原因不明。Windowsの更新をすれば直ったという事例あり。
引数にRangeを使いたい時に「オブジェクトが必要です」エラーが出る
Sub A(r as Range)
なんてものを用意した時、別のプロシージャの中から
Dim R as range
Set R=range("A1")
A(R)
てな感じで呼び出すとエラーになる。意味わからんかったけど、
Call A(R)
か
A R
にしたら通る模様。わかりにくー。
コードの実行が中断されました
全てが不可解。止まるはずがないところでこれが出たら、Ctrl押しながら停止ボタンクリックで解決っぽい。
【参考サイト】
プログレスバーの最大値を0には出来ない
別にええやんか...。やらせてくれよ...。プログレスバーの 'max' プロパティには1以上の整数を入れないといけない。いろいろ処理した結果、最大値が0になる可能性がある場合は、先に最大値を調べてからifなどで回避するしかないか。
テキストボックスのカーソル位置に文字挿入
テキストボックスのSelTextを使う。
.SelText
現在選択中の文字列。選択していない時はなにもない。このプロパティに文字列を代入すると、現在地にその文字列が挿入される。
.SelStart
カーソルの現在位置を示す数値。先頭の場合は0。
.SelLength
現在選択中の文字数。
マシン名の取得
WshNetworkを使用する。
参照設定:Wiondows Script Host Object Model
ライブラリ名:IWshRuntimeLisbrary
Dim WshNetworkObject As Object
Set WshNetworkObject = CreateObject("WScript.Network")
MsgBox( WshNetworkObject.ComputerName )
ComputerName:PC名
UserName:ユーザー名
ちなみに取得したPC名は全て大文字になっている模様。
マクロの実行時間に差が出る
マクロを実行する際にどうやって実行するかによって、差があるみたい。
マクロ編集画面からの実行や、ActiveXコントロール、シェイプに割り当てて実行するのに比べ、シート上にフォームコントロールを配置して、それにマクロを割り当てるとなぜか爆速。
もちろんマクロの内容によって大きく変わるが、一例ではシェイプから実行して30秒かかってたマクロが、全く同じコードをフォームのボタンから実行すると1秒で終わった。原因はわからないが、使わない手はない。
ただし、フォームコントロールは色もつけれないしフォントも変えれない。質素すぎる。もう少しデザインしたい。どうするか。
シェイプ:自由なデザインが可能だが遅い。
フォームコントロール;質素だが早い。
導いた答えは、
シェイプで自由にデザイン
その上にフォームコントロールの「ラベル」を配置
ラベルの文字を空文字にする
2つをグループ化する
ラベルも文字を空文字にするとただの透明なコントロールを作成できる。そいつにマクロを割り当てることで、まずは実行速度を確保。そしてそれをシェイプなどで自由にデザインしたボタン(と見せかけた単なる図形)の上に重ねることで、あたかもシェイプをクリックしている感覚で実はラベルをクリックさせる。
そうするとそうすると見た目は自由にデザインしつつも爆速を維持できる。
グループ化するのは移動させるときなど、ばらばらにならないようにするため。
VBA実行中に突然落ちる
根本の原因は特定できていないが、動いていたはずのマクロが突然死することがある。エラーで止まるとかでもないので、1行ずつ実行してExcelが急に落ちる場所を見つけ出す。
解決策とまではいかないが、今まであったパターンだと
IF a=b Then c
と1行で書いていたものを、
IF a=b Then
c
End IF
と、複数行にする。
Not ActiveWorkbook Is ThisWorkbook
と書いていたものを
Not (ActiveWorkbook Is ThisWorkbook)
と、計算順をより明確に。
雑な書き方をしていると落ちやすそうなので横着しないほうがいいかもしれない。
(2つ目はそもそもIsよりNOTのほうが評価順優先やっけ?じゃあ、しばらく問題なく通っていたのは何故か...)
参照設定をいつも忘れる
よく使うものの、ライブラリどれだっけ?てことが多発!!書いとく。
正規表現:
参照設定
Microsoft VBScript Regular Expressions 5.5
実行時バインド
CreateObject("VBScript.RegExp")
HTTP通信:
参照設定
Microsoft XML v6.0
Dim a as XMLHTTP60
Set a = New XMLHTTP60
実行時バインド
Set a = CreateObject("MSXML2.XMLHTTP")