Excel VBAã«ãããç¹°ãè¿ãåŠçã®åºæ¬
ç§ã¯VBAã®æŽ»çšçµéšãéããŠåŸãç¥èãæŽçããå ±æããç®çã§èšäºãäœæããŠããããã°ã©ãã³ã°æŽ1幎åã«ãªããšã³ãžãã¢ã§ãã ååã¯ãExcel VBAã«ãããæ¡ä»¶åå²ã«ã€ããŠè§£èª¬ããŸãããä»åã¯ãå®åã§å¿ é ãšãªãç¹°ãè¿ãåŠçã®åºæ¬ãã¯ããã¯ã«ã€ããŠè©³ãã説æããŠãããŸããé©åãªç¹°ãè¿ãåŠçã®å®è£ ã¯ã倧éã®ããŒã¿ãå¹ççã«åŠçããããã®éèŠãªèŠçŽ ã§ããVBAã䜿ã£ãããŒã¿åŠçãå¹çåãããæ¹ãç¹ã«å€§éããŒã¿ã®åŠçãèªååãããæ¹ã¯ããã²åèã«ããŠã¿ãŠãã ããã
- 第1å: Excel VBAã®åºç€ç¥èãšã»ãã¥ãªãã£èšå®
- 第2å: Excel VBAã®åºæ¬æäœãšãªããžã§ã¯ãã®ç解
- 第3å: Excel VBAã«ãããå€æ°ãšå®æ°ã®åºæ¬
- 第4å: Excel VBAã«ãããã·ãŒãæäœã®åºæ¬ãšãšã©ãŒåŠç
- 第5å: Excel VBAã«ãããæ¡ä»¶åå²
- 第6å: Excel VBAã«ãããç¹°ãè¿ãåŠçã®åºæ¬ïŒæ¬èšäºïŒ
- 第7å: Excel VBAã«ãããé åãšFor Eachã®æŽ»çš
ç®æ¬¡
ã¯ããã«
ç¹°ãè¿ãåŠç
For...Nextã¹ããŒãã¡ã³ã
Do...Loopã¹ããŒãã¡ã³ã
For...NextæãšDo...Loopæã®äœ¿ãåã
ãŸãšã
ã¯ããã«
ããã°ã©ãã³ã°ã«ãããŠãç¹°ãè¿ãåŠçãšã¯ãã決ãŸã£ãåæ°ãããæ¡ä»¶ãæºãããããŸã§ãåãåŠçãç¶ç¶ããŠè¡ãããã®ããæ¹ã§ããäŸãã°ã売äžããŒã¿ãç»é²ãããæ°çŸè¡ã®ã·ãŒãããéèšãè¡ãå Žåãã倧éã®é¡§å®¢ããŒã¿ã«å¯ŸããŠåãåŠçãç¹°ãè¿ãå®è¡ããå Žåãªã©ãå®åã§ã¯éåžžã«å€ãçºçããŸããç¹°ãè¿ãåŠçãé©åã«å®è£ ããããšã§ããããã®äœæ¥ãèªååããæ¥åå¹çãå€§å¹ ã«åäžãããããšãã§ããŸãã
Excel VBA ã§ã¯ãFor Next
ãš Do...Loop
ãšãã2ã€ã®ä»£è¡šçãªç¹°ãè¿ãåŠçã®æ¹æ³ããããŸãããããã¯ãç°ãªãç¹æ§ãæã¡ãæ§ã
ãªç¶æ³ã«å¿ããŠäœ¿ãåããããšãæ±ããããŸãããã®èšäºã§ã¯ãããããã®æ§æãšäœ¿ãã©ãããå
·äœäŸã亀ããªãã説æããŸãã®ã§ãVBAã®å¹ççãªã³ãŒãäœæã«åœ¹ç«ãŠãŠããã ããã°å¹žãã§ãã
ç¹°ãè¿ãåŠç
VBAã§ã®ç¹°ãè¿ãåŠçã¯ãç¹å®ã®æ¡ä»¶ãæºãããããŸã§ãŸãã¯æå®ãããåæ°ã ãåŠçãå®è¡ããŸãã代衚çãªãã®ã« For Next
ãš Do...Loop
ããããŸãã
For...Nextã¹ããŒãã¡ã³ã
For...Next
ã¹ããŒãã¡ã³ãã¯ãæå®ããåæ°ã ãåŠçãç¹°ãè¿ããæãåºæ¬çãªç¹°ãè¿ãåŠçã§ãã
åºæ¬æ§æ
For ã«ãŠã³ã¿å€æ° = éå§å€ To çµäºå€ [Step å¢æžå€]
' ç¹°ãè¿ãåŠç
Next [ã«ãŠã³ã¿å€æ°]
- ã«ãŠã³ã¿å€æ°ïŒç¹°ãè¿ãã®åæ°ã管çããå€æ°
- éå§å€ïŒã«ãŠã³ã¿å€æ°ã®åæå€
- çµäºå€ïŒã«ãŠã³ã¿å€æ°ã®æçµå€
- å¢æžå€ïŒã«ãŠã³ã¿å€æ°ã®å¢æžå€ïŒçç¥å¯èœãçç¥æã¯1ïŒ
ã³ãŒãäŸ
Dim i As Long
For i = 1 To 5
Debug.Print i ' ã€ããã£ãšã€ããŠã£ã³ããŠã«è¡šç€º
Next i
ãã®äŸã§ã¯ãå€æ° i
ã1ãã5ãŸã§1ãã€å¢å ããªãããDebug.Print i
ã®åŠçãå®è¡ãããŸããçµæãšããŠãã€ããã£ãšã€ããŠã£ã³ããŠã«1ãã5ãŸã§ã®æ°åã衚瀺ãããŸãã
StepããŒã¯ãŒãã®äœ¿çšäŸ
Step
ããŒã¯ãŒãã䜿çšãããšãã«ãŠã³ã¿å€æ°ã®å¢æžå€ãæå®ã§ããŸãã
Dim i As Long
' 2ãã€å¢å ããã
For i = 0 To 10 Step 2
Debug.Print i
Next i
' 2ãã€æžå°ããã
For i = 10 To 0 Step -2
Debug.Print i
Next i
ãã®äŸã§ã¯ãæåã«0ãã10ãŸã§2ãã€å¢å ããã次ã«10ãã0ãŸã§2ãã€æžå°ãããŠããŸãã
Exit For ã¹ããŒãã¡ã³ã
Exit For
ã¹ããŒãã¡ã³ãã䜿ããšãã«ãŒãã®æ¡ä»¶ãæºããããåã«ã«ãŒããéäžã§æãåºãããšãã§ããŸãã
Dim i As Long
For i = 1 To 10
If i > 5 Then
Exit For ' i ã 5 ãã倧ãããªã£ããã«ãŒããæãã
End If
Debug.Print i
Next i
ãã®äŸã§ã¯ãi
ã5ãã倧ãããªã£ãã Exit For
ãå®è¡ãããã«ãŒããçµäºããŸãã
ç¡éã«ãŒã
ç¡éã«ãŒããšã¯ãçµäºæ¡ä»¶ãæºãããããã«ãŒããåæ¢ããªãç¶æ
ãæããŸããããã°ã©ã ãããªãŒãºããããã«èŠããåå ãšãªãããã°ã©ã ã®ãã°ã§ããFor...Next
ã¹ããŒãã¡ã³ãã§ã¯ãéå§å€ãçµäºå€ãå¢æžå€ã®èšå®ãã¹ã«ãã£ãŠçºçããããšããããŸãã
ç¡éã«ãŒãã®äŸ
' ã«ãŒãå
ã§ã«ãŠã³ã¿ãäžé©åã«æäœ
For i = 1 To 10
i = i - 1 ' iãå¢å ãããæ°žé ã«1以äžãšãªã
Debug.Print i
Next i
ç¡éã«ãŒãã®é²æ¢æ³
-
é©åãªçµäºæ¡ä»¶ã®èšå®
' ã«ãŠã³ã¿ãã«ãŒãå ã§å€æŽããªã For i = 1 To 10 Debug.Print i Next i
-
å®å šè£ 眮ãšããŠã®æ倧ã«ãŒãåæ°ã®èšå®
Dim i As Long Dim loopCount As Long For i = 1 To 10 loopCount = loopCount + 1 ' å®å šè£ 眮ïŒæ倧1000åã§ã«ãŒãã匷å¶çµäº If loopCount > 1000 Then Debug.Print "èŠåïŒã«ãŒãåæ°ã1000åãè¶ ããŸãã" Exit For ' loopCount ã 1000 ãã倧ãããªã£ããã«ãŒããæãã End If ' éåžžã®åŠç Debug.Print i Next i
ãã¹ãæ§é
For...Next
ã¹ããŒãã¡ã³ãã¯å
¥ãåã«ããããšãã§ããŸããããããã¹ãæ§é ãšåŒã³ãŸãããã¹ãæ§é ã䜿ãããšã§ãããè€éãªåŠçãèšè¿°ã§ããŸãã
' ä¹ä¹ã®è¡šãäœæããäŸ
Dim i As Long, j As Long
For i = 1 To 9
For j = 1 To 9
Cells(i, j).Value = i * j
Next j
Next i
ãã®äŸã§ã¯ãå€åŽã®ã«ãŒããä¹ä¹ã®è¡ããå åŽã®ã«ãŒããä¹ä¹ã®åãè¡šããŠããŸãã
å®è·µçãªäŸïŒã»ã«ã®é£ç¶åŠç
Dim i As Long
' A1ããA10ãŸã§é£çªãå
¥å
For i = 1 To 10
Cells(i, 1).Value = i
' å¶æ°ã®å Žåã¯èæ¯è²ãé»è²ã«
If i Mod 2 = 0 Then
Cells(i, 1).Interior.Color = vbYellow
End If
Next i
ãã©ãã«
' äŸ1: ã«ãŠã³ã¿ã®å¢æžãçµäºæ¡ä»¶ã«è¿ã¥ããªã
For i = 1 To 10 Step -1 ' æ£ã®ç¯å²ã«å¯ŸããŠè² ã®å¢å
Debug.Print i
Next i
' éå§å€ãçµäºå€ãã倧ããèšå®
For i = 10 To 1 Step 1
Debug.Print i
Next i
For Nextã®ãã€ã³ã
-
Step
ãæå®ããããšã§ãã«ãŒãã®å¢æžå€ãå€ããããïŒããã©ã«ãã¯1ïŒ -
Step
ã¯æ£æ°ã§ããè² æ°ã§ããæå®å¯èœ -
Exit For
ã䜿ãããšã§ãã«ãŒããéäžã§æãåºãããšãå¯èœ - ãã¹ããæ·±ããªããããªããã泚æïŒéåžž3éå±€ãŸã§ãçæ³ïŒ
Do...Loopã¹ããŒãã¡ã³ã
Do...Loop
ã¹ããŒãã¡ã³ãã¯ãæ¡ä»¶ãæç«ããïŒãŸãã¯æç«ããªããªãïŒãŸã§åŠçãç¹°ãè¿ãããã®æ§æã§ãã
åºæ¬æ§æ
æ¡ä»¶å€å®ã®ã¿ã€ãã³ã°ãšæ¡ä»¶ã®çµã¿åããã«ããã4ã€ã®ãã¿ãŒã³ããããŸãã
-
Do While...Loop
å é ã§æ¡ä»¶å€å®ïŒæ¡ä»¶ãçã®éãç¹°ãè¿ãïŒ
Do While æ¡ä»¶
' ç¹°ãè¿ãåŠç
Loop
-
Do Until...Loop
å é ã§æ¡ä»¶å€å®ïŒæ¡ä»¶ãåœã®éãç¹°ãè¿ãïŒ
Do Until æ¡ä»¶
' ç¹°ãè¿ãåŠç
Loop
-
Do...Loop While
æ«å°Ÿã§æ¡ä»¶å€å®ïŒæ¡ä»¶ãçã®éãç¹°ãè¿ãïŒ
Do
' ç¹°ãè¿ãå®è¡ããåŠç
Loop While æ¡ä»¶
-
Do...Loop Until
æ«å°Ÿã§æ¡ä»¶å€å®ïŒæ¡ä»¶ãåœã®éãç¹°ãè¿ãïŒ
Do
' ç¹°ãè¿ãå®è¡ããåŠç
Loop Until æ¡ä»¶
ã³ãŒãäŸ
以äžã¯ã1ãã3ãŸã§ã«ãŠã³ãã¢ããããŠè¡šç€ºãã4ã€ã®ç°ãªãæžãæ¹ã§ãã ã©ã®æ¹æ³ã§ãåãçµæïŒ1, 2, 3ã®é ã§è¡šç€ºïŒãåŸãããŸãããæ¡ä»¶ã®æžãæ¹ãç°ãªããŸãã
- Do While...LoopïŒå é ã§æ¡ä»¶å€å® - æ¡ä»¶ãçã®éç¹°ãè¿ãïŒ
Dim count1 As Long
count1 = 1
Do While count1 <= 3 ' count1ã3以äžã®éãç¹°ãè¿ãå®è¡
Debug.Print count1 ' count1ã®å€ãåºå
count1 = count1 + 1 ' count1ã1å¢ãã
Loop
- Do Until...LoopïŒå é ã§æ¡ä»¶å€å® - æ¡ä»¶ãåœã®éç¹°ãè¿ãïŒ
Dim count2 As Long
count2 = 1
Do Until count2 > 3 ' count2ã3ãã倧ãããªããŸã§ç¹°ãè¿ãå®è¡
Debug.Print count2 ' count2ã®å€ãåºå
count2 = count2 + 1 ' count2ã1å¢ãã
Loop
- Do...Loop WhileïŒæ«å°Ÿã§æ¡ä»¶å€å® - æ¡ä»¶ãçã®éç¹°ãè¿ãïŒ
Dim count3 As Long
count3 = 1
Do
Debug.Print count3 ' count3ã®å€ãåºå
count3 = count3 + 1 ' count3ã1å¢ãã
Loop While count3 <= 3 ' count3ã3以äžã®éãç¹°ãè¿ãå®è¡
- Do...Loop UntilïŒæ«å°Ÿã§æ¡ä»¶å€å® - æ¡ä»¶ãåœã®éç¹°ãè¿ãïŒ
Dim count4 As Long
count4 = 1
Do
Debug.Print count4 ' count4ã®å€ãåºå
count4 = count4 + 1 ' count4ã1å¢ãã
Loop Until count4 > 3 ' count4ã3ãã倧ãããªããŸã§ç¹°ãè¿ãå®è¡
å®è¡çµæã«ã€ããŠ
ãããããæ¡ä»¶ã®ç¢ºèªã¿ã€ãã³ã°ïŒå é ããæ«å°ŸãïŒãšæ¡ä»¶ã®è¡šçŸæ¹æ³ïŒWhileããUntilãïŒãç°ãªããŸãããçµæã¯åãã«ãªããŸãã
- ãã¹ãŠã®ãã¿ãŒã³ã§åãçµæïŒ1, 2, 3ïŒãåºåãããŸã
- While/Untilã®æ¡ä»¶åŒã¯ç°ãªããŸãããåãåäœãå®çŸããŠããŸã
- While: count <= 3
- Until: count > 3
ãããã¯ãéãã®æ¡ä»¶ã«ãªã£ãŠããŸã
åãã¿ãŒã³ã®äœ¿ãåã
å é å€å®ïŒDo While/UntilïŒ
- æåããæ¡ä»¶ããã§ãã¯ãããå Žå
- æ¡ä»¶ãæåããåœã®å ŽåãäžåºŠãå®è¡ãããªã
æ«å°Ÿå€å®ïŒDo...Loop While/UntilïŒ
- æäœ1åã¯åŠçãå®è¡ãããå Žå
- ã«ãŒãå ã®åŠççµæãæ¡ä»¶å€å®ã«äœ¿çšããå Žå
WhileæãšUntilæã®éã
- Whileã¯ãïœã®éã(æ¡ä»¶åŒãTrueã®éç¶ãã)
äŸïŒ å€æ°i
ã10ããå°ããéãåŠçãç¶ãã - Untilã¯ãïœã«ãªããŸã§ã(æ¡ä»¶åŒãTrueã«ãªããŸã§ïŒFalseã®éç¶ãã)
äŸïŒ å€æ°i
ã10以äžã«ãªããŸã§ãåŠçãç¶ãã
åãçµæãåŸãããã®ç°ãªãè¡šçŸæ¹æ³ã§ããã While
ã¯ã«ãŒããç¶ç¶ããæ¡ä»¶ããUntil
ã¯ã«ãŒããçµäºããæ¡ä»¶ãèšè¿°ãããšèãããšã ããçŽæçã«ç解ããããã§ãããã
ã«ãŒããæããæ¹æ³
Exit Do
ã¹ããŒãã¡ã³ãã䜿çšãããšãDo...Loop
ã¹ããŒãã¡ã³ãã®ã«ãŒããã匷å¶çã«æãåºãããšãã§ããŸãã
Dim i As Long
i = 1
Do While True ' ç¡éã«ãŒã
Debug.Print i
i = i + 1
' iã5ãã倧ãããªã£ããã«ãŒããæãã
If i > 5 Then
Exit Do
End If
Loop
Debug.Print "ã«ãŒããæããŸãã"
ãã®äŸã§ã¯ãDo While True
ã§ç¡éã«ãŒããäœæããIf i > 5 Then Exit Do
㧠i
ã5ãã倧ãããªã£ãå Žåã«ã«ãŒããæããŠããŸãã
Do...Loopæã®ãã€ã³ã
- æ¡ä»¶å€å®ã®ã¿ã€ãã³ã°ãæè»ã«éžæå¯èœ
- While/Untilã䜿ãåããããšã§ãæ¡ä»¶ãããçŽæçã«èšè¿°å¯èœ
-
Exit Do
ã¹ããŒãã¡ã³ãã䜿çšãããšãã«ãŒããã匷å¶çã«æãåºãããšãå¯èœ - ç¡éã«ãŒãã«æ³šæããéäžã§ã«ãŒããçµäºããæ¡ä»¶ã確å®ã«èšå®ããããšãéèŠ
ç¡éã«ãŒãã®é²æ¢ç
- çµäºæ¡ä»¶ãæ確ã«ãã
- ã«ãŒãå ã®åŠçãå®è¡æ¡ä»¶ãå€åãããããšã確èªãã
For...NextæãšDo...Loopæã®äœ¿ãåã
Do...Loop
æãš For...Next
æã¯ã©ã¡ããç¹°ãè¿ãåŠçãè¡ãããã®æ§æã§ãããããããåŸæãªåŠçãç°ãªããŸãã
For...Nextæãé©ããŠããå Žå
- ç¹°ãè¿ãåæ°ã決ãŸã£ãŠããå Žå
- 決ãŸã£ãåæ°ã ãåŠçãç¹°ãè¿ãããå Žå
- ç¹å®ã®ç¯å²ã®å€ãé ã«åãåºããŠåŠçãè¡ãããå Žå
äŸãã°ããªã¹ãã®èŠçŽ ãå šãŠåŠçããå Žåãã1ãã10ãŸã§ã®æ°åãé ã«åºåããå Žåã«é©ããŠããŸãã
For...Nextæãé©ããŠããäŸ
' ç¹°ãè¿ãåæ°ãæ確ãªå Žå
Dim i As Long
For i = 1 To 10 ' 10åç¹°ãè¿ãããšãæ確
Cells(i, 1).Value = i ' A1ããA10ãŸã§é£çªãå
¥å
Next i
Do...Loopæãé©ããŠããå Žå
- ç¹°ãè¿ãåæ°ãããããã決ãŸã£ãŠããªãå Žå
- ç¹å®ã®æ¡ä»¶ãæºãããããŸã§åŠçãç¹°ãè¿ãããå Žå
- ã«ãŒãã®éäžã§æ¡ä»¶å€å®ãè¡ããæ¡ä»¶ã«å¿ããŠã«ãŒããç¶ç¶/çµäºããããå Žå
äŸãã°ããã¡ã€ã«ã®çµãããŸã§èªã¿èŸŒãåŠçããã»ã«ã空çœã«ãªããŸã§åŠçãç¹°ãè¿ãå Žåã«é©ããŠããŸãã
Do...Loopæãé©ããŠããäŸ
' ã»ã«ã空çœã«ãªããŸã§åŠçãç¶ãã
Dim i As Long
i = 1
Do While Cells(i, 1).Value <> "" ' 空çœã»ã«ãåºããŸã§ç¶ãã
Cells(i, 2).Value = Cells(i, 1).Value * 2 ' Aåã®å€ã2åããŠBåã«å
¥å
i = i + 1
Loop
䜿ãåããŸãšã
æ§æ | ç¹°ãè¿ãåæ° | é©ããç¶æ³ |
---|---|---|
Do...Loop |
ããããã決ãŸã£ãŠããªã | ç¹å®ã®æ¡ä»¶ãæºãããããŸã§åŠçãç¹°ãè¿ã |
For...Next |
決ãŸã£ãŠãã | 決ãŸã£ãåæ°ã ãåŠçãç¹°ãè¿ã |
ãŸãšã
ç¹°ãè¿ãåŠçã¯ãVBAããã°ã©ãã³ã°ã«ãããŠããŒã¿ã®å¹ççãªåŠçãå®çŸããéèŠãªèŠçŽ ã§ããFor...Next
æãš Do...Loop
æã¯ãããããã®ç¹æ§ã掻ãããŠäœ¿ãåããããšã§ãããå¹ççã§ä¿¡é Œæ§ã®é«ãããã°ã©ã ãäœæããããšãã§ããŸããç¹ã«ãç¹°ãè¿ãåæ°ãæ確ãªå Žå㯠For...Next
æããæ¡ä»¶ã«åºã¥ãç¹°ãè¿ãã«ã¯ Do...Loop
æã䜿çšãããšãã䜿ãåããæèããããšã§ãããèªã¿ããããã¡ã³ããã³ã¹ããããã³ãŒããå®çŸã§ããŸãããŸããç¡éã«ãŒããé²ãããã®é©åãªçµäºæ¡ä»¶ã®èšå®ããExit
ã¹ããŒãã¡ã³ãã®æŽ»çšã«ãããããã°ã©ã ã®å®å
šæ§ãé«ããããšãã§ããŸãã
ããèšäºã®å 容ã§äžæãªç¹ãããã詳ããç¥ãããéšåããããŸããããã³ã¡ã³ãã§ãç¥ãããã ããããŸããå®åã§ã®ç¹°ãè¿ãåŠçã®æŽ»çšäŸããããå¹ççãªå®è£ æ¹æ³ãªã©ãçæ§ã®ããŠããŠããã²å ±æããŠããã ããã°å¹žãã§ãã
次åã¯ãVBAã«ãããé åã«ã€ããŠè©³ãã解説ããäºå®ã§ããé åãç解ããããšã§ã倧éã®ããŒã¿ãå¹ççã«åŠçã§ããããã«ãªããŸããã©ããã楜ãã¿ã«ïŒ