Excel VBAã«ãããæ¡ä»¶åå²ã®åºæ¬
ç§ã¯VBAã®æŽ»çšçµéšãéããŠåŸãç¥èãæŽçããå
±æããç®çã§èšäºãäœæããŠããããã°ã©ãã³ã°æŽ1幎åã«ãªããšã³ãžãã¢ã§ãã
ååã¯ãã·ãŒãæäœã®åºæ¬ãšãšã©ãŒåŠçã«ã€ããŠè§£èª¬ããŸãããä»åã¯ã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ã®æŽ»çš
ç®æ¬¡
ã¯ããã«
æ¡ä»¶åå²
Ifæ
Select Case
æ¡ä»¶åå²ã®äœ¿ãåã
ãŸãšã
ã¯ããã«
æ¡ä»¶åå²ã¯ããããããªãããšããåŠçãããã°ã©ã ã§å®çŸããæ©èœã§ããäŸãã°ãããã売äžãç®æšå€ãè¶ ããŠããã°ãããŒãã¹ãèšç®ããããããåšåº«ãäžè¶³ããŠããã°ãçºæ³šåŠçãè¡ãããšãã£ãæ¥åããžãã¯ãå®è£ ããéã«äžå¯æ¬ ãªèŠçŽ ã§ãã
VBAã§ã¯ãIf
æãš Select Case
æãäž»ã«äœ¿çšãããããããç°ãªãæ¡ä»¶ã«åºã¥ããŠããã°ã©ã ã®æµããæè»ã«å¶åŸ¡ããããã«çšããããŸããæ¡ä»¶åå²ãç解ã掻çšããããšã§ãããè€éãªVBAããã°ã©ã ãäœæããããšãã§ããæ¥åã®èªååãå¹ççã«å®çŸå¯èœã§ãã
æ¡ä»¶åå²
æ¡ä»¶åå²ãšã¯ãããã°ã©ã ã®äžã§ç¹å®ã®æ¡ä»¶ãæºãããŠãããã©ãããå€æãããã®çµæã«å¿ããŠç°ãªãåŠçãå®è¡ããä»çµã¿ã®ããšã§ãã
æ¥åžžç掻ã§ãããéšãéã£ãŠãããåããããããä¿¡å·ãéã«ãªã£ãã暪ææ©éãæž¡ãããšãã£ãããã«ãç¡æèã®ãã¡ã«æ¡ä»¶åå²ãè¡ã£ãŠããŸãã
VBAã«ãããŠããæ¡ä»¶åå²ã¯ããã°ã©ã ã®æµããå€ããéèŠãªåœ¹å²ãæ ã£ãŠããŸãã äŸãã°ããã»ã«ã®å€ãç¹å®ã®æ°å€ä»¥äžãªãæåã®è²ãå€ãããããå ¥åãããããŒã¿ã«ãã£ãŠåŠçå 容ãå€ããããšãã£ãåŠçãèªååããããšãã§ããŸãã
Ifæ
If
æã¯ãæãåºæ¬çãªæ¡ä»¶åå²ã®æ¹æ³ã§ããããïœãªãããšããæ¡ä»¶å€æãè¡ããŸãã
äžããããæ¡ä»¶ãçãŸãã¯åœã§ããããè©äŸ¡ããããã«åºã¥ããŠç°ãªãåŠçãè¡ããŸãã
æ¡ä»¶ã«ã¯æ¯èŒæŒç®åïŒã²ãããããããïŒã䜿çšããŸããæ¯èŒæŒç®åãšã¯ã2ã€ã®å€ãæ¯èŒããŠããã®çµæããæ£ãã (True)ãããééã£ãŠãã (False)ãããå€æããããã«äœ¿ãèšå·ã®ããšã§ãã
ç°¡åã«èšããšããAãšBã¯çããïŒããAã¯Bãã倧ããïŒããšãã£ã質åãã³ã³ãã¥ãŒã¿ãŒã«ããããã®éå ·ã§ãã
æ¯èŒæŒç®åã®çš®é¡
æŒç®å | æå³ | äŸ |
---|---|---|
= | çãã | If A = B Then |
<> | çãããªã | If A <> B Then |
> | ãã倧ãã | If A > B Then |
< | ããå°ãã | If A < B Then |
>= | ä»¥äž | If A >= B Then |
<= | ä»¥äž | If A <= B Then |
åºæ¬æ§æ
If æ¡ä»¶ Then
' æ¡ä»¶ãçã®å Žåã«å®è¡ããåŠç
End If
ã³ãŒãäŸ
Dim num As Long
num = 10
If num > 5 Then
MsgBox "numã¯5ãã倧ããã§ã"
End If
ãã®äŸã§ã¯ãå€æ° num
ã5ãã倧ããå Žåãã¡ãã»ãŒãžããã¯ã¹ã衚瀺ãããŸããã5ããå°ããå Žåã¯ãäœã衚瀺ãããŸããã
If...Then...Elseã¹ããŒãã¡ã³ã
æ¡ä»¶ãçã®å ŽåãšåœïŒFalseïŒã®å Žåã§ãç°ãªãåŠçãå®è¡ã§ããŸãã
å
çšã®äŸã§ãããšã5ããå°ããå Žåããã¡ãã»ãŒãžã衚瀺ãããå Žåã«äœ¿çšããŸãã
If æ¡ä»¶ Then
' æ¡ä»¶ãçã®å Žåã«å®è¡ããåŠç
Else
' æ¡ä»¶ãåœã®å Žåã«å®è¡ããåŠç
End If
ã³ãŒãäŸ
Dim num As Integer
num = 3
If num > 5 Then
MsgBox "numã¯5ãã倧ããã§ã"
Else
MsgBox "numã¯5以äžã§ã"
End If
ãã®äŸã§ã¯ãå€æ° num
ã5ãã倧ããå Žå㯠ãnumã¯5ãã倧ããã§ãããããã§ãªãå Žå㯠ãnumã¯5以äžã§ã"ããšããã¡ãã»ãŒãžããã¯ã¹ã衚瀺ãããŸãã
If...Then...ElseIf...Elseã¹ããŒãã¡ã³ã
è€æ°ã®æ¡ä»¶ãé ã«è©äŸ¡ããæåã«çãšãªãæ¡ä»¶ã«å¯Ÿå¿ããåŠçãå®è¡ããŸãã
If æ¡ä»¶1 Then
' æ¡ä»¶1ãçã®å Žåã«å®è¡ããåŠç
ElseIf æ¡ä»¶2 Then
' æ¡ä»¶2ãçã®å Žåã«å®è¡ããåŠç
Else
' ã©ã®æ¡ä»¶ãçã§ãªãå Žåã«å®è¡ããåŠç
End If
ã³ãŒãäŸ
Dim ã¹ã³ã¢ As Long
ã¹ã³ã¢ = Range("A1").Value ' ã»ã«A1ã«å
¥åãããå€ãååŸ
' ã¹ã³ã¢ã«åºã¥ããŠè©äŸ¡ã衚瀺
If ã¹ã³ã¢ >= 80 Then
MsgBox "åª"
ElseIf ã¹ã³ã¢ >= 60 Then
MsgBox "å¯"
Else
MsgBox "åŠ"
End If
ãã®äŸã§ã¯ãå€æ° ã¹ã³ã¢
ã®å€ã«å¿ããŠã"åª"ã"å¯"ã"åŠ" ã®ããããã®ã¡ãã»ãŒãžããã¯ã¹ã衚瀺ãããŸãã
ãŸããããã«æ¡ä»¶ãå¢ããããå Žåã¯ããã®æ¡ä»¶ã®æ°ã ã ElseIf
ãèšèŒããŸãã
Dim ã¹ã³ã¢ As Long
ã¹ã³ã¢ = Range("A1").Value ' ã»ã«A1ã«å
¥åãããå€ãååŸ
' ã¹ã³ã¢ã«åºã¥ããŠè©äŸ¡ã衚瀺
If ã¹ã³ã¢ >= 80 Then
MsgBox "åª"
ElseIf ã¹ã³ã¢ >= 70 Then
MsgBox "è¯"
ElseIf ã¹ã³ã¢ >= 60 Then
MsgBox "å¯"
Else
MsgBox "åŠ"
End If
ãã¹ããããIfæ
ãã¹ãããã If
æãšã¯ãIf
æã®äžã«ããã«å¥ã® If
æãå«ããããšã§ãããããããã¹ããããIfæãããå
¥ãåã®IfæããšåŒã³ãŸããè€æ°ã®æ¡ä»¶ãçµã¿åãããŠããã现ããæ¡ä»¶åå²ãäœãããšãã§ããŸããäŸãã°ãããŸãç¹å®ã®æ¡ä»¶Aãæãç«ã€å Žåã«ãããã«æ¡ä»¶Bãæãç«ã€ãããšãããããªå Žåã«äœ¿ããŸãã
' æåã®æ¡ä»¶ïŒããæ¡ä»¶1ãçãªã
If æ¡ä»¶1 Then
' æ¡ä»¶1ãçã®å Žåã«å®è¡ãããåŠç
If æ¡ä»¶2 Then
' æ¡ä»¶1ãšæ¡ä»¶2ãäž¡æ¹ãšãçã®å Žåã«å®è¡ãããåŠç
' æ¡ä»¶2ãåœã®å Žå
Else
'æ¡ä»¶1ã¯çã ãæ¡ä»¶2ãåœã®å Žåã«å®è¡ãããåŠç
End If
'æ¡ä»¶1ãåœã®å Žå
Else
'æ¡ä»¶1ãåœã®å Žåã«å®è¡ãããåŠç
End If
ã³ãŒãäŸ
Dim 幎霢 As Long
Dim æ§å¥ As String
幎霢 = Range("A1").Value '幎霢ãã»ã«A1ããååŸ
æ§å¥ = Range("B1").Value 'æ§å¥ãã»ã«B1ããååŸ
If æ§å¥ = "ç·æ§" Then
' æåã®æ¡ä»¶ïŒæ§å¥ãç·æ§ã®å Žå
If 幎霢 >= 20 Then
' 2ã€ç®ã®æ¡ä»¶ïŒ20æ³ä»¥äžã®å Žå
MsgBox "æ人ç·æ§ã§ã"
Else
' 2ã€ç®ã®æ¡ä»¶ïŒ20æ³æªæºã®å Žå
MsgBox "æªæ幎ç·æ§ã§ã"
End If
End If
ãã®äŸã§ã¯ãå€æ° æ§å¥
ããç·æ§ããã©ããããã§ãã¯ããŸãããããç·æ§ããªããããã«ãå€æ° 幎霢
ã20以äžãã©ããããã§ãã¯ããæ人ç·æ§ã§ãããšè¡šç€ºããŸãããã20æªæºãªããæªæ幎ç·æ§ã§ãããšè¡šç€ºããŸãã
è€åæ¡ä»¶
è€æ°ã®æ¡ä»¶ãçµã¿åãããŠãããè€éãªæ¡ä»¶å€æãè¡ãããšãã§ããŸããããã«ã¯ãAndïŒãã€ïŒãOrïŒãŸãã¯ïŒãNotïŒïœã§ãªãïŒãšãã£ãè«çæŒç®åã䜿ããŸãã
-
And
: ãã¹ãŠã®æ¡ä»¶ãçã®å Žåã«çãšãªã -
Or
: ã©ãã1ã€ã®æ¡ä»¶ãçã®å Žåã«çãšãªã -
Not
: æ¡ä»¶ã®çµæãå転ããã
If æ¡ä»¶A And æ¡ä»¶B Then
' äž¡æ¹ã®æ¡ä»¶ãçã®å Žåã«å®è¡ããåŠç
End If
If æ¡ä»¶A Or æ¡ä»¶B Then
' ã©ã¡ããã®æ¡ä»¶ãçã®å Žåã«å®è¡ããåŠç
End If
If Not æ¡ä»¶A Then
' æ¡ä»¶Aãåœã®å Žåã«å®è¡ããåŠç
End If
ã³ãŒãäŸ
Dim æ°å€A As Long
Dim æ°å€B As Long
' äŸãšããŠæ°å€ãèšå®
æ°å€A = 10
æ°å€B = 20
' æ°å€Aã5ãã倧ããããã€æ°å€Bã15ããå°ãã
If æ°å€A > 5 And æ°å€B < 15 Then
' æ°å€Aã¯5ãã倧ããããæ°å€Bã¯15ãã倧ããã®ã§ãäžèšã®ã¡ãã»ãŒãžã¯ã衚瀺ãããªã
MsgBox "æ°å€Aã¯5ãã倧ããããã€æ°å€Bã¯15ããå°ãã"
End If
' æ°å€Aã5ãã倧ããããŸãã¯æ°å€Bã15ããå°ãã
If æ°å€A > 5 Or æ°å€B < 15 Then
' æ°å€Aã¯5ãã倧ãããããæ°å€Bã¯15ãã倧ããããäžèšã®ã¡ãã»ãŒãžã¯ã衚瀺ããã
MsgBox "æ°å€Aã¯5ãã倧ããããæ°å€Bã¯15ããå°ãã"
End If
' æ°å€Aã15ãã倧ãããªãïŒæ°å€Aã15以äžïŒ
If Not æ°å€A > 15 Then
' æ°å€Aã¯15以äžã®ãããäžèšã®ã¡ãã»ãŒãžã¯ã衚瀺ããã
MsgBox "æ°å€Aã¯15以äžã§ã"
End If
ããå®è·µçãªè€åæ¡ä»¶ã®äŸ
Dim 幎霢 As Long
Dim æ§å¥ As String
Dim äŒå¡ As Boolean
' ã»ã«ã«å
¥åãããå€ãååŸ
幎霢 = Range("A1").Value
æ§å¥ = Range("B1").Value
äŒå¡ = Range("C1").Value
' 女æ§ã§65æ³ä»¥äžããŸãã¯äŒå¡ã§ããå Žåã«å²åŒ
If (æ§å¥ = "女æ§" And 幎霢 >= 65) Or äŒå¡ = True Then
MsgBox "å²åŒå¯Ÿè±¡ã§ã"
End If
Ifæã®ãã€ã³ã
- å¿
èŠã«å¿ããŠ
ElseIf
ãElse
ãè¿œå ããŠæ¡ä»¶ãå¢ããããšãã§ãã - æ¡ä»¶ãè€æ°ããå Žåã¯ãäžããé ã«æ¡ä»¶ãè©äŸ¡ãããæåã«æºããæ¡ä»¶ãèŠã€ãã£ãæç¹ã§ãã®åå²ã«å ¥ã
- Ifæã¯ãäœéã«ããã¹ãïŒå ¥ãåïŒã«ã§ãã
- ããŸãæ·±ããã¹ããããšã³ãŒããèªã¿ã«ãããªãããã泚æãå¿ èŠ
-
And
ãOr
ãNot
ã䜿ãããšã§ãããè€éãªæ¡ä»¶åå²ãå¯èœã«ãªã - æ¡ä»¶ãçµã¿åãããããšã§ããã现ããæ¡ä»¶ã«å¯Ÿå¿ã§ãã
- æ¡ä»¶ãå€ããªãããããšåããã«ãããªãã®ã§ãå¿ èŠã«å¿ããŠåå²ããããšããå§ã
Select Case
Select Case
ã¯ã1ã€ã®åŒãå€æ°ãåãåŸãè€æ°ã®å€ã«å¿ããŠãç°ãªãåŠçãè¡ãå Žåã«äœ¿çšããŸããIf-ElseIf
ãå€çšããå Žåã®ä»£æ¿ãšããŠæå¹ã§ããïŒã³ãŒããããç°¡æœã«ããããšãã§ããïŒ
åºæ¬æ§æ
Select Case å€æ°
Case å€1
' å€1ã®å Žåã®åŠç
Case å€2
' å€2ã®å Žåã®åŠç
Case Else
' ãã®ä»ã®å Žåã®åŠç
End Select
ã³ãŒãäŸ
' ææ¥ã®æ°å€ãæ ŒçŽããå€æ°
Dim dayOfWeek As Long
' äŸãšããŠæ°å€ãèšå®
dayOfWeek = 3 ' 1:æ¥ææ¥, 2:æææ¥, ..., 7:åææ¥
' ææ¥ã®æ°å€ã«å¿ããŠå¯Ÿå¿ããææ¥åã衚瀺
Select Case dayOfWeek
Case 1
MsgBox "æ¥ææ¥"
Case 2
MsgBox "æææ¥"
Case 3
MsgBox "ç«ææ¥"
Case 4
MsgBox "æ°Žææ¥"
Case 5
MsgBox "æšææ¥"
Case 6
MsgBox "éææ¥"
Case 7
MsgBox "åææ¥"
' 1ãã7以å€ã®å€ã®å Žå
Case Else
MsgBox "ç¡å¹ãªå€ã§ã"
End Select
ãã®äŸã§ã¯ãdayOfWeek
ã®å€ã«å¿ããŠãææ¥ã衚瀺ããã¡ãã»ãŒãžããã¯ã¹ã衚瀺ãããŸãã
è€æ°ã®å€ãæå®ããå Žå
1ã€ã®æ¡ä»¶ã«å¯ŸããŠè€æ°ã®å€ãæå®ãããå Žåã¯ãã«ã³ãïŒ ,
ïŒã§åºåã£ãŠèšè¿°ããŸãã
' æ瞟ãæ ŒçŽããå€æ°
Dim grade As String
' äŸãšããŠAã代å
¥
grade = "A"
' æ瞟ã«å¿ããŠå€å®çµæã衚瀺
Select Case grade
Case "A", "B", "C" ' AãBãCã®ããããã®å Žå
MsgBox "åæ Œ"
Case "D", "E" ' DãŸãã¯Eã®å Žå
MsgBox "åè©Šéš"
Case Else ' äžèšä»¥å€ã®å Žå
MsgBox "äžåæ Œ"
End Select
ãã®äŸã§ã¯ãgrade
ã"A"ã"B"ã"C"ã®ããããã®å Žåã«"åæ Œ"ã"D"ã"E"ã®ããããã®å Žåã«"åè©Šéš"ããã以å€ã®å Žåã«"äžåæ Œ"ãšè¡šç€ºãããŸãã
ç¯å²ã§å€ãæå®ããå Žå
æ°å€ã®ç¯å²ã§ã±ãŒã¹ãæå®ããå Žå㯠To
æŒç®åã䜿çšããŸãã
' ç¹æ°ãæ ŒçŽããå€æ°
Dim score As Long
' äŸãšããŠ85ç¹ã代å
¥
score = 85
' ç¹æ°ã®ç¯å²ã«å¿ããŠè©äŸ¡ã衚瀺
Select Case score
Case 90 To 100 ' 90ãã100ãŸã§ã®å Žå
MsgBox "S"
Case 80 To 89 ' 80ãã89ãŸã§ã®å Žå
MsgBox "A"
Case 70 To 79 ' 70ãã79ãŸã§ã®å Žå
MsgBox "B"
Case Else
MsgBox "C"
End Select
ãã®äŸã§ã¯ãscore
ã®å€ã90以äž100以äžã®å Žåã¯ãSãã80以äž89以äžã®å Žåã¯ãAãã70以äž79以äžã®å Žåã¯ãBãããã以å€ã®å Žåã¯ãCããšè¡šç€ºããŸãã
æ°å€ã®å€§å°ãæ¯èŒãããå Žå
æ°å€ã®å€§å°æ¯èŒãè¡ãå Žå㯠Is
æŒç®åãšæ¯èŒæŒç®åãçµã¿åãããŠäœ¿çšããŸãã
' ã¹ã³ã¢ãæ ŒçŽããå€æ°
Dim ã¹ã³ã¢ As Long
' ã»ã«A1ã«å
¥åãããå€ãååŸ
ã¹ã³ã¢ = Range("A1").Value
' ã¹ã³ã¢ã«åºã¥ããŠè©äŸ¡ã衚瀺
Select Case ã¹ã³ã¢
Case Is >= 80
MsgBox "åª"
Case Is >= 70
MsgBox "è¯"
Case Is >= 60
MsgBox "å¯"
Case Else
MsgBox "åŠ"
End Select
ãã®äŸã§ã¯ãå€æ° ã¹ã³ã¢
ã®å€ã«å¿ããŠã"åª"ã"è¯"ã"å¯"ã"åŠ" ã®ããããã®ã¡ãã»ãŒãžããã¯ã¹ã衚瀺ãããŸãã
ãããŸãæ€çŽ¢ãããå Žå
æååã®ãã¿ãŒã³ãããã³ã°ïŒæååãç¹å®ã®ãã¿ãŒã³ã«äžèŽãããïŒãè¡ãå Žå㯠Like
æŒç®åãšã¯ã€ã«ãã«ãŒãïŒ*ã?ïŒã䜿çšããŸãã
ã¯ã€ã«ãã«ãŒãã«ã€ããŠ
ã¯ã€ã«ãã«ãŒããšã¯ãæååæ€çŽ¢ããã¿ãŒã³ãããã³ã°ã§äœ¿çšããç¹æ®æåã®ããšã§ãã
-
*
ïŒ0æå以äžã®ä»»æã®æåå -
?
ïŒä»»æã®1æå -
#
ïŒä»»æã®1æ¡ã®æ°åïŒ0-9ïŒ - [æåãªã¹ã]ïŒæå®ããæåã®ãããã1æåãšäžèŽ
- [A-Z]ïŒAããZãŸã§ã®ä»»æã®1æå
- [0-9]ïŒ0ãã9ãŸã§ã®ä»»æã®1æå
- [!æåãªã¹ã]ïŒæå®ããæå以å€ã®ãããã1æåãšäžèŽ
ãã詳ããæ å ±ã¯ãMicrosoftå ¬åŒããã¥ã¡ã³ãã®æååã®æ¯èŒã§äœ¿çšãããã¯ã€ã«ãã«ãŒãæåããåç §ãã ããã
ã¯ã€ã«ãã«ãŒãã®äœ¿çšäŸ
䜿çšäŸ | æå³ïŒè©²åœäŸïŒ |
---|---|
*è±* |
ãè±ããå«ãæååïŒè±æãè±ç¶ãè±ç«ãæ¡ã®è±ãè±ããå£åïŒ |
è±* |
ãè±ãã§å§ãŸãæååïŒè±æãè±ç¶ãè±ç«ïŒ |
è±? |
ãè±ãã§å§ãŸã2æåã®æååïŒè±æïŒ |
*è± |
ãè±ãã§çµããæååïŒè±ç«ãæ¡ã®è±ïŒ |
???è± |
ãè±ãã§çµãã4æåã®æååïŒæ¡ã®è±ïŒ |
è±#æ¬ |
ãè±ãã®åŸã«1æ¡ã®æ°åããã®åŸã«ãæ¬ããç¶ãæååïŒè±1æ¬ãè±2æ¬ãªã©ïŒ |
è±[å寺] |
ãè±åããè±å¯ºãã®ãããããšäžèŽ |
è±[!å寺] |
ãè±åããè±å¯ºã以å€ã§ãè±ãã§å§ãŸã2æåã®æååãšäžèŽ |
[æ¥å€ç§å¬]è± |
ãæ¥è±ããå€è±ããç§è±ããå¬è±ãã®ãããããšäžèŽ |
[!æ¥å€ç§å¬]è± |
ãæ¥è±ããå€è±ããç§è±ããå¬è±ã以å€ã§ãè±ãã§çµãã2æåã®æååãšäžèŽ |
202[0-3]-[0-1]#-* |
2020幎ãã2023幎ã®éã§ãæã01æãã12æã®éã§ãä»»æã®å¹Žææ¥ïŒäŸïŒ2021-04-01ïŒ |
ã¯ã€ã«ãã«ãŒã䜿çšæã®ãã€ã³ã
è€éãªã¯ã€ã«ãã«ãŒãã®çµã¿åããã¯æ··ä¹±ã®åå ãšãªããããå¿ èŠæå°éã®äœ¿çšãå¿ãããŸãããããŸããé »ç¹ã«äœ¿çšãããã¿ãŒã³ã¯å€æ°ãå®æ°ãšããŠå®çŸ©ããŠãããšãã³ãŒãã®å¯èªæ§ãåäžããŸãã
ã³ãŒãäŸ
' ãã¡ã€ã«åãæ€çŽ¢ããããã®å€æ°ã宣èš
Dim fileName As String
' æ€çŽ¢ããããã¡ã€ã«åãèšå®
fileName = "Test123.txt"
' ãã¡ã€ã«åã®ãã¿ãŒã³ããã§ãã¯
Select Case fileName
Case Like "Test*" ' Testã§å§ãŸããã¡ã€ã«åããã§ãã¯
MsgBox "ãã¡ã€ã«åã¯Testã§å§ãŸããŸã"
Case Like "*.txt" ' æ¡åŒµåã.txtããã§ãã¯
MsgBox "ããã¹ããã¡ã€ã«ã§ã"
Case Like "???##*" ' 3æå+2æ¡ã®æ°åã§å§ãŸããã¡ã€ã«åããã§ãã¯
MsgBox "3æå+2æ¡ã®æ°åã§å§ãŸããã¡ã€ã«ã§ã"
Case Like "*[A-Z]*" ' 倧æåã®ã¢ã«ãã¡ããããå«ãããã§ãã¯
MsgBox "倧æåã®ã¢ã«ãã¡ããããå«ããã¡ã€ã«ã§ã"
Case Else ' äžèšã®ã©ã®ãã¿ãŒã³ã«ãäžèŽããªãå Žå
MsgBox "ã©ã®ãã¿ãŒã³ã«ãäžèŽããŸããã§ãã"
End Select
Select Caseã®ãã€ã³ã
- å
Case
ã¯éžæããåºæºã§ãã - è€æ°ã®å€ãã³ã³ãã§åºåã£ãŠ1ã€ã®
Case
ã«å«ããããšãå¯èœ - æ°å€ã®ç¯å²ã¯
To
ã§æå® - æ¯èŒæ¡ä»¶ã¯
Is
ã§æå® - æååã®ãããŸãæ€çŽ¢ïŒãã¿ãŒã³ãããã³ã°ïŒã¯
Like
ã§æå® -
Case Else
ã¯ãå šãŠã®Case
ã«è©²åœããªãå Žåã®åŠçãæå®
Select Caseæ䜿çšæã®æ³šæç¹
- æ¯èŒããå€ã®åã¯çµ±äžãã
â» æååãšæ°å€ãæ··åšããããšäºæãã¬åäœã®åå ã«ïŒ - Case Elseã¯å¯èœãªéãå®è£
ãã
â» æ³å®å€ã®å€ãžã®å¯Ÿå¿ãå¿ããã«ïŒ - è€æ°ã®å€æ°ãåæã«æ¡ä»¶åå²ããããšã¯ãå¯èªæ§ã®èŠ³ç¹ããéæšå¥šïŒ
â» ã幎霢ã20æ³ä»¥äžããã€ãæ§å¥ãç·æ§ãã®ãããªè€æ°æ¡ä»¶ã®æå®ã¯Ifæã䜿çšïŒ
æ¡ä»¶åå²ã®äœ¿ãåã
If
æãš Select Case
æã¯ã©ã¡ããæ¡ä»¶åå²ã«äœ¿çšã§ããŸãããããããåŸæãªå Žé¢ãç°ãªããŸãã
Ifæ | Select Caseæ | |
---|---|---|
æ¡ä»¶ã®æ° | è€éãªæ¡ä»¶ãè€æ°ã®æ¡ä»¶ãçµã¿åãããå Žåã«é©ããŠãã | åçŽãªæ¡ä»¶ã1ã€ã®å€æ°ã«å¯Ÿããè€æ°ã®éžæè¢ãããå Žåã«é©ããŠãã |
ã³ãŒãã®ç°¡æœã | æ¡ä»¶ãè€éã«ãªããšã³ãŒããåé·ã«ãªãããã | åçŽãªæ¡ä»¶åå²ã§ã¯ã³ãŒããç°¡æœã«ãªã |
å ·äœçãªäŸ
-
Ifæãé©ããŠããäŸ
- è€æ°ã®æ¡ä»¶ãçµã¿åãããŠå€å®ããå ŽåïŒäŸïŒå¹Žéœ¢ã20æ³ä»¥äžã§ããã€æ§å¥ãç·æ§ã®å ŽåïŒ
- æ¡ä»¶ãè€éã§ã
Select Case
æã§ã¯è¡šçŸãã«ããå Žå
' â Ifæã§ã®å®è£ ïŒæšå¥šïŒ ' ãçç±ã ' - è€æ°ã®æ¡ä»¶ïŒå¹Žéœ¢ãšæ§å¥ïŒãçµã¿åãããå€å®ã«é©ããŠãã ' - ã³ãŒãã®æå³ãåããããã If 幎霢 >= 20 And æ§å¥ = "ç·æ§" Then MsgBox "æ人ç·æ§ã§ã" ElseIf 幎霢 >= 20 And æ§å¥ = "女æ§" Then MsgBox "æ人女æ§ã§ã" Else MsgBox "æªæ幎ã§ã" End If ' â Select Caseæã§ã®å®è£ ïŒéæšå¥šïŒ ' ãçç±ã ' - åäžã®å€æ°ã®å€ãå ã«ããå€å®ãåºæ¬ã®ãããåå¿è ããããã«ãã ' - ã³ãŒãã®æå³ãåããã«ãã (â è©³çŽ°ïŒ ' Select Case True ã䜿ã£ãŠè€éãªè«çå€å®ãè¡ããšãIfæãšå€§å·®ãªããªãã ' æžãæ¹ãç解ã®èŠ³ç¹ããåé·ã«ãªã£ãŠããŸãããã Select Case True ' åé·ãªã³ãŒã Case 幎霢 >= 20 And æ§å¥ = "ç·æ§" MsgBox "æ人ç·æ§ã§ã" Case 幎霢 >= 20 And æ§å¥ = "女æ§" MsgBox "æ人女æ§ã§ã" Case Else MsgBox "æªæ幎ã§ã" End Select
-
Select Caseæãé©ããŠããäŸ
- 1ã€ã®å€æ°ã®å€ã«ãã£ãŠè€æ°ã®åŠçã«åå²ããå ŽåïŒäŸïŒææ¥ã«ãã£ãŠç°ãªãåŠçãè¡ãå ŽåïŒ
- æ¡ä»¶ãåçŽã§ã
Select Case
æã§è¡šçŸããããå Žå
' â Select Caseæã§ã®å®è£ ïŒæšå¥šïŒ ' ãçç±ã ' - ã³ãŒããç°¡æœã§èªã¿ããã ' - æ¡ä»¶ã®è¿œå ã»å€æŽã容æ ' - ãããã°ãç°¡å Select Case ææ¥ Case "æ", "ç«", "æ°Ž", "æš", "é" MsgBox "å¹³æ¥ã§ã" Case "å", "æ¥" MsgBox "äŒæ¥ã§ã" Case Else MsgBox "ç¡å¹ãªææ¥ã§ã" End Select ' â Ifæã§ã®å®è£ ïŒéæšå¥šïŒ ' ãçç±ã ' - åãå€æ°ïŒææ¥ïŒãäœåºŠãæžãå¿ èŠããã ' - ã³ãŒããé·ããªã ' - æ¡ä»¶ãè¿œå ããéã«ãã¹ãèµ·ãããã If ææ¥ = "æ" Or ææ¥ = "ç«" Or ææ¥ = "æ°Ž" Or ææ¥ = "æš" Or ææ¥ = "é" Then MsgBox "å¹³æ¥ã§ã" ElseIf ææ¥ = "å" Or ææ¥ = "æ¥" Then MsgBox "äŒæ¥ã§ã" Else MsgBox "ç¡å¹ãªææ¥ã§ã" End If
ç¶æ³ã«å¿ããŠé©åãªæ¡ä»¶åå²ã䜿ãåããããšã§ãããå¹ççã§å¯èªæ§ã®é«ãVBAããã°ã©ã ãäœæããããšãã§ããŸãã
ãŸãšã
æ¡ä»¶åå²ã¯ãVBAããã°ã©ãã³ã°ã«ãããŠåŠçã®æµããå¶åŸ¡ããéèŠãªèŠçŽ ã§ããIf
æãšSelect Case
æã¯ãããããã®ç¹åŸŽã掻ãããŠäœ¿ãåããããšã§ãããå¹ççã§ä¿å®æ§ã®é«ãããã°ã©ã ãäœæããããšãã§ããŸããç¹ã«ãè€éãªæ¡ä»¶å€æã«ã¯If
æããåäžã®å€æ°ã«åºã¥ãè€æ°ã®åå²ã«ã¯Select Case
æã䜿çšãããšãã䜿ãåããæèããããšã§ãããèªã¿ããããã¡ã³ããã³ã¹ããããã³ãŒããå®çŸã§ããŸãããŸãããšã©ãŒåŠçãæ³å®å€ã®å
¥åãžã®å¯Ÿå¿ãšããŠãElse
ãCase Else
ãé©åã«å®è£
ããããšã§ãããã°ã©ã ã®ä¿¡é Œæ§ãé«ããããšãã§ããŸãã
ããèšäºã®å 容ã§äžæãªç¹ãããã詳ããç¥ãããéšåããããŸããããã³ã¡ã³ãã§ãç¥ãããã ããããŸããå®åã§ã®æ¡ä»¶åå²ã®æŽ»çšäŸããããå¹ççãªå®è£ æ¹æ³ãªã©ãçæ§ã®ããŠããŠããã²å ±æããŠããã ããã°å¹žãã§ãã
次åã¯ãVBAã«ãããã«ãŒãåŠçïŒForæãDoæïŒã«ã€ããŠè©³ãã解説ããäºå®ã§ããç¹°ãè¿ãåŠçãç解ããããšã§ãããå¹ççãªèªååããã°ã©ã ãäœæã§ããããã«ãªããŸããã©ããã楜ãã¿ã«ïŒ