ããžãã¹ã®å¹çåãç®æãããªãã«ãšã£ãŠãExcel VBAã¯é¿ããŠã¯éããªãããŒããããããŸããããã®ã·ã³ãã«ãªããã°ã©ãã³ã°ãã§ããã°ãé¢åãªäœæ¥ãèªååããæéãå€§å¹ ã«ç¯çŽããããšãã§ããŸããããã§ä»åã¯ãVBAã®åºæ¬ããå¿çšãŸã§ãã¹ããŒãã£ã«åŠã¶ããã®èŠç¹ãç°¡æœã«è§£èª¬ããŸããåå¿è åãã«VBAã®æŠå¿µãæ確ã«ããæºåã®æ®µéããå®è·µçãªã¹ã¯ãªããã®äœæãããã«ã¯ãã©ãã«ã·ã¥ãŒãã£ã³ã°ã«è³ããŸã§ã圹ç«ã€æ å ±ãã®ã¥ããšåçž®ãããžãã¹ã·ãŒã³ã§äžæ©ãªãŒãããVBA掻çšè¡ã10åã§æŽã¿ãŸãããã
1. VBAãšã¯ïŒ – ããã°ã©ãã³ã°åå¿è ã®ããã®å ¥éã¬ã€ã
1.1 VBAã®åºæ¬æŠå¿µ
Visual Basic for Applications (VBA)ã¯ãMicrosoft Office補åå éšã§åäœããããã°ã©ãã³ã°èšèªã§ããVBAãå©çšããããšã§ãExcelãWordãªã©ã®æ¥ã ã®äœæ¥ãèªååããè€éãªäœæ¥ãåçŽåããããšãã§ããŸããVBAã¯ãã€ãã³ãé§ååããã°ã©ãã³ã°ãšåŒã°ãããã©ãã€ã ã«åºã¥ããŠãããç¹å®ã®ã¢ã¯ã·ã§ã³ïŒäŸïŒãã¿ã³ã¯ãªãã¯ïŒãå®è¡ãããæã«ããã·ãŒãžã£ãèµ·åããŸãã
VBAã«ã¯ãã¯ããšåŒã°ããæ©èœãå«ãŸããŠãããããã䜿çšããããšã§ãäžé£ã®æäœãèšé²ããåŸããç¹°ãè¿ãå®è¡ããããšãã§ããŸãããã¯ãã¯ãäœæ¥ã®æéççž®ã«æå¹çãªæ¹æ³ãšããŠå€ãã®æ¥åã§çšããããŠããŸãã
ãŸããVBAã¯ãªããžã§ã¯ãæåã®èŠçŽ ãå«ãã§ããããªããžã§ã¯ããããããã£ãã¡ãœãããã€ãã³ããšããæŠå¿µãéããŠã¢ããªã±ãŒã·ã§ã³ãæäœããŸãããããã®æŠå¿µãç解ã掻çšããããšã§ãããé«åºŠãªèªååã¹ã¯ãªããã®äœæãå¯èœã«ãªããŸãã
1.2 VBAã䜿ãããç°å¢ãšçç±
VBAã¯äž»ã«Microsoft Office補å矀ã«çµã¿èŸŒãŸããŠãããExcelãWordãAccessãOutlookãªã©åã¢ããªã±ãŒã·ã§ã³å ã§ã«ã¹ã¿ã ã¹ã¯ãªãããäœæããæäœãèªååããããã«åºã䜿ãããŠããŸãããã®èšèªãéåžžã«æ®åããŠããçç±ã®äžã€ã«ã¯ãMicrosoft Officeãããžãã¹ã®äžçã§åºãå©çšãããŠããããšãæããããŸãã
ãŸããVBAã䜿çšããçç±ãšããŠãå¹ åºããŠãŒã¶ãŒãããŒã«ãã«ã¹ã¿ãã€ãºããç¹å®ã®äœæ¥ããã»ã¹ã«åãããŠèª¿æŽã§ãããšããç¹ããããŸããããã«ãããå¹çåãæéã®ç¯çŽã ãã§ãªããç¹å®ã®æ¥åã«å¿ èŠãªæ©èœãæäŸããããšãã§ããŸãã
ããã«ãVBAã¯æ¯èŒçç¿åŸã容æã§ãããããã°ã©ãã³ã°åå¿è ã§ãåºæ¬çãªã¹ã¯ãªãããæ¯èŒççæéã§åŠã¶ããšãå¯èœã§ãããã®ãããéæ å ±æè¡éšéã®äººã ã«ãåãå ¥ããããããã§ãã
1.3 ããã°ã©ãã³ã°èšèªãšããŠã®VBAã®äœçœ®ä»ã
ããã°ã©ãã³ã°èšèªãšããŠã®VBAã¯å ¥éã¬ãã«ãšèããããããšãå€ãã§ããããã¯ãèŠèŠçãã€å¯Ÿè©±çãªããã°ã©ãã³ã°ã¢ãããŒããæ¡ã£ãŠãããããåºæ¬çãªã³ã³ã»ãããç解ããããšã容æãšãããããã§ãããããããªããžã§ã¯ãæåããã°ã©ãã³ã°ã®ååãé©åœã«åŠãã§æŽ»çšã§ãããããäžçŽè åãã®ã¹ãã«ãå¹ãã®ã«ãé©ããŠããŸãã
VBAã¯ãŸããå€ãã®çµã¿èŸŒã¿æ©èœãæã£ãŠãããè€éãªã¢ã«ãŽãªãºã ãããŒã¿åŠçãç°¡åã«è¡ããŸãããã®ãããã¢ããªã±ãŒã·ã§ã³ãšç·å¯ã«çµ±åãããããã°ã©ãã³ã°ãå¹æçã«è¡ãããã®äžã€ã®éžæè¢ãšãªããŸãã
äžæ¹ã§ãVBAã¯ãã€ã¯ããœããã®ãããã¯ãã«éå®ãããŠãããããä»ã®ãœãããŠã§ã¢ãã·ã¹ãã ãšã¯é£æºãã«ããåŽé¢ããããŸããããã§ããOffice補åã®é«åºŠãªã«ã¹ã¿ãã€ãºãæ±ããããå Žåã«ã¯ãäŸç¶ãšããŠæé©ãªéžæè¢ã§ãã
1.4 VBAã®å©ç¹ãšå¶çŽ
VBAã®å©ç¹ã®äžã€ãšããŠãæšæºã§Microsoft Office補åã«çµã¿èŸŒãŸããŠãããããè¿œå ã®ãœãããŠã§ã¢è³Œå ¥ãã€ã³ã¹ããŒã«ã®å¿ èŠããªãããšãæããããŸããããã«ããããªãã£ã¹ç°å¢ã§ã¯åææè³ãæãã€ã€ã匷åãªããŒã«ãå©çšããããšãã§ããŸãã
ãŸãããšã¯ã»ã«ã®ã¹ãã¬ããã·ãŒãæäœãææžã®æŽåœ¢ãªã©ãæ¥åžžçãªæ¥åããã»ã¹ãèªååããããšã§ãäœæ¥å¹çãé£èºçã«åäžãããããšãå¯èœã§ãã
äžæ¹ã§ãVBAã¯ã»ãã¥ãªãã£ã®èŠ³ç¹ããäžå®ã®å¶çŽãæã£ãŠããŸãããã¯ãã¯æªæã®ããã³ãŒããå«ãå¯èœæ§ãããããã®ããå®è¡ã«ã¯åžžã«æ³šæãå¿ èŠã§ããå ããŠãVBAã¯Microsoft Officeã«äŸåããŠãããããä»ã®ãã©ãããã©ãŒã ããªãã¬ãŒãã£ã³ã°ã·ã¹ãã ã«ã¯å¯Ÿå¿ããŠããŸããããã®ç¹ã¯ã䜿çšããç°å¢ãéžæããéã«èæ ®ããå¿ èŠããããŸãã
2. VBAéå§åã®æºå – å¿ èŠãªããŒã«ãšèšå®
Visual Basic for ApplicationsïŒVBAïŒã¯ãExcelãªã©ã®Office補åã匷åã«ãµããŒãããããã°ã©ãã³ã°èšèªã§ãããããçšããŠäœæ¥ã®èªååãè€éãªèšç®ãããŒã¿åæãªã©ãå¯èœã«ãªããŸããããã®åã«ããã€ãã®æºåãå¿ èŠã§ãã
ãŸãæåã«å¿ èŠãªãã®ã¯ãMicrosoft Officeãã€ã³ã¹ããŒã«ãããŠããã³ã³ãã¥ãŒã¿ãŒã§ããVBAã¯Officeã«æšæºæèŒãããŠããã®ã§ãè¿œå ã®ãœãããŠã§ã¢ã¯å¿ èŠãããŸããã
次ã«ãExcelã§VBAã䜿çšããããã®éçºç°å¢èšå®ãè¡ãå¿ èŠããããŸãã以äžã®ã»ã¯ã·ã§ã³ã§ã¯éçºç°å¢ã®æ§ç¯ããå®éã«ãã¯ããèšé²ããæ¹æ³ãŸã§ã説æããŠãããŸãã
2.1 Excelã§ã®VBAéçºç°å¢ã®æ§ç¯
Excelã«VBAã®éçºç°å¢ãæ§ç¯ããããã«ããŸãExcelãèµ·åããããã¡ã€ã«ãã¿ãããããªãã·ã§ã³ããéžæããŸãã次ã«ããªãã³ã®ã«ã¹ã¿ãã€ãºããè¡ãããéçºãã¿ãããªãã³ã«è¿œå ããŸãã
ãéçºãã¿ããæå¹ã«ãããšãVBAãšãã£ã¿ãéãããããã¯ããèšé²ããããã®ãã¿ã³ããªãã³ã«è¡šç€ºãããŸãã
ãªããVBAãšãã£ã¿ãçŽæ¥éãã·ã§ãŒãã«ããããŒãšããŠãAltã+ãF11ãããããŸããããã䜿ããšãããã«ã³ãŒãã£ã³ã°ç»é¢ã«ã¢ã¯ã»ã¹ããããšãã§ããŸãã
2.2 ãã¯ãã®ã»ãã¥ãªãã£èšå®ã®èª¿æŽ
Excelã§VBAãã¯ããå®è¡ããåã«ãã»ãã¥ãªãã£ã®èšå®ã確èªããŸãããããã¯ãã«ã¯æªæã®ããã³ãŒããå«ãŸããå Žåããããããããã©ã«ãã§ã¯ã»ãã¥ãªãã£ã¬ãã«ãé«ãèšå®ãããŠããŸãã
ã»ãã¥ãªãã£èšå®ãå€æŽããã«ã¯ãããã¡ã€ã«ãã¿ãããããªãã·ã§ã³ãããã»ãã¥ãªãã£ã»ã³ã¿ãŒãããããŠãã»ãã¥ãªãã£ã»ã³ã¿ãŒã®èšå®ãã®é ã«é²ã¿ãããã¯ãã®èšå®ãã§é©åãªã¬ãã«ãéžæããŸããäžè¬çã«ã¯ãããã¹ãŠã®ãã¯ããç¡å¹ã«ããã以å€ã§ããã°ãéç¥ãåºãã®ã§å®å šã§ãã
ãã ããä¿¡é Œã§ãããã¯ãã®ã¿èš±å¯ããããšããå§ãããŸããäžæ £ããªå Žåã¯ããã©ã«ãèšå®ãç¶æããç¶æ ã§ãä¿¡é Œããããšãã§ãããã¯ãã ããå®è¡ããããã«ããŠãã ããã
2.3 éçºã¿ããšVBAãšãã£ã¿ã®æŽ»çšæ¹æ³
ãéçºãã¿ãã¯ãVBAéçºã«ãããŠéèŠãªåœ¹å²ãæãããŸãããããããã³ãŒããã°ã«ãŒãã«ããããã¯ãããã¿ã³ã䜿çšããŠæ¢åã®ãã¯ããå®è¡ãããããVBAãšãã£ã¿ããéãããããããšãã§ããŸãã
VBAãšãã£ã¿ã¯ãã³ãŒããèšèŒããããã°ããããã®å°çšã®ãŠã£ã³ããŠã§ãããšãã£ã¿äžã§ã¯ãã¢ãžã¥ãŒã«ãæ¿å ¥ããããããã·ãŒãžã£ãäœæããããã³ãŒãããã¹ããããããããšãå¯èœã§ãã
ãŸãããšãã£ã¿ã§ã¯ã³ãŒãã®æ§é ãããªãŒç¶ã«æŽçãããŠãããåãªããžã§ã¯ãã®ã€ãã³ãã«é¢é£ããã³ãŒããç°¡åã«èŠã€ããããšãã§ããŸãã䜿ãå§ãã¯è€éã«æãããããããããŸããããæ £ããã°åŒ·åãªéçºããŒã«ãšãªããŸãã
2.4 æåã®ãã¯ããèšé²ããŠã¿ãã
åå¿è ã«ãšã£ãŠæãç°¡åãªVBAå ¥éæ³ã¯ããã¯ãã®èšé²æ©èœãå©çšããããšã§ãããã®æ©èœã䜿ããšãExceläžã§ã®æäœããã¯ããšããŠä¿åããããšãã§ããŸãã
ãã¯ãèšé²ãéå§ããã«ã¯ããéçºãã¿ãããããã¯ãã®èšé²ããã¿ã³ãã¯ãªãã¯ããŸããããããExceläžã§è¡ãããæäœãè¡ããå®äºãããããã¯ãã®èšé²ããã¿ã³ãå床ã¯ãªãã¯ããŠèšé²ãåæ¢ããŸãã
èšé²ãããã¯ãã¯ããã¯ãããã¿ã³ãã¯ãªãã¯ããŠå®è¡ããããšãã§ããŸãããã®æ¹æ³ã§ãç¹°ãè¿ãäœæ¥ãèªååãããã¯ããäœæããå¹çåãå³ãããšãã§ããŸãã
3. VBAã®åºæ¬çãªæ§æãšæäœ
Visual Basic for ApplicationsïŒVBAïŒã¯ãäž»ã«Microsoft Office補åãèªååããããã®ããã°ã©ã èšèªã§ãããã®ããã°ã©ã èšèªã¯ãè€éãªã¿ã¹ã¯ãç°¡ç¥åããå¹ççãªã¯ãŒã¯ãããŒãäœæããã®ã«åœ¹ç«ã¡ãŸããVBAã䜿çšããããšã§ãExcelã ãã§ãªããWordãAccessãªã©ã®ã¢ããªã±ãŒã·ã§ã³ã«ãã«ã¹ã¿ã ã¹ã¯ãªãããé©çšã§ããŸãã
ããã°ã©ãã³ã°åå¿è ã§ãååã«æ±ããVBAã§ããããã®åºç€ãç解ããããšãéèŠã§ãããã®èšäºã¯ãVBAã®åºæ¬çãªæ§æãšæäœæ¹æ³ãç°¡åã«è§£èª¬ããŸãã
3.1 å€æ°ãšããŒã¿ã®æ±ãæ¹
å€æ°ã¯ããŒã¿ãäžæçã«æ ŒçŽããããã®å®¹åšã§ããVBAã§å€æ°ã䜿ãã«ã¯ãæåã«ãDimããšããããŒã¯ãŒããçšããŠå€æ°ã宣èšããŸããäŸãã°ããDim myVar As Integerããšããããšã§ã’myVar’ãšããååã®æŽæ°åã®å€æ°ãäœæã§ããŸããããŒã¿åã«ã¯IntegerãStringãBooleanãªã©ããããããããç°ãªãçš®é¡ã®ããŒã¿ãæ±ããŸãã
å€æ°ã«å€ãå²ãåœãŠãã«ã¯ãåã«å€æ°åã®åŸã«ã=ãèšå·ãšå€ã眮ããŸããäŸãšããŠããmyVar = 10ãã¯å€æ°’myVar’ã«10ãå²ãåœãŠãŸãããŸããVBAã«ã¯å€æ°ã®ã¹ã³ãŒããå¶åŸ¡ããããã®ãPublicãããPrivateããšãã£ãããŒã¯ãŒããååšããŸãã
é©åãªããŒã¿ã®æ±ãæ¹ã¯ããšã©ãŒãé²ããããã°ã©ã ãæé©åããã®ã«åœ¹ç«ã¡ãŸããåžžã«ããŒã¿åãæèããå€æ°ãé©åã«äœ¿çšããŸãããã
3.2 ã«ãŒããšæ¡ä»¶åå²ã®åºç€
ã«ãŒãã¯ã決ããããæ¡ä»¶ãTrueã§ããéãåãã³ãŒãã®ãããã¯ãç¹°ãè¿ãå®è¡ããããã«äœ¿ããŸããVBAã§ãã䜿ãããã«ãŒãã«ã¯ãFor NextãããDo Whileããªã©ããããŸããäŸãã°ããFor i = 1 To 10ãã¯ãiã1ãã10ãŸã§å€åããéãã«ãŒãå ã®ã³ãŒããç¹°ãè¿ããŸãã
äžæ¹ãæ¡ä»¶åå²ã¯ç¹å®ã®æ¡ä»¶ã«åºã¥ããŠç°ãªãã¢ã¯ã·ã§ã³ãåãããã«äœ¿çšãããŸãããIfãæã¯ãã®äžã€ã§ããIf condition Thenãã®åŸã«ç¶ãã³ãŒãã¯ãæå®ããæ¡ä»¶ãTrueã®ãšãã«ã®ã¿å®è¡ãããŸããæ¡ä»¶ãè€æ°ããå Žåã«ã¯ãElseIfãããElseãã䜿çšããŠè¿œå ã®æ¡ä»¶ãæå®ããããšãã§ããŸãã
ãããã®ã«ãŒããšæ¡ä»¶åå²ã¯ãèªååã¹ã¯ãªããã«äžå¯æ¬ ã§ããã倧éã®ããŒã¿ã®åŠçããæ¡ä»¶ã«å¿ããç°ãªãæäœãè¡ãããã«ãã°ãã°çšããããŸãã
3.3 ãšã¯ã»ã«ãªããžã§ã¯ãã®æäœ
Excelãªããžã§ã¯ãã¢ãã«ã¯ãVBAãå©çšããŠExcelå ã®æ§ã ãªèŠçŽ ïŒã»ã«ãã·ãŒããã¯ãŒã¯ããã¯ãªã©ïŒãæäœããããã®ãã®ã§ããäŸãã°ããWorksheets(“Sheet1”).Range(“A1”)ãã¯ãSheet1å ã®A1ã»ã«ãæããŸãããªããžã§ã¯ãã«ã¯ããããã£ãã¡ãœãããããããããã䜿ã£ãŠãªããžã§ã¯ãã®ç¶æ ãå€æŽããããç¹å®ã®æäœãè¡ã£ããããããšãã§ããŸãã
ãªããžã§ã¯ãã«ã¯éå±€ããããããšãã°ãApplicationãã¯ExcelèªäœãããWorkbookãã¯åã ã®ãšã¯ã»ã«ãã¡ã€ã«ãããWorksheetãã¯ãã¡ã€ã«å ã®ã·ãŒããããããæããªããžã§ã¯ãã§ãããªããžã§ã¯ãéãæ£ããããã²ãŒãããããšã§ãå¹æçã«Excelãå¶åŸ¡ããããšãåºæ¥ãŸãã
ãªããžã§ã¯ãã®æäœããã¹ã¿ãŒããã«ã¯ããªããžã§ã¯ãã¢ãã«ã®ç解ãäžå¯æ¬ ã§ãã詳现ãªãªããžã§ã¯ãã®ãªãã¡ã¬ã³ã¹ã確èªããªãããæ £ããããšãéèŠã§ãã
3.4 é¢æ°ãšãµãã«ãŒãã³ã®äœ¿ãæ¹
é¢æ°ãšãµãã«ãŒãã³ã¯ãã³ãŒããã¢ãžã¥ãŒã«åãåå©çšå¯èœã«ããããã®VBAã®åŒ·åãªæ©èœã§ãããµãã«ãŒãã³ïŒSubããã·ãŒãžã£ïŒã¯ãç¹å®ã®ã¿ã¹ã¯ãå®è¡ããããã®ã³ãŒããããã¯ã§ã’Call’ããŒã¯ãŒãã䜿çšããŠåŒã³åºãããŸããäŸãã°ããSub MySub()ããããEnd Subããšãããããã¯ã¯ã’MySub’ãšããåã®ãµãã«ãŒãã³ãå®çŸ©ããŸãã
äžæ¹ãé¢æ°ïŒFunctionããã·ãŒãžã£ïŒã¯ãåŒã³åºãå ã«å€ãè¿ããµãã«ãŒãã³ã§ããããã«ãããèšç®çµæãæäœçµæãªã©ãåãåãããããä»ã®éšåã§å©çšã§ããŸããäŸãšããŠããFunction MyFunction(x As Integer) As Integerããšå®çŸ©ãããé¢æ°ã¯ãæŽæ°åã®åŒæ°ãåãåããæŽæ°åã®å€ãè¿ããŸãã
é©åãªé¢æ°ãšãµãã«ãŒãã³ã®äœ¿ãæ¹ã¯ãã³ãŒããæŽçããå¯èªæ§ãé«ããããã«éåžžã«åœ¹ç«ã¡ãŸããåå©çšå¯èœãªã³ãŒãã®ãããã¯ãäœæããããšã§ãããå¹ççã«ããã°ã©ã ãéçºããä¿å®ããããšãã§ããŸãã
4. 10åã§ã§ããïŒå®è·µVBAã¹ã¯ãªãã
4.1 è¡šã®ããŒã¿ãèªåæŽçãããã¯ã
Excelã§èšå€§ãªããŒã¿ãæ±ã£ãŠãããšããã®æŽçã«ã¯ãŸãããã€ãã®æéãããããŸããVBAã䜿çšããããšã§ããããã®äœæ¥ãèªååããæéãç¯çŽããããšãã§ããŸãã
ããšãã°ãåã®ããŒã¿ãåºã«ããŠè¡ãæé ãŸãã¯éé ã«ãœãŒããããå Žåãæ°åã®ã¯ãªãã¯ã§ãã¯ããèšé²ããåŸãããã®ãã¯ããåçãããããšãå¯èœã§ãããã®ããã»ã¹ã¯ãVBAãšãã£ã¿ã«ã¢ã¯ã»ã¹ãã’Sort’ã¡ãœããã䜿çšããããšã§ã³ãŒããæžãæããããšã§ãå®çŸã§ããŸãã
ããäžæ©é²ãã§ãç¹å®ã®æ¡ä»¶ãæºããããŒã¿ã ãããã£ã«ã¿ãªã³ã°ããå¥ã®ã·ãŒãã«ã³ããŒãããããªèªåæŽçãVBAã䜿ã£ãŠç°¡åã«å®è£ ã§ããŸããå°éç¥èããªããŠãããããäžã«ã¯å€ãã®ãµã³ãã«ã³ãŒãããããããããããåèã«ããŠèªåã®ããŒãºã«åãããŠã«ã¹ã¿ãã€ãºããããšãå¯èœã§ãã
4.2 ã»ã«ã®æ¡ä»¶ã«å¿ããèªåè²ä»ã
æ°å€ãç¹å®ã®ç¯å²ã«ããããç¹å®ã®æååãå«ãããªã©ãã»ã«ã®ã³ã³ãã³ãã«ãã£ãŠè²ä»ããèªååããããšã¯ãããŒã¿ã®èŠèŠçãªåæãå©ããŸããããã¯ãVBAã®’Conditional Formatting’æ©èœã䜿çšããããšã§å®¹æã«å®è£ ã§ããŸãã
äŸãã°ã売äžãç®æšãäžåã£ãŠããã»ã«ãèµ€ãããããšã§ãããã«åé¡ã®ããç®æãç¹å®ã§ããŸãããã®å Žåã’Range’ãªããžã§ã¯ããš’Interior.Color’ããããã£ã䜿ã£ãŠãã¹ã¯ãªããå ã§è²ã®æå®ãè¡ããŸãã
ããã«ãæ¡ä»¶åŒã䜿ã£ãŠè€æ°ã®ã«ãŒã«ãé©çšããããšãã§ããŸããããã«ãããã»ã«ããšã«ç°ãªãè²ãåçã«é©çšããããšã§ãããŒã¿ãããçŽæçã«ç解ããããšãã§ããŸãã
4.3 ãŠãŒã¶ãŒå ¥åãã©ãŒã ã®äœæ
ããŒã¿å ¥åã¯ãã³ãã³æéããããäœæ¥ã§ãããŠãŒã¶ãŒãã¬ã³ããªãŒãªå ¥åãã©ãŒã ã¯ããã®éçšãå€§å¹ ã«ç°¡æœã«ããŠãããŸããVBAã䜿çšããŠãã«ã¹ã¿ã å ¥åãã©ãŒã ãç°¡åã«äœæã§ããããã«ãã£ãŠããŒã¿ãšã³ããªã®å¹çãåäžããŸãã
ãã®ãããªãã©ãŒã ãäœæããã«ã¯ãVBAã®’UserForm’ã䜿ã£ãŠãã¶ã€ã³ãè¡ããããã¹ãããã¯ã¹ããã¿ã³ãªã©ã®ã³ã³ãããŒã«ãé 眮ããŸãããããŠããããã®ã³ã³ãããŒã«ã«é©åãªã³ãŒããå²ãåœãŠãããšã§ãããŒã¿å ¥åãåŠçã®èªååãå³ããŸãã
å ¥åãããããŒã¿ã®æ€èšŒããã·ãŒãã«ããŒã¿ã転éããããžãã¯ãçµã¿èŸŒãããšã§ããšã©ãŒã®å¯èœæ§ãæžãããæ£ç¢ºãªããŒã¿ã®åéãå©ããããšãã§ããŸãã
4.4 ã¡ãŒã«ã§ã¬ããŒããèªåéä¿¡ãããã¯ã
æ¯æ¥ãŸãã¯æ¯é±ã®ã¬ããŒããæåã§éä¿¡ããŠããå ŽåãVBAã掻çšããŠãã®éçšãèªååããããšã§ãéåžžã«æéãç¯çŽããããšãã§ããŸããVBAãä»ããŠOutlookãä»ã®ã¡ãŒã«ã¯ã©ã€ã¢ã³ããšé£æºããã¹ãã¬ããã·ãŒãããçŽæ¥ã¡ãŒã«ãéä¿¡ã§ããŸãã
ãã®ã¿ã€ãã®ãã¯ãã¯ãã¬ããŒããå®äºããåŸã«ããªã¬ãŒãããããèšå®ããããšãå¯èœã§ãããŸããã¬ããŒããæ·»ä»ãã¡ã€ã«ãšããŠå ããããæ¬æã®äžéšãšããŠããŒã¿ãæ¿å ¥ããããéžæã§ããŸãã
ãªããžã§ã¯ããšã㊒Application’ãš’Outlook.Application’ã䜿ãããšã§ãã¡ãŒã«ãäœæããå®å ãCCãBCCã件åããããŠã¡ãã»ãŒãžæ¬æãæå®ããŠéä¿¡ããã³ãŒããæžãããšãã§ããŸããããã«ãããäžè²«ããå ±åæžãæééãã«éãããšã容æã«ãªããŸãã
5. ãã©ãã«ã·ã¥ãŒãã£ã³ã°ãšãããã°ã®ã³ã
Visual Basic for ApplicationsïŒVBAïŒã䜿çšããéã«ã¯ããã©ãã«ã·ã¥ãŒãã£ã³ã°ãšãããã°ãéåžžã«éèŠã§ãããã®ã»ã¯ã·ã§ã³ã§ã¯ãäžè¬çãªãšã©ãŒãžã®å¯Ÿå¿æ¹æ³ãå¹æçãªã³ãŒããããã°ã®ã¢ãããŒããããã©ãŒãã³ã¹åäžã®ãã¯ããã¯ããããŠVBAã³ãŒãã®ä¿è·ã«ã€ããŠè§£èª¬ããŸãããããã®ã³ããæŒãããŠããããšã§ãVBAããã°ã©ãã³ã°ã®äœæ¥å¹çãé£èºçã«åäžããã§ãããã
5.1 ãããããšã©ãŒãšãã®å¯ŸåŠæ³
ãããããšã©ãŒã®äžã€ã«ãå€æ°ãæªå®çŸ©ã§ããã±ãŒã¹ããããŸããããã¯ãOption Explicitã¹ããŒãã¡ã³ãããã¡ã€ã«ã®å é ã«èšè¿°ããå€æ°ã䜿çšããåã«å®£èšããããšã§é²ãããšãå¯èœã§ãããŸãããªããžã§ã¯ãåãäžèŽããªãå Žåã«åºããåãäžèŽããŸããïŒType MismatchïŒããšã©ãŒãäžè¬çã§ããå€æ°ãšããŒã¿åãé©åãã確èªããå¿ èŠã«å¿ããŠãã£ã¹ããå€æãè¡ããŸãããã
ãšã©ãŒãã³ããªã³ã°ã«ã¯ãOn Errorã¹ããŒãã¡ã³ããå©çšãããšè¯ãã§ãããããšã©ãŒãèµ·ããå Žåã«ç¹å®ã®åŠçãè¡ãããšã©ãŒæ å ±ãèšé²ãããªã©ããŠããã°ã©ã ã®å®å®æ§ãé«ããããŸãããšã©ãŒã¡ãã»ãŒãžã®å 容ã粟èªããäžæç¹ãããã°ãªã³ã©ã€ã³ã§ã®æ å ±æ€çŽ¢ãã³ãã¥ããã£ãžã®çžè«ãæå¹ã§ãã
å ããŠãé åã®ç¯å²å€ã«ã¢ã¯ã»ã¹ããããšããéã®ãSubscript out of rangeããšã©ãŒããååšããªããªããžã§ã¯ããã³ã¬ã¯ã·ã§ã³ã«ã¢ã¯ã»ã¹ãããObject requiredãã®ãšã©ãŒã®å¯ŸåŠæ³ãç解ããããšããé »åºãããã°ã解決ããäžã§åœ¹ç«ã¡ãŸãã
5.2 ã³ãŒãã®ãããã°æ¹æ³
ãããã°ãè¡ãå ŽåããŸãã¯VBAãšãã£ã¿ã®ãã¬ãŒã¯ãã€ã³ãïŒF9ããŒïŒã掻çšããããã°ã©ã ã®å®è¡ãäžæåæ¢ãããããšãéèŠã§ããããã«ãããããã°ã©ã ã®ããæç¹ã§ã®å€æ°ããªããžã§ã¯ãã®ç¶æ ã確èªããåé¡ã®åå ã远究ããããšãã§ããŸãã
ã¹ãããã€ã³ïŒF8ããŒïŒã䜿ã£ãŠã³ãŒãã®åè¡ãäžè¡ãã€å®è¡ããäºæããªãåäœãçºçããŠããéšåãç¹å®ããŸãããããŸãããŠã©ããåŒãèšå®ããŠç¹å®ã®å€æ°ãã¢ãã¿ãªã³ã°ããããã€ããã£ãšã€ããŠã£ã³ããŠãçšããŠä»»æã®ã³ãŒããå®è¡ãããããããšãéåžžã«å¹æçã§ãã
ãããã°äžã«ã³ãŒãã®ãªãã¡ã¯ã¿ãªã³ã°ãè¡ãããšãæã«ã¯å¿ èŠã§ããè€éãªè¡šçŸãã·ã³ãã«ã«ããããšã§èªã¿ããã管çããããã³ãŒãã«ãªãããšã©ãŒã®åå ã解æãããããªããŸããæž æœãªã³ãŒãã¯ãããã°ã容æã«ããå°æ¥çãªãã©ãã«ã·ã¥ãŒãã£ã³ã°ã®æéãççž®ããŸãã
5.3 ããã©ãŒãã³ã¹ã®æ¹åæè¡
VBAã³ãŒãã®ããã©ãŒãã³ã¹åäžã®ããã«ã¯ãã«ãŒãã®æé©åããããŸããäžèŠãªã«ãŒããçãããŸãã¯For Eachã«ãŒãã䜿ãããªãããšã§ãå®è¡é床ãæ¹åã§ããŸããå ããŠãèšç®çµæããã£ãã·ã¥ããå埩çã«åãå€ãèšç®ãããªããã工倫ããããšãéèŠã§ãã
次ã«ãExcelã®ã¹ã¯ãªãŒã³ã¢ããããŒãïŒApplication.ScreenUpdatingïŒãèªåèšç®ïŒApplication.CalculationïŒã®èšå®ãã³ã³ãããŒã«ããããšã§ãè©Šç®è¡šã®åèšç®ãç»é¢ã®åæç»ãæå¶ããããã©ãŒãã³ã¹ãåäžãããããšãã§ããŸããç¹ã«å€§éã®ããŒã¿ãè€éãªèšç®ãè¡ããã¯ãã§ã¯ããããã®èšå®ã®èª¿æŽãåçãªå¹æãããããããšããããŸãã
VBAãããžã§ã¯ãå ã§ã¢ãžã¥ãŒã«ãé©åã«åå²ããåå©çšå¯èœãªããã·ãŒãžã£ãé¢æ°ãäœæããããšããã³ãŒãã®å¹çåã«ç¹ãããŸããã¢ãžã¥ãŒã«ã®åå²ã¯ãã¡ã³ããã³ã¹ããããããªãã ãã§ãªãããã£ãã·ã¥ã¡ã¢ãªã®å©çšãæ¹åããããšã«ã圹ç«ã¡ãŸãã
5.4 VBAã³ãŒãã®ä¿è·ãšã»ãã¥ãªãã£
VBAã³ãŒãã®å®å šæ§ã確ä¿ããããã«ã¯ããŸããããžã§ã¯ãã«ãã¹ã¯ãŒããèšå®ããããšããå§ãããŸããããã«ãããäžæ£ã¢ã¯ã»ã¹ããã³ãŒããä¿è·ããæ æãŸãã¯èª€ã£ãŠã®ã³ãŒãå€æŽãé²æ¢ããããšãã§ããŸãããã ãããã¹ã¯ãŒãã¯ç Žãããå¯èœæ§ããããããéå ¬éæ å ±ããã¯ãã«å«ããããšã¯é¿ããŸãããã
次ã«ãä¿¡é Œã§ããåºå žããã®ã¿ãã¯ããå®è¡ããããã«ããäžå¯©ãªãã¯ããæ·»ä»ããããã¡ã€ã«ãéããªãããšãéèŠã§ããExcelã«ã¯ãã¯ãã®å®è¡ãèš±å¯ããåã«èŠåã衚瀺ããã»ãã¥ãªãã£æ©èœããããŸãããã®æ©èœãæå¹ã«ããŠããããšã§ãæå³ããªãå®è¡ãé²ãããšãå¯èœã§ãã
æåŸã«ãä¿¡é Œã§ããããžã¿ã«èšŒææžã䜿çšããŠãã¯ããããžã§ã¯ãã«çœ²åããããšã§ããã¡ã€ã«ã®å®å šæ§ãšåºå žã®ç¢ºå®æ§ãä¿èšŒããŸãããã¯ããå ¬éããéã¯ããã®ãããªã»ãã¥ãªãã£æªçœ®ãè¬ããããšã§ããšã³ããŠãŒã¶ãŒã®ä¿¡é Œãç²åŸã§ããã§ãããã
6. VBAãåŠã¶äžã§ã®è¿œå ãªãœãŒã¹
VBAïŒVisual Basic for ApplicationsïŒã¯ãMicrosoft Officeã¢ããªã±ãŒã·ã§ã³ãèªååãŸãã¯æ¡åŒµããããã«äœ¿çšãããããã°ã©ãã³ã°èšèªã§ããVBAãç¿åŸããããšã§ãæ¥åžžã®ã¿ã¹ã¯ãå¹çåããã«ã¹ã¿ã æ©èœãäœæããããšãã§ããããã«ãªããŸããããã§ã¯ãVBAãã¹ã¿ãŒãžã®éãå¹æçã«ãµããŒãããããã®è¿œå ãªãœãŒã¹ã«ã€ããŠçŽ¹ä»ããŸãã
6.1 ãªã³ã©ã€ã³ãã¥ãŒããªã¢ã«ãšã³ãŒã¹
ãªã³ã©ã€ã³ã§VBAãåŠã¶ã®ã«ã¯æ§ã ãªãã¥ãŒããªã¢ã«ãã³ãŒã¹ããããŸããåå¿è ããäžçŽè ãŸã§ãæ§ã ãªã¹ãã«ã¬ãã«ã«å¯Ÿå¿ããå 容ãæäŸãããŠããŸãããããã®ãªãœãŒã¹ã¯ãèªå® ã§èªåã®ããŒã¹ã§åŠã¹ãç¹ãé åçã§ãã
UdemyãCourseraãªã©ã®ãã©ãããã©ãŒã ã§ã¯ãVBAã«ç¹åããã³ãŒã¹ãæäŸããŠããããããªè¬çŸ©ãã¯ã€ãºããããžã§ã¯ãããŒã¹ã®èª²é¡ãéããŠåŠç¿ãæ·±ããããšãã§ããŸãããŸããäžéšã®ã³ãŒã¹ã§ã¯ãä¿®äºèšŒãè³æ ŒèšŒæã®ååŸãå¯èœã§ãã
ç¡æã§ã¢ã¯ã»ã¹ã§ããYouTubeãã¥ãŒããªã¢ã«ããããVBAã®åºæ¬çãªæŠå¿µãã³ãŒãã£ã³ã°ã®ãã¢ã³ã¹ãã¬ãŒã·ã§ã³ãèŠãããšãã§ããŸãããããã®ãããªã¯ãçãæéã§ç¹å®ã®ãããã¯ãè£è¶³ããã®ã«åœ¹ç«ã¡ãŸãã
6.2 æé©ãªæžç±ãšã¬ã€ãã®çŽ¹ä»
VBAãåŠã¶ã«ã¯ãå°éçãªæžç±ãšã¬ã€ããéåžžã«åœ¹ç«ã¡ãŸãããããã®è³æã¯ãå ·äœçãªã³ãŒãäŸãšå ±ã«ã詳现ãªèª¬æãæäŸããããšã§ãç解ãæ·±ããããšãã§ããŸãã
ãExcel VBAããã°ã©ãã³ã° ãã€ãã«ãããã¹ããããã€ã¹ãããã§åŠã¶VBAããªã©ã®æžç±ã¯ãäœç³»çãªåŠç¿ãå¯èœã§ãæ¬ãèªã¿é²ããããšã§åŸã ã«ã¹ãã«ãæ§ç¯ã§ããŸãããŸãããVBA For Dummiesãã®ã·ãªãŒãºã¯ãåå¿è ã«ãç解ããããã¢ãããŒããæäŸããŠããŸãã
é»åæžç±ãã³ã³ãã³ãã®é åžãè¡ã£ãŠãããµã€ããå€ãããªãã©ã€ã³ã§ã®åŠç¿ãã移åäžã®ãªãŒãã£ã³ã°ã«é©ããŠããŸãããŸããããã°ããªã³ã©ã€ã³ã¢ãŒãã£ã¯ã«ã¯ææ°ã®ãã¬ã³ããæè¡çãªãã³ããåŸãã®ã«åœ¹ç«ã¡ãŸãã
6.3 å©ããæ±ããããã®ãã©ãŒã©ã ãšã³ãã¥ããã£
åŠç¿äžã«çåãå°é£ã«çŽé¢ããæããã©ãŒã©ã ãã³ãã¥ããã£ã¯ãµããŒããšäº€æµã®å ŽãšããŠéåžžã«æå¹ã§ããããã§ã¯ãVBAã«é¢ãã質åãæçš¿ããããä»ã®å©çšè ã®è§£æ±ºçãèŠããããããšãã§ããŸãã
ãStack Overflowãã¯ããã°ã©ããŒãéãŸã代衚çãªãã©ãŒã©ã ã§ãVBAã«é¢ããå€çš®å€æ§ãªãããã¯ãæ¥ã æçš¿ãããŠããŸãããŸãããVBA Express Forumãã§ã¯VBAã®ãšãã¹ããŒããã¢ããã€ã¹ãæäŸããŠãããŸãã
ç¹å®ã®ãœãããŠã§ã¢ãæè¡ã«ç¹åããã³ãã¥ããã£ãååšããäŸãã°ãMicrosoft Office Forumãã§ã¯ãç¹ã«Microsoft Office補åã«ãããVBAã®äœ¿çšã«ã€ããŠã®æ å ±äº€æã掻çºã«è¡ãããŠããŸãã
6.4 ã¹ãã«åäžã®ããã®å®è·µçãªãããžã§ã¯ãæ¡
çè«ãåŠãã åŸã«éèŠãªã®ã¯å®è·µã§ããVBAã®ç¥èãæ·±ããã¹ãã«ãåäžãããã«ã¯å®éã«ãããžã§ã¯ãã«åãçµãããšãå§ããããŸããå°ããªãããžã§ã¯ãããå§ããŠåŸã ã«é£æ床ãäžããŠããããšãæé·ã«ç¹ãããŸãã
äŸãã°ãExcelã®ããŒã¿åæãèªååãããã¯ããäœæãããOutlookã§ã®èªåã¡ãŒã«éä¿¡æ©èœãæ§ç¯ãããAccessããŒã¿ããŒã¹ã§ã®ã¬ããŒãçæããŒã«ãéçºãããªã©ã䜿çšã·ããªãªã«æ²¿ã£ããããžã§ã¯ããè©ŠããŠã¿ãŸãããã
èªåèªèº«ã®æ¥åžžæ¥åã®äžã§ç¹°ãè¿ãè¡ã£ãŠããäœæ¥ãèªååããããšã¯ãVBAã®åŠç¿ã ãã§ãªããå人ã®çç£æ§åäžã«ã圹ç«ã¡ãŸãããã®ãããªãããžã§ã¯ãã¯ãå®éã®è·å Žã§ã®å¿çšã«çŽçµãããããåŠãã ã¹ãã«ãå³åº§ã«äŸ¡å€ãçã¿åºãè¯ãäŸã§ãã
7. ãŸãšã
VBAããŸãã¯Visual Basic for Applicationsã¯ããžãã¹ã®çŸå Žã§ãã䜿çšãããããã°ã©ãã³ã°èšèªã§ãããã®ã¬ã€ãã§ã¯ãVBAã®åºæ¬ããå¿çšãŸã§ææ©ãåŠã¶ãã€ã³ãããããããã玹ä»ããŸããVBAã¯ãExcelãªã©ã®ãªãã£ã¹ãœããã«çµã¿èŸŒãŸããéçºç°å¢ã§çšããããæ¥åã®èªååã«åœ¹ç«ã€ãã¯ããã¹ã¯ãªãããäœæããŸããããã°ã©ãã³ã°ã®åå¿è ã«ãç解ãããããããžãã¹ããŒãœã³ã«ã¯ãã®å©ç¹ãšå¹çåã®ããã³ã·ã£ã«ã倧å€é åçã§ãããã®çããŸãšããéããŠVBAã®äžçãžã®ç¬¬äžæ©ãèžã¿åºãããšã¯ã»ã«äœæ¥ãèªååããããžãã¹ããã»ã¹ãæ¹åããæ¹æ³ãåŠã³ãŸãããã