Excel VBAã«ãããé åãšFor Eachã®åºæ¬
ç§ã¯VBAã®æŽ»çšçµéšãéããŠåŸãç¥èãæŽçããå
±æããç®çã§èšäºãäœæããŠããããã°ã©ãã³ã°æŽ1幎åã«ãªããšã³ãžãã¢ã§ããååã¯ãExcel VBAã«ãããç¹°ãè¿ãåŠçã®åºæ¬ãã¯ããã¯ã«ã€ããŠè§£èª¬ããŸãããä»åã¯ã倧éããŒã¿åŠçã«æ¬ ãããªãé
åãšãã³ã¬ã¯ã·ã§ã³ïŒè€æ°ã®ããŒã¿ã®éãŸãïŒæäœãç°¡æœã«èšè¿°ã§ãã For Each
æ§æã«ã€ããŠèª¬æããŸãããããã®ãã¯ããã¯ãç¿åŸããããšã§ãããå¹ççãª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 Each...Nextã¹ããŒãã¡ã³ã
ãŸãšã
ã¯ããã«
é
åãšã¯ãåãããŒã¿åã®è€æ°ã®å€ãã²ãšã€ã®å€æ°ã§ç®¡çããä»çµã¿ã§ããäŸãã°ã100åã®ç€Ÿå¡ããŒã¿ãåŠçããå Žåã100åã®å€æ°ãçšæããã®ã§ã¯ãªãã1ã€ã®é
åå€æ°ã§å¹ççã«ç®¡çã§ããŸãããŸããFor Each
ã¯ã³ã¬ã¯ã·ã§ã³ãé
åã®åèŠçŽ ã«å¯ŸããŠåãåŠçãç°¡æœã«èšè¿°ã§ãã䟿å©ãªæ§æã§ãã
ãã®èšäºãèªãããšã§ã以äžã®ããšãã§ããããã«ãªããŸãã
- é åã®åºæ¬çãªäœ¿ãæ¹ãç解ãã倧éããŒã¿ãå¹ççã«æ±ããããã«ãªã
- é åã®å®£èšãåæåãæäœæ¹æ³ãç¿åŸãã
- å€æ¬¡å é åã䜿ã£ãŠè¡šåœ¢åŒã®ããŒã¿ãå¹ççã«åŠçã§ããããã«ãªã
-
For Each
æ§æã䜿ã£ãŠãã³ã¬ã¯ã·ã§ã³ãé åã®èŠçŽ ã«å¯ŸããåŠçãã·ã³ãã«ã«èšè¿°ã§ããããã«ãªã
å®åã§ã¯ã売äžããŒã¿ã®éèšã顧客æ å ±ã®äžæ¬åŠçãè€æ°ã·ãŒãã®æäœãªã©ãæ§ã ãªå Žé¢ã§é åãšFor Eachã掻èºããŸãããããã®ãã¯ããã¯ã身ã«ã€ããããšã§ãVBAããã°ã©ãã³ã°ã®å¹ ã倧ããåºããã§ãããã
éçé å
éçé åãšã¯ãããã°ã©ã ã®äžã§ããããã決ããããåºå®ãµã€ãºã®é åã®ããšã§ããäžåºŠãµã€ãºã決ãããšããã®ãµã€ãºã¯ããã°ã©ã ã®å®è¡äžã«å€ããããšãã§ããŸãããéçé åã¯éåžžãããŒã¿ã®éãäºåã«ããã£ãŠããå Žåã«äœ¿çšããŸããã¡ã¢ãªã®äœ¿çšå¹çãè¯ããåŠçé床ãéããšããç¹åŸŽããããŸãã
é åã®å®£èš
é
åã¯éåžžã®å€æ°ãšåæ§ã«å®£èšããŸãããæ¬åŒ§ ()
ãè¿œå ããŠé
åã§ããããšã瀺ããŸãã
' åºæ¬çãªé
åã®å®£èš
Dim å€æ°å(ã€ã³ããã¯ã¹) As ããŒã¿å
' äŸïŒ10åã®æŽæ°ãæ ŒçŽã§ããé
å
Dim numbers(9) As Long
' äŸïŒé
åã®ã€ã³ããã¯ã¹ã®ç¯å²ãæå®
Dim scores(1 To 10) As Long
é åã®ã€ã³ããã¯ã¹ïŒèŠçŽ æ°ïŒã«ã€ããŠ
- é
åã¯ãããã©ã«ãã§ã¯0ããå§ãŸããŸãïŒ0åºç¹ïŒ
ã€ãŸãã10åã®èŠçŽ ãæã€é åã®çªå·ã¯0ãã9ã«ãªããŸã -
Option Base 1
ã宣èšéšã«èšè¿°ãããšã1ããå§ãŸãé åïŒ1åºç¹ïŒã«ãªããŸã - æ瀺çã«ç¯å²ãæå®ããå Žåã¯
Dim array(1 To 10) As Long
ã®ããã«æžããŸã - å®åã§ã¯æ··ä¹±ãé¿ãããããæ瀺çã«ç¯å²ãæå®ããæ¹æ³ãæšå¥šãããŸã
é åã®åæåãšå€ã®èšå®
é åã®å®£èšåŸãåèŠçŽ ã«å€ãèšå®ã§ããŸãã
' é
åã®å®£èšã3ã€ã®èŠçŽ ãæ〠"String" åã®é
å
Dim fruits(2) As String
fruits(0) = "ããã"
fruits(1) = "ããã"
fruits(2) = "ãªã¬ã³ãž"
' é
åã®èŠçŽ ã«å€ãèšå®
Dim numbers(1 To 5) As Long
numbers(1) = 10
numbers(2) = 20
numbers(3) = 30
numbers(4) = 40
numbers(5) = 50
é åã®èŠçŽ ã«ã¢ã¯ã»ã¹ãã
é åã®åèŠçŽ ã«ã¯ãã€ã³ããã¯ã¹ã䜿ã£ãŠã¢ã¯ã»ã¹ããŸãã
Dim fruits(1 To 3) As String
fruits(1) = "ããã"
fruits(2) = "ããã"
fruits(3) = "ãªã¬ã³ãž"
' èŠçŽ ã®å€ãååŸããŠè¡šç€º
Debug.Print fruits(2) ' "ããã" ã衚瀺ããã
' èŠçŽ ã®å€ãå€æŽ
fruits(3) = "ã¶ã©ã"
Debug.Print fruits(3) ' "ã¶ã©ã" ã衚瀺ããã
é åã®ãµã€ãºãååŸãã
é
åã®ãµã€ãºïŒèŠçŽ æ°ïŒãååŸããã«ã¯ãLBound
é¢æ°ãš UBound
é¢æ°ã䜿çšããŸãã
Dim numbers(1 To 10) As Long
' é
åã®äžéïŒæå°ã€ã³ããã¯ã¹ïŒãååŸ
Dim lowerBound As Long
lowerBound = LBound(numbers) ' 1 ã代å
¥ããã
' é
åã®äžéïŒæ倧ã€ã³ããã¯ã¹ïŒãååŸ
Dim upperBound As Long
upperBound = UBound(numbers) ' 10 ã代å
¥ããã
' é
åã®èŠçŽ æ°ãèšç®
Dim arraySize As Long
arraySize = UBound(numbers) - LBound(numbers) + 1 ' 10 ã代å
¥ãããïŒ10 - 1 + 1ïŒ
é åæäœã®ãã¹ããã©ã¯ãã£ã¹
- é åã®ç¯å²ãè¶ ããã€ã³ããã¯ã¹ã«ã¢ã¯ã»ã¹ãããšãšã©ãŒãçºçããŸã
- åŠçåã«å¿
ã
LBound
ãšUBound
ã䜿ã£ãŠé åã®ç¯å²ã確èªããŸããã - é
åã®ã«ãŒãåŠçã§ã¯ã
For i = LBound(array) To UBound(array)
ã®ããã«èšè¿°ãããšå®å šã§ã
é åã®å®çšäŸïŒã»ã«ããŒã¿ã®äžæ¬åŠç
以äžã¯ãAåã®ããŒã¿ãé åã«èªã¿èŸŒã¿ãåŠçããŠçµæãBåã«åºåããäŸã§ãã
' 10åã®æååãæ ŒçŽã§ããé
å
Dim data(1 To 10) As String
' ã«ãŒãã«ãŠã³ã¿çšã®å€æ°
Dim i As Long
' A1:A10ã®ããŒã¿ãé
åã«èªã¿èŸŒã
For i = 1 To 10
' Aåã®iè¡ç®ã®å€ãé
åã«ä¿å
data(i) = Cells(i, 1).Value
Next i
' é
åå
ã®ããŒã¿ãåŠçïŒãã¹ãŠå€§æåã«å€æïŒ
For i = 1 To 10
' é
åã®åèŠçŽ ã倧æåã«å€æ
data(i) = UCase(data(i))
Next i
' åŠççµæãB1:B10ã«åºå
For i = 1 To 10
' é
åã®içªç®ã®èŠçŽ ãBåã®iè¡ç®ã®ã»ã«ã«å
¥å
Cells(i, 2).Value = data(i)
Next i
åçé å
åçé åã¯ãããã°ã©ã ã®å®è¡äžã«å¿ èŠã«å¿ããŠãµã€ãºãå€æŽã§ããé åã®ããšã§ãããã®æè»æ§ããããããããŒã¿ã®éãã©ããããããåãã£ãŠããããªãç¶æ³ã§äŸ¿å©ã§ããã¡ã¢ãªãæè»ã«äœ¿çšã§ããŸãããé åã®èŠçŽ æ°ãå€æŽããåŠçãå¿ èŠãªåãéçé åããå°ãé ããšããç¹åŸŽããããŸãã
åçé åã®å®£èš
åçé åã¯ãæåã¯èŠçŽ æ°ãæå®ããã«å®£èšããŸãã
' åçé
åã®å®£èšïŒèŠçŽ æ°ã¯æå®ããªãïŒ
Dim dynamicArray() As String
ReDim ã¹ããŒãã¡ã³ã
ReDim
ã¹ããŒãã¡ã³ãã䜿çšããŠãåçé
åã®ãµã€ãºãèšå®ãããå€æŽãããã§ããŸãã
' åçé
åã®å®£èš
Dim numbers() As Long
' åæãµã€ãºã®èšå®
ReDim numbers(1 To 3)
' é
åã«å€ãèšå®
numbers(1) = 10
numbers(2) = 20
numbers(3) = 30
' é
åã®ãµã€ãºãå€æŽïŒæ¢åã®å€ã¯æ¶å»ãããïŒ
' numbers(1)以éãã¹ãŠ0
ReDim numbers(1 To 10)
' Preserveã¹ããŒãã¡ã³ãã䜿çšãããšãæ¢åã®å€ãä¿æãããŸãŸãµã€ãºãå€æŽã§ãã
' - numbers(1)ã¯10ã®ãŸãŸ
' - numbers(2)ã¯20ã®ãŸãŸ
' - numbers(3)ã¯30ã®ãŸãŸ
' - numbers(4)以éã¯0ïŒæ°ããè¿œå ãããèŠçŽ ïŒ
ReDim Preserve numbers(1 To 10)
åçé åã®æ³šæç¹
-
ReDim
ã ãã䜿ããšãæ¢åã®ããŒã¿ã¯ãã¹ãŠæ¶å»ãããŸã -
ReDim Preserve
ã䜿ããšãæ¢åã®ããŒã¿ãä¿æãããŸãŸãµã€ãºãå€æŽã§ããŸã -
Preserve
ã䜿çšããå ŽåãæåŸã®æ¬¡å ïŒéåžžã¯èŠçŽ æ°ïŒã®ã¿å€æŽå¯èœã§ã - é »ç¹ãªãµã€ãºå€æŽã¯ãåŠçå¹çãäœäžãããå¯èœæ§ããããããæåã«ååãªãµã€ãºã確ä¿ããŠããããšãããããããŸã
åçé åã®å®çšäŸïŒããŒã¿ã®é次远å
次ã®äŸã§ã¯ããŠãŒã¶ãŒãå ¥åããããŒã¿ãåçé åã«è¿œå ããŠãããŸãã
' ãŠãŒã¶ãŒã®å
¥åããŒã¿ãæ ŒçŽããããã®æååé
å
Dim userData() As String
' ãŠãŒã¶ãŒã®å
¥åãäžæçã«æ ŒçŽããå€æ°
Dim userInput As String
' åéããããŒã¿ã®æ°ã远跡ããããã®å€æ°
Dim itemCount As Long
' åéããŒã¿ã®æ°ã0ã«èšå®
itemCount = 0
' ãŠãŒã¶ãŒå
¥åãåãä»ãã
Do
userInput = InputBox("ããŒã¿ãå
¥åããŠãã ããããã£ã³ã»ã«ã§çµäºããŸãã", "ããŒã¿å
¥å")
' ãã£ã³ã»ã«ãã¿ã³ãæŒãããå Žåã¯ã«ãŒããæãã
If userInput = "" Then
' ã«ãŒããçµäº
Exit Do
End If
' é
åã®ãµã€ãºãæ¡åŒµ
itemCount = itemCount + 1
' é
åã®ãµã€ãºã1ã€å€§ãããã
ReDim Preserve userData(1 To itemCount)
' å
¥åããŒã¿ãé
åã«æ ŒçŽ
userData(itemCount) = userInput
Loop
' ã«ãŒãã«ãŠã³ã¿çšã®å€æ°
Dim i As Long
' åéããããŒã¿ã衚瀺
For i = 1 To itemCount
' iè¡1åã®ã»ã«ã«ãuserDataé
åã®içªç®ã®èŠçŽ ã®å€ãå
¥å
Cells(i, 1).Value = userData(i)
Next i
' å®äºã¡ãã»ãŒãžã衚瀺
MsgBox "ããŒã¿ã®åéãå®äºããŸããã" & itemCount & "件ã®ããŒã¿ãååŸããŸããã"
InputBoxã«ã€ããŠ
ãŠãŒã¶ãŒããã®å
¥åãåãåãããã®åºæ¬çãªé¢æ°ã§ãããã®é¢æ°ã䜿ãã°ããŠãŒã¶ãŒã«å¯ŸããŠå
¥åãã€ã¢ãã°ã衚瀺ãããã®å
¥åãã¹ã¯ãªããïŒããã°ã©ã å
ïŒã§æŽ»çšã§ããŸãã
InputBox
é¢æ°ã¯2ã€ã®åŒæ°ãåããŸããæåã®åŒæ°ã¯ãŠãŒã¶ãŒã«å
¥åãä¿ãããã®ã¡ãã»ãŒãžã§ãããäžã€ã¯ã¿ã€ãã«ïŒãã€ã¢ãã°ããã¯ã¹ã®äžéšã«è¡šç€ºãããããŒã®ã¿ã€ãã«ïŒã§ãã
詳ããã¯ãä»åŸã®èšäºã§è§£èª¬ããŸãã
å€æ¬¡å é å
å€æ¬¡å é åã¯ãè€æ°ã®æ¬¡å ïŒè»žïŒãæã€é åã§ããäžè¬çã«ã¯ã2次å ã®è¡šåœ¢åŒããŒã¿ã3次å ã®ç«äœçãªããŒã¿ãæ±ãå Žåã«äœ¿çšããŸãã
2次å é åã®å®£èšãšæäœ
2次å é åã¯ãè¡ãšåãæã€è¡šã®ãããªããŒã¿æ§é ã§ãã
' 2次å
é
åã®å®£èšïŒ3è¡4åã®è¡šãè¡šçŸïŒ
Dim matrix(1 To 3, 1 To 4) As Long
' å€ã®èšå®
matrix(1, 1) = 11
matrix(1, 2) = 12
matrix(1, 3) = 13
matrix(1, 4) = 14
matrix(2, 1) = 21
matrix(2, 2) = 22
matrix(2, 3) = 23
matrix(2, 4) = 24
matrix(3, 1) = 31
matrix(3, 2) = 32
matrix(3, 3) = 33
matrix(3, 4) = 34
' é
åã®å€ã«ã¢ã¯ã»ã¹
Debug.Print matrix(2, 3) ' 23 ã衚瀺ããã
å€æ¬¡å é åã®åŠç
å€æ¬¡å
é
åã®å€ãåŠçããã«ã¯ãå
¥ãåã«ãªã£ã For
ã«ãŒãã䜿çšããŸãã
' 3è¡4åã®2次å
é
å
Dim matrix(1 To 3, 1 To 4) As Long
' ã«ãŒãçšã®å€æ°
Dim i As Long, j As Long
' é
åã«å€ãèšå®
For i = 1 To 3 ' 1ãã3ãŸã§è¡ã®ã«ãŒããéå§
For j = 1 To 4 ' 1ãã4ãŸã§åã®ã«ãŒããéå§
matrix(i, j) = i * 10 + j ' é
åã®åèŠçŽ ã«ãè¡çªå· à 10 + åçªå·ã®å€ã代å
¥
Next j ' åã®ã«ãŒããçµäº
Next i ' è¡ã®ã«ãŒããçµäº
' é
åã®å€ã衚瀺
For i = 1 To 3 ' 1ãã3ãŸã§è¡ã®ã«ãŒããéå§
For j = 1 To 4 ' 1ãã4ãŸã§åã®ã«ãŒããéå§
Debug.Print matrix(i, j) ' é
åã®å€ãé çªã«è¡šç€º
Next j ' åã®ã«ãŒããçµäº
Debug.Print "" ' è¡ã®åºåããšããŠç©ºè¡ãæ¿å
¥
Next i ' è¡ã®ã«ãŒããçµäº
å€æ¬¡å é åã®å®çšäŸïŒã·ãŒãããŒã¿ã®é«éèªã¿æžã
ã·ãŒãã®ããŒã¿ãäžåºŠã«é åã«èªã¿èŸŒã¿ãåŠçããŠäžåºŠã«æžãæ»ãæ¹æ³ã¯ãã»ã«ããšã®èªã¿æžããããå€§å¹ ã«é«éã§ãã
' A1:D10ã®ã»ã«ç¯å²ã2次å
é
åãšããŠèªã¿èŸŒã
Dim data As Variant
data = Range("A1:D10").Value
' é
åå
ã®ããŒã¿ãåŠçïŒäŸïŒåã»ã«ã®å€ã2åã«ããïŒ
Dim i As Long, j As Long
For i = LBound(data, 1) To UBound(data, 1) ' é
åã®è¡ãé ã«åŠçããã«ãŒã
For j = LBound(data, 2) To UBound(data, 2) ' é
åã®åãé ã«åŠçããã«ãŒã
If IsNumeric(data(i, j)) Then ' ã»ã«ã®å€ãæ°å€ã§ããã確èª
data(i, j) = data(i, j) * 2 ' æ°å€ã®å Žåãå€ã2åã«ãã
End If
Next j ' åã®ã«ãŒããçµäº
Next i ' è¡ã®ã«ãŒããçµäº
' åŠçããé
åãF1:I10ã«äžåºŠã«æžã蟌ã
Range("F1:I10").Value = data
MsgBox "ããŒã¿åŠçãå®äºããŸããã"
Variantåã«ã€ããŠ
äžèšã®ã³ãŒãã§äœ¿ãããŠãã Variant
åã¯ãããŸããŸãªããŒã¿åãäžã€ã®å€æ°ã§æ±ããç¹æ®ãªåã§ãã第3å: Excel VBAã«ãããå€æ°ãšå®æ°ã®åºæ¬ã§ã¯ Variant
åã¯ãéæšå¥šããšèšèŒããŠãããŸãããä»åã®ããã«Excelã®ç¯å²ããã®ãŸãŸé
åãšããŠæ±ãå ŽåããšãŠã䟿å©ã§ãã
å®åã§ã¯åŠçã®ã¹ããŒããšã³ãŒãã£ã³ã°ã®ç°¡äŸ¿ããéèŠã§ãããã®ãããªå Žé¢ã§ã¯ãVariant
åã®æè»ãã掻ããããšã§ãã³ãŒããããã·ã³ãã«ã«ã§ãããšããå©ç¹ããããŸãã
' Variantåã䜿çšããå ŽåïŒã·ã³ãã«ãªèšè¿°ïŒ
Dim data As Variant
data = Range("A1:D10").Value ' ã·ãŒãã®ç¯å²ãçŽæ¥é
åãšããŠååŸã§ãã
' Variantåã䜿çšããªãå ŽåïŒè€éãªèšè¿°ïŒ
Dim data(1 To 10, 1 To 4) As String ' é
åã®ãµã€ãºãäºåã«æ±ºããå¿
èŠããã
Dim i As Long, j As Long ' ã«ãŒãçšã®å€æ°
For i = 1 To 10 ' 1ãã10ãŸã§è¡ã®ã«ãŒããéå§
For j = 1 To 4 ' 1ãã4ãŸã§åã®ã«ãŒããéå§
data(i, j) = Cells(i, j).Value ' ã»ã«ããšã«åå¥ã«å€ãååŸ
Next j ' åã®ã«ãŒããçµäº
Next i ' è¡ã®ã«ãŒããçµäº
ãã ããVariant
åã䜿çšãããšãå€æ°ã®åãæ確ã«ãªãã¥ãããå€ãã®ã¡ã¢ãªãæ¶è²»ããå¯èœæ§ããããããæå³ãã䜿çšããã®ã¯é¿ããŸããããä»åã¯ãã®å©äŸ¿æ§ãšå¿
èŠæ§ãã䜿çšããŠãããæ確ãªç®çã®ç¯å²å
ã§ã®äœ¿çšã¯é©åãšèšããŸãã
é åã®æäœé¢æ°
é åãå¹ççã«æäœããããã«ãVBAã«ã¯äŸ¿å©ãªé¢æ°ãçšæãããŠããŸããããã§ã¯ããã䜿çšãããé åæäœé¢æ°ã«ã€ããŠèª¬æããŸãã
Arrayé¢æ°
Array
é¢æ°ã¯ãç°¡åã«é
åãäœæãã䟿å©ãªé¢æ°ã§ããç¹ã«ãåæåãããå€ãæã€å°èŠæš¡ãªé
åã®äœæã«äŸ¿å©ã§ãã
' Variantåã®å€æ°ãå¿
ã䜿çš
Dim fruits As Variant
' Arrayé¢æ°ã§é
åãäœæïŒ" , " åºåãã§åèªãèšèŒïŒ
fruits = Array("ããã", "ããã", "ãªã¬ã³ãž", "ã¶ã©ã", "ãã¡ã")
' äœæããé
åã®èŠçŽ ã«ã¢ã¯ã»ã¹
Debug.Print fruits(0) ' "ããã" ã衚瀺ããã
Debug.Print fruits(1) ' "ããã" ã衚瀺ããã
Debug.Print fruits(4) ' "ãã¡ã" ã衚瀺ããã
Erase é¢æ°
Erase
é¢æ°ã¯ãé
åã®èŠçŽ ãåé€ããããã«äœ¿çšããŸãã
-
éçé åã®å Žå
é åã®èŠçŽ ã¯ãã¹ãŠåæå€ã«ãªã»ãããããŸããæ°å€åã§ããã° 0ãæåååã§ããã°ç©ºæå "" ã«ãªããŸãã -
åçé åã®å Žå
é åã®ã¡ã¢ãªé åã解æŸãããé åã¯ååšããªãç¶æ ã«ãªããŸãã
' éçé
åã®å®£èšãšåæå
Dim staticArray(1 To 2) As String
staticArray(1) = "ããã"
staticArray(2) = "ããã"
' éçé
åã®èŠçŽ ãåé€
Erase staticArray
' åèŠçŽ ã確èªãããšã空æåã«ãªã£ãŠãã
Debug.Print staticArray(1) ' æ¹è¡ããã
Debug.Print staticArray(2) ' æ¹è¡ããã
' åçé
åã®å®£èšãšåæå
Dim dynamicArray() As Long
ReDim dynamicArray(1 To 2)
dynamicArray(1) = 100
dynamicArray(2) = 200
' åçé
åã®èŠçŽ ãåé€
Erase dynamicArray
' é
åã¯ååšããªãç¶æ
ã«ãªããããã¢ã¯ã»ã¹ãããšãšã©ãŒã«ãªã
Debug.Print dynamicArray(1) ' ãšã©ãŒãçºçãã
IsArrayé¢æ°
IsArray
é¢æ°ã¯ãå€æ°ãé
åãã©ããã確èªããããã®é¢æ°ã§ããé
åã§ããã° True
ãããã§ãªããã° False
ãè¿ããŸããé
åã®ç¢ºèªæã«äœ¿çšããŸãã
' é
åãšéåžžã®å€æ°ã宣èšãã
Dim numbers(1 To 3) As Long ' é
åã宣èš
Dim normalVar As Long ' éåžžã®å€æ°ã宣èš
' é
åãã©ãããå€å®ããŠçµæã衚瀺
Debug.Print "numbersã®å€å®çµæ: " & IsArray(numbers) ' True ã衚瀺ããã
Debug.Print "normalVarã®å€å®çµæ: " & IsArray(normalVar) ' False ã衚瀺ããã
For Each...Nextã¹ããŒãã¡ã³ã
For Each...Next
æ§æã¯ãé
åãã³ã¬ã¯ã·ã§ã³ã®åèŠçŽ ã«å¯ŸããŠç¹°ãè¿ãåŠçãè¡ã䟿å©ãªæ¹æ³ã§ããéåžžã® For
ã«ãŒããããç°¡æœã«èšè¿°ã§ããç¹ã«ãèŠçŽ ãäœåãããåãããªããŠãç°¡åã«æ±ãããããéåžžã«äŸ¿å©ã§ãã
åºæ¬æ§æ
For Each èŠçŽ å€æ° In ã³ã¬ã¯ã·ã§ã³ãŸãã¯é
å
' åèŠçŽ ã«å¯ŸããåŠç
Next èŠçŽ å€æ°
é åã§ã®äœ¿çšäŸ
' é
åã®å®£èšãšåæå
Dim fruits As Variant
fruits = Array("ããã", "ããã", "ãªã¬ã³ãž", "ã¶ã©ã", "ãã¡ã")
' For Eachã䜿ã£ãŠé
åã®åèŠçŽ ãåŠç
Dim fruit As Variant
' fruitsã®å·Šããé ã«ç¹°ãè¿ã
For Each fruit In fruits
Debug.Print fruit
Next fruit
For EachãšFor Nextã®éã
-
For Each
ã¯èŠçŽ ã®å€ã«çŽæ¥ã¢ã¯ã»ã¹ã§ããã€ã³ããã¯ã¹ã管çããå¿ èŠããªã -
For Next
ã¯ã€ã³ããã¯ã¹ã䜿ã£ãæäœãå¿ èŠïŒäŸïŒé åã®ç¹å®äœçœ®ã®èŠçŽ ãå€æŽããå ŽåïŒ -
For Each
ã¯ã³ã¬ã¯ã·ã§ã³ãªããžã§ã¯ãã«å¯ŸããŠç¹ã«å¹æç -
For Each
ã¯ã³ãŒããç°¡æœã«ãªããå¯èªæ§ãåäžãã
䜿ãåãã®ãã€ã³ã
-
For Next
: ã€ã³ããã¯ã¹ãå¿ èŠãªæãç¹å®ã®ééã§åŠçãè¡ãæ -
For Each
: å šèŠçŽ ãé çªã«åŠçãããæãã€ã³ããã¯ã¹ãäžèŠãªæ
Worksheetsã³ã¬ã¯ã·ã§ã³ã®æäœ
Excel VBAã§ã¯ãã¯ãŒã¯ã·ãŒããã»ã«ç¯å²ãªã©ã®ãªããžã§ã¯ãã¯ã³ã¬ã¯ã·ã§ã³ãšããŠç®¡çãããŠããŸããFor Each
ã䜿ããšããããã®ã³ã¬ã¯ã·ã§ã³ãç°¡åã«æäœã§ããŸãã
'ã¯ãŒã¯ã·ãŒããæ ŒçŽããããã®å€æ°
Dim ws As Worksheet
' ãã¹ãŠã®ã¯ãŒã¯ã·ãŒãã«å¯ŸããŠåŠçãå®è¡
For Each ws In ThisWorkbook.Worksheets
' åã·ãŒãã®A1ã»ã«ã«çŸåšã®æ¥ä»ãå
¥å
ws.Range("A1").Value = Date
' ã·ãŒãåãB1ã»ã«ã«å
¥å
ws.Range("B1").Value = ws.Name
Next ws
MsgBox "ãã¹ãŠã®ã·ãŒãã®åŠçãå®äºããŸããã"
ã»ã«ç¯å²ã®æäœ
' ã»ã«ãæ ŒçŽããå€æ°
Dim cell As Range
' éžæãããã»ã«ç¯å²ã®åã»ã«ã«å¯ŸããŠåŠçãå®è¡
For Each cell In Selection
' æ°å€ã»ã«ã®å Žåãå€ã2åã«ãã
If IsNumeric(cell.Value) Then
cell.Value = cell.Value * 2
' æååã»ã«ã®å Žåã倧æåã«å€æ
ElseIf TypeName(cell.Value) = "String" Then
cell.Value = UCase(cell.Value)
End If
Next cell
MsgBox "éžæã»ã«ã®åŠçãå®äºããŸããã"
Exit For ã®äœ¿çš
For Each
ã«ãŒãã Exit For
ã䜿ã£ãŠéäžã§æãåºãããšãã§ããŸãã
' ã»ã«ãæ ŒçŽããå€æ°
Dim cell As Range
'æ€çŽ¢ããå€ãæ ŒçŽããå€æ°
Dim searchValue As String
' æ€çŽ¢ããç¹å®ã®å€ãèšå®
searchValue = "ç¹å®ã®å€"
' A1:Z100ã®ç¯å²ã§ç¹å®ã®å€ãæ€çŽ¢
For Each cell In Range("A1:Z100")
'ã»ã«ã®å€ãæ€çŽ¢å€ãšäžèŽããããã§ãã¯
If cell.Value = searchValue Then
' èŠã€ãã£ãã»ã«ãéžæããŠèæ¯è²ãé»è²ã«
cell.Select
cell.Interior.Color = vbYellow
MsgBox "å€ã" & searchValue & "ããèŠã€ãããŸãããã»ã«: " & cell.Address
Exit For ' æåã«èŠã€ãã£ãæç¹ã§ã«ãŒããæãã
End If
Next cell
倧éããŒã¿åŠçã®ããã©ãŒãã³ã¹åäžãã¯ããã¯
- ã»ã«ã®åå¥ã¢ã¯ã»ã¹ãããé åã䜿ã£ãäžæ¬åŠçãåªå ãã
- å¿ èŠãªç¯å²ã ããé åã«èªã¿èŸŒã¿ãäžèŠãªããŒã¿ã¯å«ããªã
- é »ç¹ãªé åã®ãªãµã€ãºïŒãµã€ãºå€æŽïŒãé¿ããå¿ èŠãªãµã€ãºãäºåã«ç¢ºä¿ãã
ãŸãšã
é åã¯ãVBAããã°ã©ãã³ã°ã«ãããŠããŒã¿ãå¹ççã«ç®¡çã»åŠçããããã®éèŠãªèŠçŽ ã§ããéçé åãšåçé åã¯ãããããã®ç¹æ§ã掻ãããŠäœ¿ãåããããšã§ãããå¹ççã§æè»æ§ã®é«ãããã°ã©ã ãäœæããããšãã§ããŸããç¹ã«ãããŒã¿éãäºåã«ææ¡ã§ããå Žåã¯éçé åããå®è¡æã«ãµã€ãºå€åã®å¯èœæ§ãããå Žåã¯åçé åã䜿çšãããšãã䜿ãåããæèããããšã§ãããæé©åãããã³ãŒããå®çŸã§ããŸãããŸããå€æ¬¡å é åãã»ã«ç¯å²ã®äžæ¬åŠçã掻çšããããšã§ãåŠçé床ãå€§å¹ ã«åäžãããããšãã§ããŸãã
å®åã§ã¯ã売äžããŒã¿ã®åæã瀟å¡æ å ±ã®äžæ¬åŠçãè€æ°ã·ãŒãã®æ¯èŒãªã©ãæ§ã ãªå Žé¢ã§ãããã®ãã¯ããã¯ã掻èºããŸããç¹ã«å€§éããŒã¿ãæ±ãå Žåã¯ãã»ã«ã®åå¥ã¢ã¯ã»ã¹ãããé åã䜿ã£ãäžæ¬åŠçãå§åçã«é«éã§ãã
ããèšäºã®å
容ã§äžæãªç¹ãããã詳ããç¥ãããéšåããããŸããããã³ã¡ã³ãã§ãç¥ãããã ããããŸããå®åã§ã®é
åã For Each
ã®æŽ»çšäŸããããå¹ççãªå®è£
æ¹æ³ãªã©ãçæ§ã®ããŠããŠããã²å
±æããŠããã ããã°å¹žãã§ãã
次åã¯ãFormula
ãš Value
ã®äœ¿ãåããInputBox
é¢æ°ããããŠã¯ãŒã¯ã·ãŒãé¢æ°ã®å©çšã«ã€ããŠè©³ãã解説ããäºå®ã§ãããããã®æ©èœãç解ããããšã§ãExcel VBAã®æŽ»çšç¯å²ãããã«åºããã§ããããã©ããã楜ãã¿ã«ïŒ