1. VBAãšã¯äœãïŒ
VBAãããã¯Visual Basic for Applicationsã®ç¥ã§ããã€ã¯ããœãã瀟ãæäŸããããã°ã©ãã³ã°èšèªã§ãã
1-1. VBAã®å®çŸ©
VBAã¯ãã€ã¯ããœããã®Office補åïŒExcelãWordãAccessãªã©ïŒã«æšæºæèŒãããŠããããã°ã©ãã³ã°èšèªã§ããããšããšã¯Microsoftã®ããã°ã©ãã³ã°èšèªã§ãããVisual BasicããOffice補åçšã«ã¢ã¬ã³ãžãããã®ã§ãããã«ããOffice補åã®æ©èœãæ¡åŒµããããç¬èªã®åŠçãè¿œå ãããããããšãå¯èœã«ãªããŸãã
1-2. VBAã®çšéãšå©ç¹
VBAã䜿çšããã°ãOffice補åã®èªååãã«ã¹ã¿ãã€ãºãæŽã«ã¯è€éãªåŠçãç¬èªã®é¢æ°ã®äœæãå¯èœã«ãªããŸããäŸãã°Excelã®å ŽåãäžåºŠã®ã¯ãªãã¯ã ãã§äœçŸãäœåãšããè¡ã®ããŒã¿ãåŠçãããã¯ããäœæããããWordã§ã¯äžåºŠã«å€§éã®ææžãã©ãŒããããæŽããããšãã§ããŸããæäœæ¥ã§ã¯å°é£ãªäœæ¥ããVBAã䜿ãããšã§å¹çãããæ£ç¢ºã«è¡ãããšãå¯èœã«ãªããŸãããŸããä»ã®ããã°ã©ãã³ã°èšèªãšæ¯èŒããŠããåå¿è ã§ãåŠã³ããããOffice補åããã䜿çšããããžãã¹ããŒãœã³ã«ãšã£ãŠã¯éåžžã«æçšãªã¹ãã«ãšãªããŸãã
1-3. VBAãåŠã¶äžã§ã®éèŠæ§
VBAãåŠã¶ããšã§ããã°ã©ãã³ã°ã®åºç€ç¥èã身ã«ã€ããããšãã§ããããã«ããæ¥ã ã®æ¥åå¹çã®åäžã ãã§ãªããããŒã¿åŠçãåé¡è§£æ±ºã®ã¹ãã«ã身ã«ã€ããããšãå¯èœãšãªããŸããæŠéã·ãŒã³ã§éå±ãªæ¥åžžã®äœæ¥ãã解æŸããããšåæã«ãèªååãæé©åã«ããæéãå€§å¹ ã«ç¯çŽããããšãå¯èœãšãªããŸãããŸããVBAã®ç¥èã¯ExcelãWordãªã©ã®æ©èœãæ倧éã«æŽ»çšã§ããã¹ãã«ãšãªããŸããããã«ãããåãç°å¢ã«ãããŠäœããšåœ¹ç«ã€ã¹ãã«ã身ã«ã€ããããšãã§ããŸãã
2. VBAã®åºæ¬æŠå¿µ
ä»åã¯ãããããVBAãåŠãã§ããæ¹ã ããŸãç解ãã¹ãåºæ¬çãªæŠå¿µã«ã€ããŠã話ããŸããVBAãç解ãïŒå¹æçã«æŽ»çšããããã«ã¯ããªããžã§ã¯ãå¿åã®ç解ã倧åã§ãããŸãããã¯ã©ã¹ãšãªããžã§ã¯ããããããããã£ãšã¡ãœãããã®ç解ãVBAã®å°å ¥ãšãªãäž»èŠãªèŠçŽ ãšãªããŸãã
2-1. ãªããžã§ã¯ãå¿åãšã¯
ãªããžã§ã¯ãå¿åãšã¯ãããã°ã©ãã³ã°ã®èšèšææ³ã®äžã€ã§ãããçŸå®ã®äžçããªããžã§ã¯ããšããŠæš¡å£ããããšããèãæ¹ã§ãããªããžã§ã¯ãã¯ç¶æ ãšæ¯ãèããæã€ããšããããããããã°ã©ãã³ã°ã®äžçã«åãå ¥ããããšã§ãããçŸå®ã«è¿ã圢ã§è€éãªåé¡ã解決ããããšãå¯èœãšãªããŸãã
2-2. ã¯ã©ã¹ãšãªããžã§ã¯ã
ããŠãå ã»ã©ã®ãªããžã§ã¯ãå¿åã®èª¬æã§ç»å Žãããªããžã§ã¯ãã§ããããªããžã§ã¯ãã¯ã¯ã©ã¹ãããšã«ãªã£ãŠçæãããŸããã¯ã©ã¹ãšã¯ããªããžã§ã¯ãã®èšèšå³ã®ãããªãã®ã§ããªããžã§ã¯ããæã€ã¹ãç¶æ ãšæ¯ãèããå®çŸ©ããŠããŸãããã£ãŠãã¯ã©ã¹ããç¹å®ã®ãªããžã§ã¯ããç£ã¿åºãããšã§ããã®ãªããžã§ã¯ãã¯ã¯ã©ã¹ã§å®çŸ©ãããç¶æ ãšæ¯ãèããæã€ããšã«ãªããŸãã
2-3. ããããã£ãšã¡ãœãã
ç¶ããŠãããããã£ãšã¡ãœããã«ã€ããŠèª¬æããŸããã¯ã©ã¹ãæã€ç¶æ ãè¡šããã®ãããããã£ãæ¯ãèããè¡šããã®ãã¡ãœãããšãªããŸããããããã£ã¯ãã®ã¯ã©ã¹ãæã€ããŒã¿ãè¡šçŸã ã¡ãœããã¯ããŒã¿ãæäœãããç¹å®ã®åŠçãè¡ãããã®ãã®ã§ã
VBAã§ã¯ãå ·äœçã«ã¯ãã¯ãŒã¯ã·ãŒããã»ã«ãšãã£ããªããžã§ã¯ãããããå称ãè²ãšãã£ãããããã£ãæã¡ãŸãããããã®ãªããžã§ã¯ãã«å¯ŸããŠãå 容ãå€æŽãããååŸãããããããã®ã¡ãœãããæäŸãããŠããŸãããã®ããã«ç解ããããããã£ãšã¡ãœããã®äœ¿ãæ¹ãèŠããããšã¯ãVBAã身ã«ã€ããããã§éåžžã«å€§åã§ãã
3. VBAã®åºæ¬çãªæäœ
VBAã䜿ãããªãããã«ã¯ãåºæ¬çãªæäœæ¹æ³ã®ç¥èãäžå¯æ¬ ã§ããVBAã®éçºç°å¢ã®æŽåããã¯ãã®äœææ¹æ³ãã³ãŒãã®ç·šéããããŠãããã°ãšãã£ãåºæ¬çãªæäœæé ãããã§ã¯è§£èª¬ããŸãã
3-1. VBA ã®æºå
Microsoft Officeã®ã¢ããªã±ãŒã·ã§ã³ãäŸãã°ExcelãWordã¯ãåºæ¬çã«VBAã®éçºç°å¢ïŒIDEïŒãå«ãŸããŠããŸããããã䜿çšããããã«ã¯ãåã¢ããªã±ãŒã·ã§ã³ã®[ãã¡ã€ã«]ã¡ãã¥ãŒãã[ãªãã·ã§ã³]ãéžæããŠã[ã«ã¹ã¿ãã€ãº ãªãã³]ã¿ãã§[éçº]ãã§ãã¯ããã¯ã¹ããªã³ã«ããå¿ èŠããããŸããããã§éçºã¿ãã衚瀺ãããããããVBAã®éçºç°å¢ãéãããšãã§ããŸãã
3-2. ãã¯ãã®äœæ
VBAã§äœæããåŠçã®ãŸãšãŸãããã¯ããšåŒã³ãŸãããã¯ããäœæããã«ã¯éçºã¿ããã[Visual Basic]ãã¿ã³ãã¯ãªãã¯ããVBAã®éçºç°å¢ãéããŸããå·ŠåŽã®ãããžã§ã¯ããšã¯ã¹ãããŒã©ãŒã§äœæ察象ã®ãããžã§ã¯ããéžæããã¡ãã¥ãŒã®[æ¿å ¥]ãã[ã¢ãžã¥ãŒã«]ãéžæããŸããããã§æ°ããã¢ãžã¥ãŒã«ãäœæãããããã«èªåã®ãã¯ãïŒããã·ãŒãžã£ïŒãæžã蟌ãããšãã§ããããã«ãªããŸãã
3-3. ã³ãŒãã®ç·šé
VBAã®ã³ãŒãã¯ãéçºç°å¢ã®ã³ãŒããŠã£ã³ããŠã§ç·šéããŸããã³ãŒãã®æžãæ¹ã¯ãŸãã«è±èªã®ããã«ãäž»èªã»åè©ã»ç®çèªãã®é ã«èšè¿°ããŠãããŸããæ°ããªè¡ã«ç§»ãããšã§æ°ããã³ãŒãã®è¡ãå§ãŸããå é ããèªãã§ãã£ãŠèšè¿°éãã®åŠçãè¡ãããŠããä»çµã¿ã§ãã
3-4. VBAã®ãããã°
äœæãããã¯ããé©åã«åäœãããã©ããã調ã¹ãããšããããã°ãšèšããŸããVBAã®éçºç°å¢ã¯ããã€ãã®ãããã°æ©èœãæäŸããŠããŸããäž»ãªãã®ã«ãã¬ãŒã¯ãã€ã³ãïŒåæ¢äœçœ®ïŒãã¹ãããå®è¡ïŒäžè¡ãã€åŠçãé²ããïŒããŠã©ããïŒå€æ°ãåŒã®å€ãç£èŠïŒãªã©ããããŸããç¹ã«ãã¹ãããå®è¡ãã¯ãã³ãŒããé ã«ã©ã®ããã«å®è¡ãããŠããã芳å¯ããã®ã«éåžžã«äŸ¿å©ãªæ©èœã§ãã
4. VBAã®åºæ¬çãªææ³ãšæžãæ¹
VBAã®ææ³ãšæžãæ¹ã«ã€ããŠè©³ããèŠãŠãããŸãããããããã£ãç¥èãæŒãããŠããããšã§ãããè€éãªVBAã®ãã¯ããèªç±èªåšã«æ±ãããšãå¯èœã«ãªããŸãã
4-1. å€æ°ãšããŒã¿å
ãŸãã¯ãå€æ°ããšãããŒã¿åãã«ã€ããŠåŠã³ãŸããããå€æ°ã¯ãæãåºæ¬çãªããã°ã©ãã³ã°ã®æŠå¿µã§ãããŒã¿ãä¿æããããã®å®¹åšã®ãããªãã®ã§ããVBAã§ã¯ãå€æ°ã«ååšããããŒã¿åãè±å¯ã§ããã®äžã§ããã䜿ãããInteger, String, Doubleãªã©ããããŸãããã®åã ã®ããŒã¿åã¯ããŒã¿ã®çš®é¡ã«ãã£ãŠæ±ºå®ããããã®ç¹æ§ãç¹å®ã®ç¯å²å ã«ä¿æããŸãã
4-2. æŒç®åãšåŒ
次ã«ãæŒç®åãšåŒãã«ã€ããŠè§£èª¬ããŸããVBAã®æŒç®åã¯ãæ°å€ãæååãè«çå€ã®æäœã«äœ¿ãããŸããåºæ¬çãªååæŒç®ãæååçµåãè«çæŒç®ãªã©ãå€æ§ãªèšç®ãæäœãè¡ãããšãã§ããŸããäžæ¹ãåŒãšã¯ãã®æŒç®åãšå€æ°ããªãã©ã«ãªã©ãçµã¿åããã£ããã®ã§ãå ·äœçãªèšç®çµæãçåœã®è©äŸ¡çµæãè¿ããŸãã
4-3. å¶åŸ¡æ§é
VBAã®ããã°ã©ã ã¯äžããäžãžãšé çªã«å®è¡ãããŸããããã®æµããå¶åŸ¡ããã®ããå¶åŸ¡æ§é ãã§ããVBAã®å¶åŸ¡æ§é ã«ã¯äž»ã«ãIfïœElseæ§æãããForïœNextã«ãŒãããªã©ããããŸããIfïœElseæ§æã¯æ¡ä»¶ã«ãã£ãŠå®è¡ãããã³ãŒããåå²ãããããForïœNextã«ãŒãæ§æã¯äžå®ã®åæ°ã ãåãåŠçãç¹°ãè¿ãããã®ãã®ã§ãã
4-4. é¢æ°ãšãµãã«ãŒãã³
æåŸã«ãé¢æ°ãšãµãã«ãŒãã³ãã«ã€ããŠèª¬æããŸããVBAã«ãããé¢æ°ãšã¯ãç¹å®ã®åŠçãè¡ãçµæãè¿ãããã·ãŒãžã£ã®ããšãèšããŸãããµãã«ãŒãã³ã¯é¢æ°åæ§ããã·ãŒãžã£ã®äžåœ¢æ ã§ãããé¢æ°ãšç°ãªãäœãç¹å®ã®å€ãè¿ãããšã¯ãããŸããããã ããããããå ±éããŠãäœåºŠã§ãåŒã³åºãããšãå¯èœã§ãæ±çšæ§ã®é«ãããã°ã©ã ãäœæããããã«ã¯éåžžã«éèŠãªæŠå¿µã§ãã
5. VBAã§ãã䜿çšããããªããžã§ã¯ããšãã®æäœ
VBAã¯ãExcelã®æäœãå¶åŸ¡ããããã®ããã°ã©ã èšèªã§ãããæäœã®å¯Ÿè±¡ãšãªããã®ã¯ããªããžã§ã¯ãããšåŒã³ãŸãããã®ç« ã§ã¯ãç¹ã«Excelæäœã§ãã䜿çšããããã¯ãŒã¯ã·ãŒããªããžã§ã¯ããã¬ã³ãžãªããžã§ã¯ããã¯ãŒã¯ããã¯ãªããžã§ã¯ãã«ã€ããŠè§£èª¬ããŠãããŸãã
5-1. ã¯ãŒã¯ã·ãŒããªããžã§ã¯ã
ã¯ãŒã¯ã·ãŒããªããžã§ã¯ããšã¯ãExcelã®åã·ãŒããæ±ãããã®ãªããžã§ã¯ãã§ããå ·äœçã«ã¯ãã·ãŒãã®ååãååŸãããå€æŽããããæ°ããã·ãŒããè¿œå ããããæ¢åã®ã·ãŒããåé€ãããããããšãã§ããŸãã
' ã·ãŒãã®ååãååŸ Worksheets("Sheet1").Name ' ã·ãŒãã®ååãå€æŽ Worksheets("Sheet1").Name = "NewName" ' æ°ããã·ãŒããè¿œå Worksheets.Add ' ã·ãŒããåé€ Worksheets("Sheet1").Delete
äžèšã®ããã«ãWorksheetsãªããžã§ã¯ããçšããŠåçš®æäœãè¡ãããšãå¯èœã§ãã
5-2. ã¬ã³ãžãªããžã§ã¯ã
ã¬ã³ãžãªããžã§ã¯ãã¯ãã¯ãŒã¯ã·ãŒãäžã®ç¹å®ã®ç¯å²ãæããªããžã§ã¯ãã§ãããã®ãªããžã§ã¯ãã䜿ãã°ãã»ã«ã®å€ãååŸãããå€æŽãããããã©ãŒããããèšå®ããããèšç®ãè¡ã£ããããããšãå¯èœã§ãã
' A1ã®ã»ã«ã®å€ãååŸ Range("A1").Value ' A1ã®ã»ã«ã®å€ãå€æŽ Range("A1").Value = "Hello, VBA!" ' A1:B2ã®ç¯å²ã®ãã©ãŒããããèšå® Range("A1:B2").HorizontalAlignment = xlCenter ' A1:A10ã®ç¯å²ã®åèšãèšç® Application.WorksheetFunction.Sum(Range("A1:A10"))
äžèšã®ããã«ãRangeãªããžã§ã¯ããçšããããšã§ãå ·äœçãªã»ã«ã«å¯Ÿããæäœãèšç®ãè¡ãããšãã§ããŸãã
5-3. ã¯ãŒã¯ããã¯ãªããžã§ã¯ã
ã¯ãŒã¯ããã¯ãªããžã§ã¯ããšã¯ãExcelã®äžã€ã®ãã¡ã€ã«ïŒ.xlsxã.xlsãªã©ïŒå šäœãè¡šããªããžã§ã¯ãã§ãããããçšããããšã§ãæ°ããã¯ãŒã¯ããã¯ã®äœæãä¿åãéãã¯ãŒã¯ããã¯ã®åãæ¿ããªã©ãã¯ãŒã¯ããã¯å šäœã«å¯Ÿããæäœãè¡ãããšãã§ããŸãã
' æ°ããã¯ãŒã¯ããã¯ãäœæ Workbooks.Add ' ã¯ãŒã¯ããã¯ãä¿å ActiveWorkbook.Save ' ååãã€ããŠã¯ãŒã¯ããã¯ãä¿å ActiveWorkbook.SaveAs Filename:="C:tempsample.xlsx" ' å¥ã®ã¯ãŒã¯ããã¯ãéã Workbooks.Open Filename:="C:tempsample2.xlsx" ' ç¹å®ã®ã¯ãŒã¯ããã¯ãã¢ã¯ãã£ãã«ãã Workbooks("sample.xlsx").Activate
ãã®ããã«ãWorkbooksãActiveWorkbookãªããžã§ã¯ãã䜿ã£ãŠãã¯ãŒã¯ããã¯å šäœã®æäœãå¯èœã§ãããããã®ãªããžã§ã¯ããç解ããŠäœ¿ãããªãããšã¯ãVBAã§Excelãæäœããäžã§éåžžã«éèŠã§ãã
6. VBAã®å¿çšçãªäœ¿ãæ¹
VBAã®åºç€ãæŒãããåŸã次ã«ææŠãããã®ãVBAã®å¿çšçãªäœ¿ãæ¹ã§ããä»åã¯ãå ·äœçã«ãŠãŒã¶ãŒãã©ãŒã ã®äœæãã€ãã³ãããã°ã©ãã³ã°ããšã©ãŒãã³ããªã³ã°ã«ã€ããŠè§£èª¬ããŸãã
6-1. ãŠãŒã¶ãŒãã©ãŒã ã®äœæ
ãŸããVBAã䜿ã£ãŠãŠãŒã¶ãŒãã©ãŒã ãäœæããæ¹æ³ã«ã€ããŠåŠã³ãŸãããããŠãŒã¶ãŒãã©ãŒã ãšã¯ãExcelã®ã·ãŒãäžã§ã¯ãªãå¥çªã§è¡šç€ºã§ãããã€ã¢ãã°ããã¯ã¹ã®ããšã§ããããã䜿ãã°ãŠãŒã¶ãŒã«æ å ±ãå ¥åããããéžæããããªã©ãããèŠèŠçãªæäœãå¯èœã«ããŸãã
ãŠãŒã¶ãŒãã©ãŒã ã¯VBAãšãã£ã¿ã®[æ¿å ¥]ã¡ãã¥ãŒãã[ãŠãŒã¶ãŒãã©ãŒã ]ãéžæããããšã§ç°¡åã«äœæã§ããŸããäœæãããã©ãŒã ã«ã¯ãããã¹ãããã¯ã¹ããã¿ã³ãã©ãã«ãªã©ã®ã³ã³ãããŒã«ãé 眮ããããããã«å¯Ÿå¿ããã³ãŒããæžãããšã§åäœãå¶åŸ¡ããŸãã
6-2. ã€ãã³ãããã°ã©ãã³ã°
次ã«ãã€ãã³ãããã°ã©ãã³ã°ã«ã€ããŠèŠãŠãããŸããããããã¯ãç¹å®ã®ã€ãã³ãïŒãŠãŒã¶ãŒã®æäœãªã©ïŒãèµ·ãã£ããšãã«èªåçã«ããã°ã©ã ãåäœããæè¡ã§ãã
VBAã§ã€ãã³ãããã°ã©ãã³ã°ãè¡ãã«ã¯ãã€ãã³ãã«å¿ããVBAã®ã³ãŒããæžãå¿ èŠããããŸããäŸãã°ããã¿ã³ãã¯ãªãã¯ããããšãã«åŠçãå®è¡ããã«ã¯ããã®ãã¿ã³ã®ã¯ãªãã¯ã€ãã³ãã«ã³ãŒããèšè¿°ããŸãããã®ããã«ã€ãã³ãã«å¯Ÿå¿ããã³ãŒããæžãããšã§ããŠãŒã¶ãŒã®æäœã«ããåçã«ããã°ã©ã ãåäœããŸãã
6-3. ãšã©ãŒãã³ããªã³ã°
æåŸã«ããšã©ãŒãã³ããªã³ã°ã«ã€ããŠåŠãã§ãããŸãããããšã©ãŒãã³ããªã³ã°ãšã¯ãäºæãã¬ãšã©ãŒãçºçããæã«é©åã«å¯ŸåŠããããã®æè¡ã§ãããšã©ãŒãã³ããªã³ã°ãè¡ãããšã§ããšã©ãŒãçºçããŠãããã°ã©ã ãäžæããããšãªãé©åãªåŠçãç¶ããããšãå¯èœã«ãªããŸãã
VBAã§ãšã©ãŒãã³ããªã³ã°ãè¡ãã«ã¯ãäžè¬çã«ã¯
On Error
ã¹ããŒãã¡ã³ããå©çšããŸãããOn Error GoTo [ã©ãã«å]ããšæžããšããšã©ãŒãçºçããéã«æå®ããã©ãã«ã«é£ãã§ãšã©ãŒåŠçãè¡ãããšãã§ããŸãã
以äžããVBAã®å¿çšçãªäœ¿ãæ¹ã«ã€ããŠã®è§£èª¬ã§ãããåºæ¬ãç解ããäžã§ãããããã¹ã¿ãŒããããšã§ãVBAã®ãã¯ãŒãååã«åŒãåºãããšãã§ããŸãã
7. VBAã§å®è·µçãªåŠçãè¡ã
VBAã®ééå³ã¯ãé »ç¹ãªã¿ã¹ã¯ãèªååããããããŒã¿åæãç°¡åã«å®è¡ã§ããããã«ããããšã«ãããŸãããããã玹ä»ããé åã§ã¯ãVBAã®å®è·µçãªäœ¿ãæ¹ãšãã®åœ±é¿ãç解ããã®ã«åœ¹ç«ã¡ãŸãã
7-1. Excelæäœã®èªåå
æåã«VBAã䜿çšããŠã®Excelæäœã®èªååã«ã€ããŠè§£èª¬ããŸããæ¥ã ã®æ¥åãããŒã¿åæã§ã¯ãåãExcelæäœãäœåºŠãè¡ãå Žé¢ãå€ã ãããŸããããã§ã¯ãäŸãã°ãã¹ãŠã®ã·ãŒãã®A1ã»ã«ã«åãå€ãå ¥åãããšããäœæ¥ãVBAã§èªååããŠã¿ãŸãããã
Sub FillValueToAllSheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets ws.Range("A1").Value = "Hello, VBA!" Next ws End Sub
ãã®ã³ãŒãã¯ãå šãŠã®ã·ãŒãã®A1ã»ã«ã«”Hello, VBA!”ãšããå€ãæ¿å ¥ããŸãããã®ããã«ãVBAã䜿çšãããšãæäœæ¥ã§äœåºŠãç¹°ãè¿ããããªäœæ¥ãäžåºŠã«ãŸãšããŠå®è¡ããããšãå¯èœã«ãªããŸãã
7-2. ããŒã¿åæã®èªåå
VBAã䜿ããšãããŒã¿åæãèªååããããšãå¯èœã§ããäžäŸãæãããšãè¡šã®äžããç¹å®ã®æ¡ä»¶ãæºããããŒã¿ãæœåºããæ°ããã·ãŒãã«åºåãããšããããšãVBAã§å®çŸã§ããŸãã
Sub FilterAndCopyToNewSheet() Dim ws As Worksheet Set ws = Sheets.Add ThisWorkbook.Sheets("Data").Range("A1:C10").AutoFilter Field:=2, Criteria1:=">100" ThisWorkbook.Sheets("Data").Range("A1:C10").SpecialCells(xlCellTypeVisible).Copy ws.Range("A1") End Sub
äžã®ã³ãŒãã¯ã”Data”ãšããååã®ã·ãŒãå ã®A1:C10ç¯å²ã«ããããŒã¿ãããBåã100ãã倧ããããŒã¿ãæ°ããã·ãŒãã«ã³ããŒããŸãããã®ããã«ãVBAã䜿ãããšã§å€§éã®ããŒã¿ã«å¯ŸããŠã®åæäœæ¥ãèªååã§ããå¹ççã«äœæ¥ãé²ããããšãå¯èœãšãªããŸãã
7-3. å°ããªããã°ã©ã ã®äœæäŸ
VBAã¯ããã°ã©ãã³ã°èšèªã§ããããããåçŽãªç¹°ãè¿ãäœæ¥ã ãã§ãªããããå°ãè€æ°ã®å€æã䌎ãåŠçãæžãããšãã§ããŸãã
äŸãšããŠæªå®äºã®ã¿ã¹ã¯ãªã¹ãããæãç· åæ¥ãæ©ãã¿ã¹ã¯ãåºåããããã°ã©ã ãäœæããŠã¿ãŸãããã
Sub FindEarliestDeadline() Dim rng As Range Dim minDate As Date Dim task As String minDate = Date + 365 For Each rng In ThisWorkbook.Sheets("Tasks").Range("A2:B10") If rng.Offset(0, 1).Value = "Incomplete" And rng.Value < minDate Then minDate = rng.Value task = rng.Offset(0, 1).Value End If Next rng MsgBox "The earliest incomplete task is " & task & " with deadline of " & minDate End Sub
ãã®ã³ãŒãã¯æªå®äºã®ã¿ã¹ã¯ãªã¹ãããæãç· åæ¥ãæ©ãã¿ã¹ã¯ãMessageBoxã§åºåããŸãããã®ããã«ãVBAãçšããããšã§å¹ççã«æ å ±ãæœåºããæ確ãªçµæãåºåããããšãã§ããŸãã
以äžããVBAãçšããŠExcelæäœãèªååããããããŒã¿åæãè¡ã£ãããå°ããªããã°ã©ã ãäœæããäŸã§ãããVBAãåŠã¶ããšã§æ¥ã ã®äœæ¥ãå€§å¹ ã«å¹çåãããããšãæããŠããã ããããšæããŸãã
8. VBAç¿åŸã®ããã®åŠç¿æ³ãšæ¬¡ã®ã¹ããã
ããã°ã©ãã³ã°èšèªãç¿åŸããäžã§å¹æçãªåŠç¿æ³ã¯ãå埩åŠç¿ãããå®è·µçãªèª²é¡ã®è§£æ±ºãããæçãªãªã³ã©ã€ã³ãªãœãŒã¹ã®æŽ»çšãã®3ã€ã§ãããããã®åŠç¿æ³ãçšããããšã§ãããªãã¯ããè¿ éãã€å¹ççã«VBAããã¹ã¿ãŒããããšãã§ããŸãã
8-1. å埩åŠç¿ã®éèŠæ§
極ããŠåºæ¬çãªççã§ãããäžåºŠèŽããã ããäžåºŠèªãã ã ããäžåºŠæžããã ãã§ã¯ããã°ã©ãã³ã°ã®ç¥èãæè¡ãå®å šã«ç解ãã身ã«ã€ããã®ã¯é£ããã§ããæ°ããåŠãã ããšãäœåºŠãå埩ããå®éã«æãåãããŠã³ãŒããæžããçµæã確èªããããšã§ããç¥èãããã¹ãã«ããžãšæè¯ããŸããVBAãäŸå€ã§ã¯ãããŸãããæé ãæ§æãäœåºŠãç¹°ãè¿ããŠæžãããšã§ãèšæ¶ããè ã«ç¥èã移ãã®ãå®æã§ããã¯ãã§ãã
8-2. å®è·µçãªèª²é¡ã解ã
å®éã®åé¡ã解決ããããšã§ãåŠãã ç¥èã掻çšããå®è·µåãéããŸããææã®åŠç¿ã ãã§ãªããèªèº«ãæ±ããåé¡ãVBAã§è§£æ±ºããè©Šã¿ãè¡ãããšãéèŠã§ããããã¯ããšã¯ã»ã«ã®ããŒã¿æäœãèªååããããã®ç°¡åãªãã¯ããæžãããšããå§ããããŸããããããçµéšãéããŠãVBAã®ã³ãŒãã£ã³ã°èœåã ãã§ãªããããã°ã©ãã³ã°æèåãéããããšãã§ããŸãã
8-3. ãªã³ã©ã€ã³ãªãœãŒã¹ã®æŽ»çš
çŸåšãã€ã³ã¿ãŒãããã«ã¯å€ãã®åŠç¿ãªãœãŒã¹ããããŸããå ¬åŒã®ãªãã¡ã¬ã³ã¹ããå§ããStack Overflowãªã©ã®ãªã³ã©ã€ã³ã³ãã¥ããã£ãYouTubeãªã©ã®ãããªãã£ã³ãã«ãGithubãªã©ã®ã³ãŒãå ±æãµã€ããŸã§ãåçš®ã®ãªãœãŒã¹ããã«ã«æŽ»çšããããšã§ãã¹ãã«ã¢ããã®é床ãšå¹çãå€§å¹ ã«åäžãããããšãã§ããŸãããããã®ãªãœãŒã¹ã掻çšããããšã§ãèªå·±åŠç¿ããã楜ãã¿ããããã®ãšãªããå¿åæ§ãé«ãŸãã¯ãã§ãã