Excel VBAã§å¯èŠã»ã«ã掻çšãããã£ã«ã¿ãŒæäœãã¯ããã¯
ç§ã¯VBAã®æŽ»çšçµéšãéããŠåŸãç¥èãæŽçããå ±æããç®çã§èšäºãäœæããŠããããã°ã©ãã³ã°æŽ1幎åã«ãªããšã³ãžãã¢ã§ããååã¯ãéžæããç¯å²ã«å¯ŸããŠäžè¡ããã«ç©ºè¡ãæ¿å ¥ããå®çšçãªãã¯ãã«ã€ããŠè©³ãã説æããŸãããä»åã¯ãå¯èŠã»ã«ïŒè¡šç€ºãããŠããã»ã«ïŒã掻çšããé«åºŠãªãã£ã«ã¿ãŒæäœã«ã€ããŠè§£èª¬ããŸãã
- 第1å: Excel VBAã®åºç€ç¥èãšã»ãã¥ãªãã£èšå®
- 第2å: Excel VBAã®åºæ¬æäœãšãªããžã§ã¯ãã®çè§£
- 第3å: Excel VBAã«ããã倿°ãšå®æ°ã®åºæ¬
- 第4å: Excel VBAã«ãããã·ãŒãæäœã®åºæ¬ãšãšã©ãŒåŠç
- 第5å: Excel VBAã«ãããæ¡ä»¶åå²
- 第6å: Excel VBAã«ãããç¹°ãè¿ãåŠçã®åºæ¬
- 第7å: Excel VBAã«ãããé åãšFor Eachã®æŽ»çš
- 第8å: Excel VBAã«ãããFormulaãšValueã®äœ¿ãåããšãŠãŒã¶ãŒå ¥åã®ååŸ
- 第9å: Excel VBAã«ããããã¡ã€ã«æäœãšãã©ã«ã管çã®åºæ¬
- 第10å: Excel VBAã«ãããFileSystemObjectãæŽ»çšããé«åºŠãªãã¡ã€ã«æäœ
- 第11å: Excel VBAã«ãããFileSystemObjectãæŽ»çšããé«åºŠãªãã¡ã€ã«æäœ å¿çšç·š
- 第12å: Excel VBAã«ãããStrConv颿°ã®æŽ»çšãšå¿çšãã¯ããã¯
- 第13å: Excel VBAã«ãããã¯ãŒã¯ããã¯ã®å®å šãªæäœãšç®¡çãã¯ããã¯
- 第14å: Excel VBAã«ãããFunctionïŒé¢æ°ïŒã®äœæãšæŽ»çšãã¯ããã¯
- 第15å: Excel VBAã«ãããé åãè¿ã颿°ã®äœæãšæŽ»çšãã¯ããã¯
- 第16å: Excel VBAã«ãããã³ã¬ã¯ã·ã§ã³ã®æŽ»çšãšå¿çšãã¯ããã¯
- 第17å: Excel VBAã«ãããèŸæžåïŒDictionaryïŒã®æŽ»çšãšå¿çšãã¯ããã¯
- 第18å: Excel VBAã«ãããEnumåãæŽ»çšãã颿°èšèšãšå®è£ ãã¯ããã¯
- 第19å: Excel VBAã«ããããŠãŒãã£ãªãã£é¢æ°ã®äœæãšæŽ»çšãã¯ããã¯
- 第20å: Excel VBAã«ãããæ£èŠè¡šçŸã掻çšãããã¡ã€ã«åè§£æãã¯ããã¯
- 第21å: Excel VBAã§ç¯å²å ã®å³åœ¢ãå¹ççã«åé€ãããã¯ããã¯
- 第22å: Excel VBAã§ææ°ãã¡ã€ã«ãå¹ççã«æ€çŽ¢ãã颿°èšèšãã¯ããã¯
- 第23å: Excel VBAã§éžæããç¯å²ã«å¯ŸããŠãäžè¡ããã«ç©ºè¡ãæ¿å ¥ãããã¯ããã¯
- 第24å: Excel VBAã§å¯èŠã»ã«ã掻çšãããã£ã«ã¿ãŒæäœãã¯ããã¯ïŒæ¬èšäºïŒ
- 第25å: Excel VBAã§å¯èŠã»ã«ã®ã¿ãå¹ççã«ã³ããŒãããã¯ããã¯
- 第26å: Excel VBAã«ããããã¡ã€ã«ã»ãã©ã«ãç§»åã®ååž°åŠçãã¯ããã¯
- 第27å: Excel VBAã«ããã芪ãã©ã«ããã¹ååŸã®å®è£ ãã¯ããã¯
- 第28å: Excel VBAã«ãããç¬èªã€ãã³ãã®èšèšãšå®è£ ãã¯ããã¯
ç®æ¬¡
- ã¯ããã«
- å¯èŠã»ã«ãšãã£ã«ã¿ãŒæäœã®åºæ¬æŠå¿µ
- å¯èŠã»ã«ååŸã®å®è£
- åçãã£ã«ã¿ãŒäœæã®å®è£
- å®è·µçãªæŽ»çšäŸ
- ãšã©ãŒãã³ããªã³ã°ãšãã¹ããã©ã¯ãã£ã¹
- å®è£ æã®æ³šæç¹
- ãŸãšã
ã¯ããã«
Excelã§ããŒã¿åæãè¡ãéããã£ã«ã¿ãŒæ©èœã¯æ¬ ãããªãããŒã«ã§ããããããæåã§ãã£ã«ã¿ãŒæ¡ä»¶ãèšå®ããäœæ¥ã¯ãããŒã¿ã倧éã«ãªããšç ©éã«ãªããŸããç¹ã«ãçŸåšãã£ã«ã¿ãªã³ã°ãã衚瀺ãããŠããããŒã¿ïŒå¯èŠã»ã«ïŒã«åºã¥ããŠãåãèŠåºããããä»ã®ãã¡ã€ã«ã«åæ§ã®ãã£ã«ã¿ãŒãé©çšãããå Žåãäžã€ãã€æ¡ä»¶ãéžæããã®ã¯éå¹ççã§ãã
äŸãã°ã売äžããŒã¿ããç¹å®ã®æ¡ä»¶ã§ãã£ã«ã¿ãªã³ã°ããååIDããå¥ã®ãã¡ã€ã«ã®ååIDã«å¯ŸããŠãã£ã«ã¿ãŒãšããŠé©çšãããå ŽåããããŸããæåã§è¡ãå Žåããã£ã«ã¿ãªã³ã°çµæãšããŠè¡šç€ºãããæ°åããæ°çŸã®ååIDãäžã€ãã€ç¢ºèªããå¥ã®ãã¡ã€ã«ã§åãååIDãéžæããå¿ èŠããããŸãããã®äœæ¥ã¯æéããããã ãã§ãªããéžææŒããééããçºçãããããããŒã¿ã®æŽåæ§ã«ã圱é¿ãäžããå¯èœæ§ããããŸãã
ãã®ãããªå Žé¢ã§ãå¯èŠã»ã«ã®å€ãèªåçã«ååŸãããããé åãšããŠä¿åããŠãã£ã«ã¿ãŒæ¡ä»¶ãšããŠæŽ»çšããããšã§ãäœæ¥å¹çãå€§å¹ ã«åäžãããããšãã§ããŸããããã«ããã®ææ³ã«ãã人çãã¹ãé²ããè€æ°ã®ãã¡ã€ã«éã§äžè²«ãããã£ã«ã¿ãªã³ã°æ¡ä»¶ãé©çšã§ããããã«ãªããŸãã
ä»å玹ä»ãããã¯ããã¯ã¯ãçŸåšè¡šç€ºãããŠããã»ã«ã®å€ãåçé
åïŒãµã€ãºãå¯å€ã®é
å
ïŒãšããŠååŸããããã䜿ã£ãŠä»ã®ã·ãŒãããã¡ã€ã«ã«å¯ŸããŠãã£ã«ã¿ãŒãäœæããæ¹æ³ã§ãããã®ææ³ããã¹ã¿ãŒããããšã§ãè€éãªããŒã¿åæäœæ¥ã®å¹çåã ãã§ãªããè€æ°ããŒã¿ãœãŒã¹éã®é£æºäœæ¥ã倧å¹
ã«æ¹åã§ããŸãã
å¯èŠã»ã«ãšãã£ã«ã¿ãŒæäœã®åºæ¬æŠå¿µ
å¯èŠã»ã«ãšã¯
å¯èŠã»ã«ãšã¯ããã£ã«ã¿ãŒãé©çšãããç¶æ ãè¡ãé衚瀺ã«ãªã£ãŠããç¶æ ã§ãå®éã«ç»é¢äžã«è¡šç€ºãããŠããã»ã«ã®ããšã§ãã
ãã£ã«ã¿ãŒæäœã®åºæ¬æ§é
VBAã§ã®ãã£ã«ã¿ãŒæäœã¯ã以äžã®èŠçŽ ããæ§æãããŸãã
èŠçŽ | 説æ | äŸ |
---|---|---|
Field | ãã£ã«ã¿ãŒãé©çšããåçªå· | 1ïŒAåïŒã2ïŒBåïŒ |
Criteria1 | ãã£ã«ã¿ãŒæ¡ä»¶ïŒå€ãŸãã¯é åïŒ | "åå001"ãArray("åå001", "åå002") |
Operator | ãã£ã«ã¿ãŒã®æŒç®å | xlFilterValuesïŒè€æ°å€ãã£ã«ã¿ãŒïŒ |
è€æ°ãã¡ã€ã«éã§ã®ãã£ã«ã¿ãŒé©çšã®æµã
å®éã®æ¥åã§ã¯ã以äžã®ãããªæµãã§ãã£ã«ã¿ãŒæ¡ä»¶ãä»ã®ãã¡ã€ã«ã«é©çšããŸãã
- å ãã¡ã€ã«ã§ã®ãã£ã«ã¿ãªã³ã°: 売äžããŒã¿ããç¹å®æ¡ä»¶ã§ååIDãçµã蟌ã¿
- å¯èŠã»ã«ã®ååŸ: ãã£ã«ã¿ãªã³ã°çµæãšããŠè¡šç€ºãããååIDãé åãšããŠååŸ
- 察象ãã¡ã€ã«ã§ã®ãã£ã«ã¿ãŒé©çš: ååŸããååIDé åãå¥ãã¡ã€ã«ã®ååIDåã«ãã£ã«ã¿ãŒãšããŠé©çš
- çµæã®ç¢ºèª: äž¡ãã¡ã€ã«ã§åãååIDã®ããŒã¿ã衚瀺ãããããšã確èª
å¯èŠã»ã«ååŸã®å®è£
åºæ¬çãªå¯èŠã»ã«ååŸåŠç
Sub SetVisibleArray()
' 衚瀺ãããŠããã»ã«ãååŸ
Dim visibleCells As Range
Set visibleCells = Selection.SpecialCells(xlCellTypeVisible)
' ã«ãŠã³ã¿å€æ°
Dim count As Long
count = 0
' ã«ãŒãçšã®ã»ã«ç¯å²å€æ°
Dim area As Range
' 衚瀺ã»ã«ã®æ°ãã«ãŠã³ã
For Each area In visibleCells.Areas
count = count + area.Cells.Count
Next area
' è€æ°æ€çŽ¢ã®æååé
åãæ ŒçŽãã倿°ã®åæ°ãèšå®
ReDim criteriaArray(1 To count)
' ã«ãŒãçšã®å€æ°
Dim i As Long, cell As Range
' é
åã«å€ãæååãšããŠæ ŒçŽ
i = 1
For Each area In visibleCells.Areas
For Each cell In area.Cells
i = i + 1
criteriaArray(i) = CStr(cell.Value)
Next cell
Next area
End Sub
åŠçã®è©³çŽ°è§£èª¬
1. å¯èŠã»ã«ã®ååŸ
Dim visibleCells As Range
Set visibleCells = Selection.SpecialCells(xlCellTypeVisible)
SpecialCells(xlCellTypeVisible)
ã«ãããéžæç¯å²å
ã§è¡šç€ºãããŠããã»ã«ã®ã¿ãååŸããŸãããã£ã«ã¿ãŒã§é衚瀺ã«ãªã£ãŠããã»ã«ã¯é€å€ãããŸãã
2. AreasæŠå¿µã®çè§£
å¯èŠã»ã«ã¯é£ç¶ããŠããªãå ŽåããããŸããäŸãã°ããã£ã«ã¿ãŒã«ãã£ãŠ1è¡ç®ã3è¡ç®ã5è¡ç®ã®ã¿ã衚瀺ãããŠããå Žåããããã¯3ã€ã®ç°ãªãããšãªã¢ããšããŠæ±ãããŸãã
3. åçé åã®äœæ
ReDim criteriaArray(1 To count)
ReDim
ïŒé
åã®åå®çŸ©
ïŒã«ãããå®éã®å¯èŠã»ã«æ°ã«åãããŠé
åãµã€ãºãåçã«èª¿æŽããŸããããã«ãããã¡ã¢ãªå¹çãåäžããŸãã
åçãã£ã«ã¿ãŒäœæã®å®è£
ãã£ã«ã¿ãŒé©çšåŠç
Sub SetVisibleFilter()
' ãŠãŒã¶ãŒã«ãã£ã«ã¿ãŒåçªå·ãå
¥åããã(Type:=1ã§æ°å€å
¥å)
Dim filterColumn As Long
filterColumn = Application.InputBox(prompt:="ãã£ã«ã¿ãŒãé©çšããåçªå·ãå
¥åããŠãã ãã", _
Title:="ãã£ã«ã¿ãŒåã®æå®", _
Type:=1)
' ãŠãŒã¶ãŒããã£ã³ã»ã«ããå Žåã®åŠç
If filterColumn = 0 Then
' ã¡ãã»ãŒãžã衚瀺ããåŠçãçµäºãã
MsgBox "ãã£ã«ã¿ãŒã®é©çšããã£ã³ã»ã«ããŸããã", vbInformation
Exit Sub
End If
' çŸåšé©çšãããŠãããã£ã«ã¿ãŒç¯å²ãé©çš
Dim filterTargetRange As Range
Set filterTargetRange = ActiveSheet.AutoFilter.Range
filterTargetRange.AutoFilter Field:=filterColumn, _
Criteria1:=criteriaArray, _
Operator:=xlFilterValues
End Sub
InputBoxã®æŽ»çš
filterColumn = Application.InputBox(prompt:="ãã£ã«ã¿ãŒãé©çšããåçªå·ãå
¥åããŠãã ãã", _
Title:="ãã£ã«ã¿ãŒåã®æå®", _
Type:=1)
Type:=1
ãæå®ããããšã§ãæ°å€ã®ã¿ã®å
¥åã匷å¶ã§ããŸããããã«ãããåçªå·ãšããŠäžé©åãªå€ã®å
¥åãé²ããŸãã
æ©æãªã¿ãŒã³ãã¿ãŒã³ã®æ¡çš
If filterColumn = 0 Then
MsgBox "ãã£ã«ã¿ãŒã®é©çšããã£ã³ã»ã«ããŸããã", vbInformation
Exit Sub
End If
ãŠãŒã¶ãŒããã£ã³ã»ã«ããå ŽåïŒæ»ãå€ã0ã®å Žå
ïŒã¯ãå³åº§ã«åŠçãçµäºããŸããããã«ãããåŸç¶ã®åŠçã§ãšã©ãŒãçºçããããšãé²ããŸãã
å®è·µçãªæŽ»çšäŸ
å®å šçã®å®è£
以äžã¯ããšã©ãŒãã³ããªã³ã°ãšãªãœãŒã¹ç®¡çãå«ãå®çšçãªããŒãžã§ã³ã§ãããã®ã³ãŒãã¯ãäžã€ã®ãã¡ã€ã«ã§ãã£ã«ã¿ãªã³ã°ããçµæããåããã¡ã€ã«å ã®å¥ã®åããä»ã®ãã¡ã€ã«ã®åãé ç®ã«å¯ŸããŠãã£ã«ã¿ãŒãšããŠé©çšããéã«äœ¿çšã§ããŸãã
' ã¢ãžã¥ãŒã«ã¬ãã«å€æ°ïŒè€æ°ã®ããã·ãŒãžã£éã§å
±æããé
åïŒ
Private criteriaArray() As String
' ã¡ã€ã³åŠçïŒå¯èŠã»ã«ãããã£ã«ã¿ãŒæ¡ä»¶ãäœæããæå®åã«ãã£ã«ã¿ãŒãé©çšãã
Sub CreateFilterFromVisibleCells()
' 倿°å®£èš
Dim visibleCells As Range
Dim filterColumn As Long
Dim isCompleted As Boolean
' åæå
isCompleted = False
' ãšã©ãŒãçºçããå Žåã®åŠçå
ãæå®
On Error GoTo Cleanup
' éžæç¯å²ã®æ€èšŒïŒè€æ°ã»ã«ãéžæãããŠããããã§ãã¯ïŒ
If Selection.Cells.Count = 1 Then
MsgBox "è€æ°ã®ã»ã«ãéžæããŠãããã¯ããå®è¡ããŠãã ããã", vbExclamation
GoTo Cleanup
End If
' åŠçéå§ã¡ãã»ãŒãž
Application.StatusBar = "å¯èŠã»ã«ãååŸäž..."
' ç»é¢æŽæ°ãäžæåæ¢
Application.ScreenUpdating = False
' å¯èŠã»ã«ã®ååŸãšé
åäœæ
Call ExtractVisibleCellsToArray
' ãã£ã«ã¿ãŒåã®ååŸ
filterColumn = Application.InputBox(prompt:="ãã£ã«ã¿ãŒãé©çšããåçªå·ãå
¥åããŠãã ãã", _
Title:="ãã£ã«ã¿ãŒåã®æå®", _
Type:=1)
' ãã£ã³ã»ã«æã®åŠç
If filterColumn = 0 Then
MsgBox "ãã£ã«ã¿ãŒã®é©çšããã£ã³ã»ã«ããŸããã", vbInformation
GoTo Cleanup
End If
' ãã£ã«ã¿ãŒã®é©çš
Call ApplyDynamicFilter(filterColumn)
' åŠçå®äºãã©ã°ãèšå®
isCompleted = True
Cleanup:
' ç»é¢æŽæ°ãå¿
ãåé
Application.ScreenUpdating = True
' ã¹ããŒã¿ã¹ããŒãã¯ãªã¢
Application.StatusBar = False
' åŠççµæã«å¿ããŠã¡ãã»ãŒãžã衚瀺
If Err.Number <> 0 Then
MsgBox "ãšã©ãŒãçºçããŸãã: " & Err.Description, vbCritical
ElseIf isCompleted Then
MsgBox "ãã£ã«ã¿ãŒã®é©çšãå®äºããŸããã", vbInformation
End If
' ãšã©ãŒæ
å ±ãã¯ãªã¢
Err.Clear
End Sub
' å¯èŠã»ã«ååŸåŠçïŒéžæç¯å²ã®è¡šç€ºãããŠããã»ã«ã®å€ãé
åã«æ ŒçŽãã
Private Sub ExtractVisibleCellsToArray()
' 衚瀺ãããŠããã»ã«ãååŸ
Dim visibleCells As Range
Set visibleCells = Selection.SpecialCells(xlCellTypeVisible)
' å¯èŠã»ã«æ°ã®ã«ãŠã³ã
Dim count As Long
count = 0
' ãšãªã¢ããšã«ã»ã«æ°ãã«ãŠã³ã
Dim area As Range
For Each area In visibleCells.Areas
count = count + area.Cells.Count
Next area
' é
åã®ãµã€ãºãåçã«èšå®
ReDim criteriaArray(1 To count)
' é
åã«å€ãæ ŒçŽ
Dim i As Long, cell As Range
i = 0
For Each area In visibleCells.Areas
For Each cell In area.Cells
i = i + 1
criteriaArray(i) = CStr(cell.Value)
Next cell
Next area
End Sub
' ãã£ã«ã¿ãŒé©çšåŠçïŒäœæããæ¡ä»¶é
åã䜿ã£ãŠæå®åã«ãã£ã«ã¿ãŒãèšå®ãã
Private Sub ApplyDynamicFilter(ByVal filterColumn As Long)
' çŸåšã®ãã£ã«ã¿ãŒç¯å²ãååŸ
Dim filterTargetRange As Range
Set filterTargetRange = ActiveSheet.AutoFilter.Range
' ãã£ã«ã¿ãŒãé©çš
filterTargetRange.AutoFilter Field:=filterColumn, _
Criteria1:=criteriaArray, _
Operator:=xlFilterValues
End Sub
ãšã©ãŒãã³ããªã³ã°ãšãã¹ããã©ã¯ãã£ã¹
1. ã¢ãžã¥ãŒã«ã¬ãã«å€æ°ã®æŽ»çš
Private criteriaArray() As String
é åãã¢ãžã¥ãŒã«ã¬ãã«ã§å®£èšããããšã§ãè€æ°ã®ããã·ãŒãžã£éã§ããŒã¿ãå ±æã§ããŸããããã«ãããå¯èŠã»ã«ååŸåŠçãšãã£ã«ã¿ãŒé©çšåŠçãåé¢ã§ããã³ãŒãã®ä¿å®æ§ãåäžããŸããç¹ã«ãç°ãªããã¡ã€ã«éã§ãã£ã«ã¿ãŒæ¡ä»¶ãåãæž¡ãããéã«æå¹ã§ãã
2. åŠçã®åå²èšèš
倧ããªåŠçã以äžã®ããã«åå²ããããšã§ãååŠçã®è²¬ä»»ãæç¢ºã«ãªããŸãããã®èšèšã«ãããåããã£ã«ã¿ãŒæ¡ä»¶ãè€æ°ã®ã·ãŒãããã¡ã€ã«ã«é©çšããéã®åå©çšæ§ãåäžããŸãã
- ExtractVisibleCellsToArray: å¯èŠã»ã«ã®ååŸãšé åäœæïŒå ãã¡ã€ã«ã§ã®åŠçïŒ
- ApplyDynamicFilter: ãã£ã«ã¿ãŒã®é©çšïŒå¯Ÿè±¡ãã¡ã€ã«ã§ã®åŠçïŒ
- CreateFilterFromVisibleCells: å šäœã®å¶åŸ¡ãšãšã©ãŒãã³ããªã³ã°
3. ãªãœãŒã¹ç®¡çãã¿ãŒã³
On Error GoTo Cleanup
' ... åŠçæ¬äœ ...
Cleanup:
Application.ScreenUpdating = True
Application.StatusBar = False
' ãã®ä»ã®ã¯ãªãŒã³ã¢ããåŠç
GoTo Cleanup
ãã¿ãŒã³ã«ãããæ£åžžçµäºã»ãšã©ãŒçµäºã«é¢ããããå¿
èŠãªãªãœãŒã¹ã®è§£æŸã確å®ã«å®è¡ãããŸãã
å®è£ æã®æ³šæç¹
1. ãã£ã«ã¿ãŒã®äºåé©çšã®ç¢ºèª
' AutoFilterãèšå®ãããŠãããã®ç¢ºèª
If ActiveSheet.AutoFilterMode = False Then
MsgBox "ãã£ã«ã¿ãŒãèšå®ãããŠããŸããã", vbExclamation
Exit Sub
End If
2. 倧éããŒã¿ã§ã®æ§èœèæ ®
倧éã®ããŒã¿ãæ±ãå Žåã¯ã以äžã®æé©åãæ€èšããŠãã ããã
- éè€å€ã®é€å»åŠç
- é åãµã€ãºã®äžéèšå®
- ã¡ã¢ãªäœ¿çšéã®ç£èŠ
3. ããŒã¿åã®çµ±äž
criteriaArray(i) = CStr(cell.Value)
CStr
颿°ã«ãããã»ã«ã®å€ãæååãšããŠçµ±äžããŠæ ŒçŽããŸããããã«ãããååIDãæ°å€åœ¢åŒãšæåå圢åŒã§æ··åšããŠããå Žåããç°ãªããã¡ã€ã«éã§ããŒã¿åœ¢åŒãçµ±äžãããŠããªãå Žåã§ãå®å
šã«åŠçã§ããŸãã
CStr颿°ã®éèŠæ§
Excelã®ã»ã«ã«ã¯æ°å€ãæååãæ¥ä»ãªã©æ§ã ãªããŒã¿åãæ ŒçŽãããŠããŸããç¹ã«ååIDãªã©ã®èå¥åã¯ããã¡ã€ã«ã«ãã£ãŠæ°å€åœ¢åŒïŒäŸïŒ1001ïŒãæåå圢åŒïŒäŸïŒ"P-1001"ïŒã§ç®¡çãããŠããå ŽåããããŸããCStr颿°ã«ãããããããçµ±äžçã«æååãšããŠæ±ãããšã§ãè€æ°ãã¡ã€ã«éã§ã®ãã£ã«ã¿ãŒé©çšæã®ãšã©ãŒãé²ããŸãã
å人çšãã¯ãããã¯ãžã®ä¿åãšã¯ã€ãã¯ã¢ã¯ã»ã¹ããŒã«ããŒã®æŽ»çš
å®éã®æ¥åã§ä»åã®ãã¯ããæŽ»çšããéã¯ãå人çšãã¯ãããã¯ïŒPERSONAL.XLSB
ïŒã«ä¿åããã¯ã€ãã¯ã¢ã¯ã»ã¹ããŒã«ããŒããåŒã³åºããããã«èšå®ããããšããå§ãããŸãããã®èšå®ã«ãããã©ã®Excelãã¡ã€ã«ãéããŠããæã§ããã¯ã³ã¯ãªãã¯ã§å¯èŠã»ã«ãã£ã«ã¿ãŒæ©èœã䜿çšã§ããããã«ãªããŸãã
å人çšãã¯ãããã¯ãšã¯
å人çšãã¯ãããã¯ã¯ãExcelãèªåçã«äœæããç¹å¥ãªãã¡ã€ã«ã§ãããã®ãã¡ã€ã«ã«ä¿åããããã¯ãã¯ãã©ã®Excelãã¡ã€ã«ãéããŠããæã§ã䜿çšã§ãããããæ±çšçãªããŒã«ãšããŠæŽ»çšã§ããŸããä»åã®ãããªãè€æ°ãã¡ã€ã«éã§ãã£ã«ã¿ãŒæ¡ä»¶ãå ±æãããæ©èœã¯ããŸãã«ãã®çšéã«é©ããŠããŸãã
å人çšãã¯ãããã¯ïŒPERSONAL.XLSBïŒ
Excelã§åããŠãã¯ããèšé²ããéã«èªåäœæãããé ããã¡ã€ã«ã§ãããã®ãã¡ã€ã«ã«ä¿åããããã¯ãã¯ãExcelèµ·åæã«åžžã«èªã¿èŸŒãŸããããããã¹ãŠã®ããã¯ããå©çšå¯èœã«ãªããŸãã
ã¯ã€ãã¯ã¢ã¯ã»ã¹ããŒã«ããŒãžã®ç»é²æé
éçºã¿ãã衚瀺ãããŠããªãå Žå
äžèšæé ã§éçºã¿ãã䜿çšããŸããéçºã¿ãã®è¡šç€ºæ¹æ³ã«ã€ããŠã¯ãã第1å: Excel VBAã®åºç€ç¥èãšã»ãã¥ãªãã£èšå®ãã§è©³ãã解説ããŠããŸãã®ã§ãéçºã¿ããèŠåœãããªãæ¹ã¯ãã²ãåç §ãã ããã
-
ãã¯ãã®å人çšãã¯ãããã¯ãžã®ä¿å
å人çšãã¯ãããã¯ãžã®ä¿åæ¹æ³ã¯ãååäœæãæ¢åãã¡ã€ã«ãžã®è¿œå ãã§æé ãç°ãªããŸãã
ååäœæã®å Žå
ã¯ãããŠå人çšãã¯ãããã¯ãäœæããéã¯ãéçºã¿ãã®ããã¯ãã®èšé²ãã䜿çšããŸããèšé²å ã§ãå人çšãã¯ãããã¯ããéžæããé©åœãªãã¯ããèšé²ããåŸããã®ã³ãŒããä»åäœæããã³ãŒãã«äžæžãããããæ°ãã«ã¢ãžã¥ãŒã«ã远å ããŠäœæããŸããæ¢åã®å人çšãã¯ãããã¯ãããå Žå
å人çšãã¯ãããã¯ïŒPERSONAL.XLSB
ïŒã¯ãäžèšããšã¯ã¹ãããŒã©ãŒã®ã¢ãã¬ã¹ããŒã«çŽæ¥å ¥åããããšã§ã該åœãã©ã«ãã«çŽæ¥ã¢ã¯ã»ã¹ã§ããŸãã%APPDATA%\Microsoft\Excel\XLSTART\
-
ã¯ã€ãã¯ã¢ã¯ã»ã¹ããŒã«ããŒã®ã«ã¹ã¿ãã€ãº
-
ã¢ã€ã³ã³ãšããŒã«ãã³ãã®èšå®
å®çšçãªäœ¿çšã·ãŒã³
ãã®èšå®ã«ããã以äžã®ãããªäœæ¥ãã¹ã ãŒãºã«è¡ããããã«ãªããŸãã
- 売äžããŒã¿ãã¡ã€ã«ã§ç¹å®æ¡ä»¶ã®ååIDããã£ã«ã¿ãªã³ã°
- ãã£ã«ã¿ãŒçµæãšããŠè¡šç€ºãããååIDç¯å²ãéžæ
- ã¯ã€ãã¯ã¢ã¯ã»ã¹ããŒã«ããŒã®ãå¯èŠã»ã«ãã£ã«ã¿ãŒããã¯ãªãã¯
- åšåº«ç®¡çãã¡ã€ã«ãéããååIDåã®åçªå·ãæå®
- åãååIDã®ããŒã¿ãèªåçã«ãã£ã«ã¿ãªã³ã°ããã
å人çšãã¯ãããã¯ã䜿çšããéã®æ³šæ
- ã»ãã¥ãªãã£èšå®: ãã¯ããå®è¡ã§ããããã«ã»ãã¥ãªãã£ã¬ãã«ãé©åã«èšå®
- ããã¯ã¢ãã: å人çšãã¯ãããã¯ã¯éèŠãªããŒã«ã®ããã宿çã«ããã¯ã¢ãããäœæ
- ä»ã®PCã§ã®å©çš: ä»ã®ã³ã³ãã¥ãŒã¿ãŒã§åããã¯ãã䜿çšããå Žåã¯ããã¯ãã®ãšã¯ã¹ããŒãã»ã€ã³ããŒããå¿ èŠ
ãŸãšã
ä»å解説ããå¯èŠã»ã«æŽ»çšãã£ã«ã¿ãŒãã¯ããã¯ã¯ãè€æ°ãã¡ã€ã«éã§ã®ããŒã¿åæäœæ¥ã®å¹çåã«å€§ããè²¢ç®ããå®çšçãªææ³ã§ãããã®ãã¯ããã¯ã掻çšããããšã§ãäžã€ã®ãã¡ã€ã«ã§ãã£ã«ã¿ãªã³ã°ããçµæãå¥ã®ãã¡ã€ã«ã«æåã§é©çšããç ©éãªäœæ¥ããè§£æŸãããããé«åºŠãªããŒã¿åæã«æéãéäžã§ããããã«ãªããŸãã
ãã®ææ³ã®æ žå¿ãšãªãã®ã¯ãSpecialCells(xlCellTypeVisible)
ã«ããå¯èŠã»ã«ã®ååŸãåçé
åã®æŽ»çšããããŠè€æ°ãšãªã¢ã®å¹ççãªåŠçã§ãããããã®æè¡ãçµã¿åãããããšã§ããã£ã«ã¿ãŒãããçµæãä»ã®ãã¡ã€ã«ã®åãé
ç®åã«å¯ŸããŠæ¡ä»¶ãšããŠåå©çšããæè»ãªã·ã¹ãã ãæ§ç¯ã§ããŸãã
å®éã®æ¥åã§æŽ»çšããéã«ã¯ããšã©ãŒãã³ããªã³ã°ã®å å®ãããã©ãŒãã³ã¹ã®æé©åããŠãŒã¶ããªãã£ã®åäžãªã©ã®æ¹è¯ãå ããããšã§ãããå ç¢ã§äœ¿ããããããŒã«ãäœæã§ããŸãããŸããã¢ãžã¥ãŒã«èšèšã®ååã«åŸã£ãŠåŠçãåå²ããããšã§ãä¿å®æ§ãšæ¡åŒµæ§ã®é«ãã³ãŒããå®çŸã§ããç°ãªããã¡ã€ã«éã§ã®æ¡ä»¶å ±æã容æã«ãªããŸãã
ãã®ãããªé«åºŠãªãã£ã«ã¿ãŒæäœãã¯ãããã¹ã¿ãŒããããšã§ãExcelã§ã®ããŒã¿åæäœæ¥ãå€§å¹ ã«å¹çåã§ããè€æ°ã®ããŒã¿ãœãŒã¹ã飿ºãããè€éãªããŒã¿åŠçã·ã¹ãã ã®éçºåºç€ã身ã«ã€ããããšãã§ããŸãããã²ãèªåã®ããŒã¿åææ¥åã«é©çšãããã¡ã€ã«é飿ºã®å¹çåãå³ã£ãŠã¿ãŠãã ããã
次åã¯ããã£ã«ã¿ãŒãé©çšãããç¶æ ãè¡ã»åãé衚瀺ã«ãªã£ãŠããç¯å²ããå¯èŠã»ã«ã®ã¿ãæœåºãã貌ãä»ãå ã§ãéè¡šç€ºç¶æ ãä¿æãããŸãŸé çªã«è»¢èšãããã¯ããã¯ã«ã€ããŠè§£èª¬ããŸããCollectionãªããžã§ã¯ãã«ããå¯èŠè¡ã»åã®åç管çãHiddenããããã£ã掻çšããè¡šç€ºç¶æ ã®å€å®ãé ååŠçã«ããé«éããŒã¿èªã¿åããªã©ã®æè¡èŠçŽ ãçµã¿åãããå®è·µçãªãã¯ãã§ãããã²ãæåŸ ãã ããïŒ