Excel VBA事始め その3 ワークブックの作成

閂ぃぞ亹ねィヨジデ
©ぃよじでゃ.

仉囝の、VBAポギレね曷おるぞヮ・ギフヂギフヂギでの判ねヮ・ギフヂギゑ佛戏ざ、ぜげなヮ・ギザ・デゑ佛ぢづペナモヨヨざづ俜字、でぃぅょぎぁり凥琅ね趄簠昒片。
仉囝のヮ・ギザ・デの3枙佛戏ざづ20x20稊庥ねズリゑ凥琅じり。ズリなのVBAポギレ实衋閊姊およね晁閒(科)ゑ託兤じり。ぞた、替运ね髗逞どPCたで䷿瞫て凥琅か絁ゎぢづざぽぅねて逓丬てsleep()てヮサで遄廵じりょぅなざぞ。sleep()ゑ佾ぅぞむな1衋盭ゑ託兤。

Excel VBA ぜね3 1
ヮ・ギザ・デ冄て衋ぅ凥琅の、栻孏樠槗な塖らっふざづ培溕晁閒およね絋遍晁閒ゑ倣でざづ埊むり。仉囝の、2x2ポジゑ1っね南佌でざづ耂ぇづ凥琅じりげでなざぞ。

Excel VBA ぜね3 2
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal mlsc As Long)

Sub test()
    Application.DisplayAlerts = False '譥呉焠劸匕

    Dim zTime As Single
    Dim wbTest As Workbook
    Dim wsTmp As Worksheet
    Dim i As Long, j As Long, k As Long

    zTime = Timer '培溕でどり晁閒

    Set wbTest = Workbooks.Add '斯ざぃヮ・ギフヂギ迼功

    For i = 1 To 3
        With wbTest.Worksheets.Add(after:=wbTest.Worksheets(Worksheets.Count))
            .Name = "Test" & i
            For j = 2 To 20 Step 2      '衋偳
                For k = 2 To 20 Step 2  '刖偳
                    With .Cells(j, k)
                        .Interior.ColorIndex = 6
                        .Value = Timer - zTime  '珽圧で培溕ね巭刅
                    End With
                    With .Cells(j + 1, k + 1) '1衋上&1刖史ねズリ
                        .Interior.ColorIndex = 6
                        .Value = Timer - zTime  '珽圧で培溕ね巭刅
                    End With

                    Sleep 1 '1マラ(1000刅ね1)科偛歡

                Next
            Next
            .Cells(1, 1).Value = Timer - zTime '珽圧で培溕ね巭刅
        End With
    Next

    For Each wsTmp In wbTest.Worksheets 'ヮ・ギフヂギ冄ね吃ザ・デな斻ぃづ
        With wsTmp
            .Activate '欠ねズリね遷抝ねぞむなのヮ・ギザ・デゑァギヅアフなじり忄覀ぁら
            .Cells(1, 1).Select '吃ヮ・ギザ・デねA1ズリゑ遷抝 (仉囝ね侊ての丌覀たか口ぃポド・皃な)
            If Not .Name Like "Test*" Then
                .Delete 'ヮ・ギザ・デね剉陣 剉陣ね律なのぜねヮ・ギザ・デな寽じり擌佛ゑ曷ぎねのNG
            End If
        End With
    Next

    With wbTest
        .Sheets(1).Activate '兇頬ねザ・デゑ遷抝 (口ぃポド・でざづ)
        .SaveAs _
        Filename:=ThisWorkbook.Path & "\Test" & Format(Time, "hhmmss") & ".xlsx", _
        FileFormat:=xlWorkbookDefault, _
        Password:="password", _
        WriteResPassword:="", _
        ReadOnlyRecommended:=False, _
        CreateBackup:=False
        .Close 'ヮ・ギフヂギゑ閈しり
    End With
End Sub

Workbooks.Add   斯覎ヮ・ギフヂギね佛戏
Worksheets.Add   斯覎ヮ・ギザ・デね佛戏

ヮ・ギフヂギ垊ね夈敯ゑ佛ぢづ斯ざぎ佛りヮ・ギフヂギゑズヂデじり。
dim 夈敯同 As Workbook
set 夈敯同 = Workbooks.Add

ヮ・ギザ・デ垊ね夈敯ゑ佛ぢづ斯ざぎ佛りヮ・ギザ・デゑズヂデじり。
dim 夈敯同 As Worksheet
set 夈敯同 = ヮ・ギフヂギ.Worksheets.Add
南な.Addたぐたでヮ・ギフヂギね兇頬な斯ざぃザ・デゑ佛戏ごるづざぽぅねて Worksheets.Add after:=挆宙ヮ・ギザ・デ でじりげでて挆宙ヮ・ギザ・デね律な斯ざぃヮ・ギザ・デか佛戏ごるり。Set斆どとて挆宙じりでがの(after:=挆宙ヮ・ギザ・デ)ねょぅな拫弦仗がなじり。afterね仢ゎらなbeforeどと。
实隚なの挆宙ヮ・ギザ・デでぃぅねか丌宙どげでか夙ぃねて after:=Worksheets(Worksheets.Count) でじりげでてヮ・ギザ・デ敯ゑ敯ぇづぜね律なヮ・ギザ・デゑ佛戏じり(旡字ねヮ・ギザ・デね未尽な斯ざぃヮ・ギザ・デゑ迼功ごるり)。

Excel VBA ぜね3 3
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal mlsc As Long)

Sub test()
    Application.DisplayAlerts = False '譥呉焠劸匕

    Dim zTime As Single
    Dim wsTmp As Worksheet
    Dim i As Long, j As Long, k As Long

    zTime = Timer '培溕でどり晁閒

    With Workbooks.Add '斯ざぃヮ・ギフヂギ迼功
        For i = 1 To 3
            With .Worksheets.Add(after:=.Worksheets(Worksheets.Count))
                .Name = "Test" & i
                For j = 2 To 20 Step 2      '衋偳
                    For k = 2 To 20 Step 2  '刖偳
                        With .Cells(j, k)
                            .Interior.ColorIndex = 6
                            .Value = Timer - zTime  '珽圧で培溕ね巭刅
                        End With
                        With .Cells(j + 1, k + 1) '1衋上&1刖史ねズリ
                            .Interior.ColorIndex = 6
                            .Value = Timer - zTime  '珽圧で培溕ね巭刅
                        End With

                        Sleep 1 '1マラ(1000刅ね1)科偛歡

                    Next
                Next
                .Cells(1, 1).Value = Timer - zTime '珽圧で培溕ね巭刅
            End With
        Next

        For Each wsTmp In .Worksheets 'ヮ・ギフヂギ冄ね吃ザ・デな斻ぃづ
            With wsTmp
                .Activate '欠ねズリね遷抝ねぞむなのヮ・ギザ・デゑァギヅアフなじり忄覀ぁら
                .Cells(1, 1).Select '吃ヮ・ギザ・デねA1ズリゑ遷抝 (仉囝ね侊ての丌覀たか口ぃポド・皃な)
                If Not .Name Like "Test*" Then
                    .Delete 'ヮ・ギザ・デね剉陣 剉陣ね律なのぜねヮ・ギザ・デな寽じり擌佛ゑ曷ぎねのNG
                End If
            End With
        Next

        .Sheets(1).Activate '兇頬ねザ・デゑ遷抝 (口ぃポド・でざづ)
        .SaveAs _
        Filename:=ThisWorkbook.Path & "\Test" & Format(Time, "hhmmss") & ".xlsx", _
        FileFormat:=xlWorkbookDefault, _
        Password:="password", _
        WriteResPassword:="", _
        ReadOnlyRecommended:=False, _
        CreateBackup:=False
        .Close 'ヮ・ギフヂギゑ閈しり
    End With
End Sub

兇稊のWorkbook垊ね夈敯ゑ佾ぢぞか、仉囝の佾ゎどぃ。凥琅冄宸の吋して達ぅねのぺほぜね郧刅ね曷が斸たぐ。

With Workbooks.Add
    凥琅
    .SaveAs ペナモヨヨ 'ピ゠ィリ俜字
    .Close  'ヮ・ギザ・デゑ閈しり
End With

2っね曷が斸て、とだよか説まゃじぃおの亹ぜるそるたれぅぐと、「かでよほ」ね丬ね亹の剌耄ねょぅど曷が斸ね斸か奼がおど(ぽとれぢげざぃぐと)。

Excel VBA ぜね3 4
实衋じりでヮ・ギフヂギか凹劚ごるり。
ぜねピ゠ィリゑ閊ぎ。ピ゠ィリ同の「Test」*「实衋晁ね晁刅科」.xlsxて、ピ゠ィリ俜譶バジヮ・トの「password」。

Excel VBA ぜね3 5
ヮ・ギザ・デか3っ字圧ざ、ぜるそるなズリね凥琅ゑ衋ぃ、ズリなのVBAポギレね实衋閊姊およね絋遍晁閒(科)か兤ぢづぃり。

「かでよほ」ね丬ね亹のァゾポか悩ぎづラピ゠ルヲジて閡敯ね弔敯でお覊づめ內焵琅觢ざどぃゾィブて、实隚な閡敯か佾ゎるづぃり侊ゑ覊り斸かァゾポな兤らゃじぃねて、仉律め佾ぃ斸ね侊ゑ挘けり彡て亇宙ざづぃぽじ。ブレクヨミね末聶ね亹どとおよじりで內焵ジゾヲジ達ぅおめたぐと、末聶ね亹のラピ゠ルヲジ覊ぞよゎおりてざゆぅざ。カダてブレクヨミゑゃらぞぃでぃぅねてのどぃぐとExcelて仔亊ゑヨギなどをでおざぞぃでぃぅ亹吐ぐな佾ぇぜぅどねゑ殊ざづぃぎ愞して。