つらつら Excel VBA

私の備忘録です。

配列をそのままセルに出力

配列をセルに出力する際、一次元配列は横方向に出力される。

シート横方向の限界サイズで一次元配列を作成して出力してみた。
Excel2007以降では16,384列もあるが一瞬で終わる。

Option Base 0
Sub 一次元配列の出力テスト()
    
    Dim lastColumn As Long
    lastColumn = Cells.Columns.Count16,384ReDim myArray1(lastColumn - 1)
    
    Dim i As Long
    For i = LBound(myArray1) To UBound(myArray1)
        myArray1(i) = "test" & i
    Next
    
    Dim targetRange As Range
    Set targetRange = Range("A2").Resize(, UBound(myArray1) + 1)
    targetRange = myArray1
    
End Sub


縦方向に出力したい場合は少し書き換える。

For i = LBound(myArray1) To UBound(myArray1)
    Cells(i + 1, 1) = myArray1(i)
Next

この手の処理は出力に時間がかかるので、事前に配列をなんやかんやすると良い。
TRANSPOSE関数で行列を入れ替えたり、一次元配列を二次元配列にしたり、、、いろいろ方法がある模様。
ちな本記事のように膨大なサイズの配列を扱う場合、TRANSPOSE関数は非推奨、ダメ。他の方法で配列をなんやかんやしましょう。




シート縦方向の限界サイズで二次元配列を作成して出力してみた。
Excel2007以降では1,048,576行もあるが、数秒で終わった。

Option Base 0
Sub 二次元配列の出力テスト()
    
    Dim lastRow As Long
    lastRow = Cells.Rows.Count '1,048,576行
    ReDim myArray2(lastRow - 1, 2)
    
    Dim i As Long, j As Long
    For i = LBound(myArray2, 1) To UBound(myArray2, 1)
        For j = LBound(myArray2, 2) To UBound(myArray2, 2)
            myArray2(i, j) = i & "," & j
            'Cells(i+1, j+1) = i & "," & j ' 時間かかる
        Next
    Next
    
    Dim rowSize As Long, columnSize As Long
    rowSize = UBound(myArray2, 1) + 1
    columnSize = UBound(myArray2, 2) + 1
    
    Dim targetRange As Range
    Set targetRange = Range("A1").Resize(rowSize, columnSize)
    targetRange = myArray2
    
End Sub


配列のサイズとセルのサイズを合わせるのが肝心。
出力先のセル範囲が過不足でもエラーにならないので注意。

以上。

乱数と確率

エクセルの乱数は疑似乱数。

セルに以下の数式を入れると出る。

=RAND() '0~1の間で乱数を生成




以下、VBA用。シード値については触れません。

Sub Test1()
    Randomize '乱数ジェネレータを初期化
    Debug.Print Rnd '0~1の間で乱数を生成
End Sub


整数用の乱数生成サンプル。

Function 範囲乱数(iLow As Long, iHigh As Long, Optional init As Boolean = False) As Long
    
    If init Then Randomize
    
    Dim iTmp As Long
    iTmp = Int((iHigh - iLow + 1) * Rnd + iLow)
    
    範囲乱数 = iTmp
    
End Function

'整数%
Function 確率(iRate As Integer, Optional init As Boolean = False) As Boolean
    
    Dim iTmp As Long
    iTmp = 範囲乱数(0, 99, init)
    
    If iRate > iTmp Then
        確率 = True
    Else
        確率 = False
    End If
    
End Function


以下確認用。

Sub 確認用()
    
    Columns("A:B").ClearContents
    
    Randomize
    
    Dim i As Long
    For i = 1 To 100000
        Cells(i, 1) = 範囲乱数(1, 6)
        Cells(i, 2) = 確率(1)
    Next
    
End Sub


以上。

曜日

日曜日~土曜日。1~7の数値に対応。

セルに以下の式を入力。曜日や数値が出せる。

=TEXT(NOW(),"(aaa)") '(日)~(土)
=TEXT(NOW(),"aaaa") '日曜日~土曜日
=WEEKDAY(NOW()) '1~7


セルの書式設定のユーザー定義でも出せる。aaa、(aaa)、ddddなどを設定。



VBAで曜日を出すにはFormatを推奨。

Format( expression[,format [,firstdayofweek [,firstweekofyear]]])

firstdayofweekは週の最初の曜日を設定。省略すると日曜、省略でよし。


以下サンプル。

Dim iWeek As Integer

iWeek = Weekday(Now()) '1~7
Debug.Print WeekdayName(iWeek) '日曜日~土曜日
Debug.Print WeekdayName(iWeek, True) '日~土

Debug.Print Format(Now(), "aaa") '日~土
Debug.Print Format(Now(), "(aaa)") '(日)~(土)
Debug.Print Format(Now(), "aaaa") '日曜日~土曜日
Debug.Print Format(Now(), "w") '1~7
Debug.Print Format(Now(), "ddd") 'Sun~Sat
Debug.Print Format(Now(), "dddd") 'Sunday~Saturday

Debug.Print WorksheetFunction.Weekday(Now()) '1~7
Debug.Print WorksheetFunction.Text(Now(), "aaa") '日~土


曜日別の処理分岐例

Dim s As String

Select Case Weekday(Now())
    Case vbSunday:    s = "休み!"
    Case vbMonday:    s = "今日から仕事"
    Case vbTuesday:   s = "仕事"
    Case vbWednesday: s = "今日も仕事"
    Case vbThursday:  s = "明後日は休み"
    Case vbFriday:    s = "明日は休み!"
    Case vbSaturday:  s = "休み!!!!"
    Case Else:        s = "!?"
End Select

Debug.Print s

各曜日の定数

定数説明
1vbSunday日曜日
2vbMonday月曜日
3vbTuesday火曜日
4vbWednesday水曜日
5vbThursday木曜日
6vbFriday金曜日
7vbSaturday土曜日


firstdayofweekに設定する定数(省略可)

定数説明
0vbUseSystem国準拠
1vbSunday日曜日(既定値)
7vbSaturday土曜日


以上。

CallByName

メゾッドやプロパティをテキストで指定して実行や取得ができる。
用途が思いつかない・・・

CallByName(object,procname,calltype,[args()])

calltype

定数
1vbMethod
2vbGet
4vbLet
8vbSet


使用例

total = WorksheetFunction.Sum(Range("A1:B5"))
total = CallByName(WorksheetFunction, "sum", VbMethod, Range("A1:B5"))

Range("A1").Select
Call CallByName(Range("A1"), "select", VbMethod)

以上。

CellsとRange

範囲を指定するならRangeを使う。Cellsは範囲指定ができない。
変数を使って動的な処理を行うならCellsを使う。固定セルならRangeを使う。
人に見せるなら上記を守る。
CellsとRangeを使い分ける。(私は出来てない)


よくあるパターンから。

Dim r As Range

'単一のセル指定
Set r = Range("A1")
Set r = Cells(1, 1)
Set r = Cells(1, "A")

'範囲指定  以下3例全てA1:F3
Set r = Range("A1:F3")
Set r = Range("A1", "F3")
Set r = Range(Cells(1, 1), Cells(3, 6))

'行列の範囲指定
Set r = Range("A:B") 'A~B列
Set r = Range("1:2") '1~2行目

'全セル
Set r = Cells
Set r = Range(Rows(1), Columns(1))


以下、普通やらない書き方の例。
バグや敵を産み出す可能性がありますので、結果を隠しました。

Dim r As Range

'例1
Set r = Range("A1:E5", "G2")

'例2
Set r = Range("F1:B5").Cells(1, 1)

'例3
Set r = Cells(3, 3).Range("A1", "E5")

'例4
set r = Cells(2, 2).Rows("3:5")

例1 : A1:G5

例2 : B1

例3 : C3:G7

例4 : B4:B6 (出るには出たけど正しい動きなのか分かりませんゴメンナサイ)



以下、おまけ。

Cellsをアンダーバーを駆使して連続で約1,800個書いてみた。ちゃんと動く(笑)
更に連続コピペしたらソースが一部画面から消えたりExcelが強制終了してビビった。
これはよくない。真似してはいけません。注意喚起。

Dim r As Range
Set r = Cells.Cells.Cells.Cells.Cells.Cells.Cells.Cells.Cells. _
        Cells.Cells.Cells.Cells.Cells.Cells.Cells.Cells.Cells. _
        Cells(1, 1) 'A1


強制終了前のスクリーンショット

以上。

While~Wend文ってなんぞ

Do While~Loopと全く一緒。
互換性のために残っている模様。Doを使いましょう。

Sub test1()
    
    Dim i As Long
    
    i = 0
    While i < 10
        i = i + 1
    Wend
    Debug.Print i '10
    
    i = 0
    Do While i < 10
        i = i + 1
    Loop
    Debug.Print i '10
    
    i = 0
    Do Until i >= 10
        i = i + 1
    Loop
    Debug.Print i '10
    
End Sub


UntilはWhileと逆の意味の条件式で混乱するから嫌い。
NotをつければWhileと同じ条件式で書ける!(最初からWhileで書ry)

Do Until Not i < 10
    i = i + 1
Loop

以上。

CSSでソースコードを折り返し表示しない

プログラムは折り返しが多いと見づらい。パソコン表示だと気にならないが、スマホ表示だととても気になる。

pre.code{
    font-size: 80%;
    max-height: 200px;
    background:#f7f7f7;
    white-space: pre;
    overflow: auto;
}

Sub 長いコードを作る目的で作ったコード抜粋()
    
    Dim iYear As Integer, iMonth As Integer, iDay As Integer
    Dim sMsg As String
    
    'テストデータ
    iYear = 2023
    iMonth = 2
    iDay = 30
    
    If iYear = 2022 And iMonth = 1 And iDay = 1 Then
        sMsg = "2022年1月1日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 2 Then
        sMsg = "2022年1月2日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 3 Then
        sMsg = "2022年1月3日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 4 Then
        sMsg = "2022年1月4日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 5 Then
        sMsg = "2022年1月5日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 6 Then
        sMsg = "2022年1月6日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 7 Then
        sMsg = "2022年1月7日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 8 Then
        sMsg = "2022年1月8日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 9 Then
        sMsg = "2022年1月9日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 10 Then
        sMsg = "2022年1月10日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 11 Then
        sMsg = "2022年1月11日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 12 Then
        sMsg = "2022年1月12日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 13 Then
        sMsg = "2022年1月13日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 14 Then
        sMsg = "2022年1月14日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 15 Then
        sMsg = "2022年1月15日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 16 Then
        sMsg = "2022年1月16日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 17 Then
        sMsg = "2022年1月17日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 18 Then
        sMsg = "2022年1月18日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 19 Then
        sMsg = "2022年1月19日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 20 Then
        sMsg = "2022年1月20日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 21 Then
        sMsg = "2022年1月21日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 22 Then
        sMsg = "2022年1月22日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 23 Then
        sMsg = "2022年1月23日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 24 Then
        sMsg = "2022年1月24日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 25 Then
        sMsg = "2022年1月25日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 26 Then
        sMsg = "2022年1月26日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 27 Then
        sMsg = "2022年1月27日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 28 Then
        sMsg = "2022年1月28日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 29 Then
        sMsg = "2022年1月29日"
    ElseIf iYear = 2022 And iMonth = 1 And iDay = 30 Then
        sMsg = "2022年1月30日"
    Else
        sMsg = "日付が不正です"
    End If
    
    Debug.Print sMsg
    
End Sub

以上。