3 VBA の基本構文

Microsoft Excel VBA ฼⏝䛾ᡭ䜋䛹䛝 2007/2010 ᑐᛂ
3 VBA 䛾ᇶᮏᵓᩥ
3 ❶䛷䛿 VBA 䛾ᇶᮏᵓᩥ䛻䛴䛔䛶⤂௓䛧䜎䛩䚹
3.1 䜸䝤䝆䜵䜽䝖
VBA 䛜⾜䛖ฎ⌮䛾ᑐ㇟䜢䛂䜸䝤䝆䜵䜽䝖䛃䛸䛔䛔䜎䛩䚹
VBA 䛷䛿ฎ⌮ᑐ㇟䛾䜸䝤䝆䜵䜽䝖䜢ᣦᐃ䛧䛶䚸≧ែ䠄䝥䝻䝟䝔䜱䠅䜢ኚ໬䛥䛫䛯䜚䚸ືస࿨௧䠄䝯䝋䝑䝗䠅䜢
࿨䛨䛯䜚䛧䛶ฎ⌮䜢⾜䛔䜎䛩䚹
౛䠅㻌 䝤䝑䜽䛂⦎⩦.xlsm䛃䛾䝽䞊䜽䝅䞊䝖䛂Sheet1䛃䛾䝉䝹䛂A5䛃䛾್䜢 100 䛻䛩䜛䚹
Workbooks(“⦎⩦.xlsm”).Worksheets(“Sheet1 ”).Range(“A5”) .Value = 100
䜸䝤䝆䜵䜽䝖
䝥䝻䝟䝔䜱
౛䠅㻌 䝤䝑䜽䛂⦎⩦.xlsm䛃䛾䝽䞊䜽䝅䞊䝖䛂Sheet1䛃䛾䝉䝹䛂A5䛃䜢䜽䝸䜰䛩䜛䚹
Workbooks(“⦎⩦.xlsm”).Worksheets(“Sheet1 ”).Range(“A5”) .Clear
䜸䝤䝆䜵䜽䝖
3.1.1
䝯䝋䝑䝗
䜸䝤䝆䜵䜽䝖䛾グ㏙᪉ἲ
䜸䝤䝆䜵䜽䝖䛿㝵ᒙᵓ㐀䛾䛯䜑䚸䜸䝤䝆䜵䜽䝖䜢ᣦᐃ䛩䜛䛸䛝䛿䚸ୖ఩䛾㝵ᒙ䛛䜙ୗ఩䛾㝵ᒙ䜈
䛂䠊䠄䝗䝑䝖䠅䛃䛷༊ษ䛳䛶グ㏙䛧䜎䛩䚹
Excel 䛾୺䛺䜸䝤䝆䜵䜽䝖䛻䛿䚸ḟ䛾䜒䛾䛜䛒䜚䜎䛩䚹
Application ············ Excel 䛭䛾䜒䛾䜢⾲䛩᭱䜒ୖ఩䛾䜸䝤䝆䜵䜽䝖
Workbook ············· 䝤䝑䜽䜢⾲䛩䜸䝤䝆䜵䜽䝖
Worksheet ············ 䝽䞊䜽䝅䞊䝖䜢⾲䛩䜸䝤䝆䜵䜽䝖
Range ·················· 䠍䛴䛾䝉䝹䚸䛒䜛䛔䛿」ᩘ䛾䝉䝹䜢⾲䛩䜸䝤䝆䜵䜽䝖
ฎ⌮䛾ᑐ㇟䛸䛺䜛䜸䝤䝆䜵䜽䝖䜢グ㏙䛩䜛ሙྜ䚸௨ୗ䛾᭩ᘧ䛷グ㏙䛧䜎䛩䚹
᭩ᘧ
䝁䝺䜽䝅䝵䞁ྡ䠄"䝯䞁䝞ྡ"䠅
䝁䝺䜽䝅䝵䞁ྡ䠄䜲䞁䝕䝑䜽䝇␒ྕ䠅
䈜㻌 䝁䝺䜽䝅䝵䞁ྡ䛾䜏ᣦᐃ䛧䛯ሙྜ䛿䚸䝁䝺䜽䝅䝵䞁䛻ྵ䜎䜜䜛䛩䜉䛶䛾䝯䞁䝞䛜ฎ⌮䛾ᑐ㇟䛸䛺䜚䜎䛩䚹
౛䠅㻌 Worksheet 䜸䝤䝆䜵䜽䝖䛾୰䛾䝽䞊䜽䝅䞊䝖䛂Sheet1䛃䚹
Worksheets(“Sheet1 ”)
䝁䝺䜽䝅䝵䞁
䝯䞁䝞ྡ
౛䠅㻌 Worksheet 䜸䝤䝆䜵䜽䝖䛾୰䛾ᕥ䛛䜙 1 ␒┠䛾䝽䞊䜽䝅䞊䝖䚹
Worksheets(1)
䝁䝺䜽䝅䝵䞁 䜲䞁䝕䝑䜽䝇␒ྕ
䕔 䝁䝺䜽䝅䝵䞁
ྠ䛨✀㢮䛾䜸䝤䝆䜵䜽䝖䛾㞟䜎䜚䜢䛂䝁䝺䜽䝅䝵䞁䛃䛸࿧䜃䜎䛩䚹䜋䛸䜣䛹䛾ሙྜ䝁䝺䜽䝅䝵䞁ྡ䛿䚸䜸䝤䝆
䜵䜽䝖ྡ䛾」ᩘᙧ䛻䛺䛳䛶䛔䜎䛩䚹
䕔 䝯䞁䝞
䝁䝺䜽䝅䝵䞁䛻ྵ䜎䜜䜛ྛ䜸䝤䝆䜵䜽䝖䜢䛂䝯䞁䝞䛃䛸࿧䜃䜎䛩䚹䝯䞁䝞ྡ䛿䛂䇾(䝎䝤䝹䜽䜷䞊䝔䞊䝅䝵䞁)䛃䛷
ᅖ䜣䛷ᣦᐃ䛧䜎䛩䚹
䕔 䜲䞁䝕䝑䜽䝇␒ྕ
䝯䞁䝞䛻௜䛡䜙䜜䜛␒ྕ䛜䛂䜲䞁䝕䝑䜽䝇␒ྕ䛃䛷䛩䚹䝽䞊䜽䝅䞊䝖䛾ሙྜ䛿䚸ᕥ䛛䜙㡰␒䛻␒ྕ䛜௜䛡
䜙䜜䚸䝤䝑䜽䛾ሙྜ䛿㛤䛔䛯㡰␒䛻␒ྕ䛜௜䛡䜙䜜䜎䛩䚹
26
Microsoft Excel VBA ฼⏝䛾ᡭ䜋䛹䛝 2007/2010 ᑐᛂ
····· Excel 䛾୺䛺䝁䝺䜽䝅䝵䞁 ········································································································
Excel 䛷౑⏝䛩䜛୺䛺䝁䝺䜽䝅䝵䞁䛻䛿䚸௨ୗ䛾䜒䛾䛜䛒䜚䜎䛩䚹
䝁䝺䜽䝅䝵䞁
Sheets
Worksheets
Charts
Workbooks
ෆᐜ
䝤䝑䜽䛻䛒䜛䛩䜉䛶䛾䝅䞊䝖䜢⾲䛩䝁䝺䜽䝅䝵䞁䛷䛩䚹
䝯䞁䝞䛻䛿 Chart 䜸䝤䝆䜵䜽䝖䜔 Worksheet 䜸䝤䝆䜵䜽䝖䛜ྵ䜎䜜䜎䛩䚹
䝤䝑䜽䛻䛒䜛䛩䜉䛶䛾䝽䞊䜽䝅䞊䝖䛾䝁䝺䜽䝅䝵䞁䛷䛩䚹
䝯䞁䝞䛻䛿 Worksheet 䜸䝤䝆䜵䜽䝖䛜ྵ䜎䜜䜎䛩䚹
䝤䝑䜽䛻䛒䜛䛩䜉䛶䛾䜾䝷䝣䝅䞊䝖䛾䝁䝺䜽䝅䝵䞁䛷䛩䚹
䝯䞁䝞䛻䛿 Chart 䜸䝤䝆䜵䜽䝖䛜ྵ䜎䜜䜎䛩䚹
⌧ᅾ㛤䛛䜜䛶䛔䜛䛩䜉䛶䛾䝽䞊䜽䝤䝑䜽䛾䝁䝺䜽䝅䝵䞁䛷䛩䚹
䝯䞁䝞䛻䛿 Workbook 䜸䝤䝆䜵䜽䝖䛜ྵ䜎䜜䜎䛩䚹
··············································································································································
3.1.2
Range 䜸䝤䝆䜵䜽䝖䛾฼⏝
䝉䝹䛾᧯స䜢⾜䛖䛻䛿 Range 䜸䝤䝆䜵䜽䝖䜢౑⏝䛧䜎䛩䚹Range 䜸䝤䝆䜵䜽䝖䛻䛿䝁䝺䜽䝅䝵䞁䛿Ꮡᅾ䛫
䛪䚸1 䛴䛾䝉䝹䛷䜒」ᩘ䛾䝉䝹䛷䜒 Range 䜸䝤䝆䜵䜽䝖䛸䛧䛶౑⏝䛧䜎䛩䚹Range 䜸䝤䝆䜵䜽䝖䛿䚸ᣓᘼ
ෆ䛻䝉䝹␒ᆅ䜢䛂”䠄䝎䝤䝹䜽䜷䞊䝔䞊䝅䝵䞁䠅䛃䛷ᅖ䜣䛷グ㏙䛧䜎䛩䚹
౛ 1䠅
౛ 2䠅
౛ 3)
Range(“A1”)
Range(“A1䠈D5”)
Range(“A3䠖D5”)
䕔 ᧯ స 䕔
䝉䝹 A1
䝉䝹 A1 䛸 D5
䝉䝹⠊ᅖ A3䠖D5
Range 䜸䝤䝆䜵䜽䝖䜢ᣦᐃ䛩䜛
䝤䝑䜽䛂ᇶᮏᵓᩥ.xlsm䛃㛤䛝䚸䝉䝹 D3 䛻್䜢ධຊ䛩䜛䝥䝻䝅䞊䝆䝱䛂Koubun1䛃䜢సᡂ䛧䜎䛩䚹
1. 䝤䝑䜽䛂ᇶᮏᵓᩥ.xlsm䛃㛤䛝䚸ᶆ‽䝰䝆䝳䞊䝹䜢ᤄධ䛧䜎䛩䚹
2. 䝥䝻䝅䞊䝆䝱䛂Koubun1䛃䜢సᡂ䛧䛶ḟ䛾䝁䞊䝗䜢ධຊ䛧䜎䛩䚹
3. Sheet1 䜢䜽䝸䝑䜽䛧䛶䚸䝥䝻䝅䞊䝆䝱䜢ᐇ⾜䛧䜎䛩䚹
4. Sheet1 䛾䝉䝹 D3 䛻䛂100䛃䛜ධຊ䛥䜜䛯䛣䛸䜢☜ㄆ䛧䜎䛩䚹
5. Sheet2 䜢䜽䝸䝑䜽䛧䛶䚸䝥䝻䝅䞊䝆䝱䜢ᐇ⾜䛧䜎䛩䚹
6. Sheet2 䛾䝉䝹 D3 䛻䛂100䛃䛜ධຊ䛥䜜䛯䛣䛸䜢☜ㄆ䛧䜎䛩䚹
27
Microsoft Excel VBA ฼⏝䛾ᡭ䜋䛹䛝 2007/2010 ᑐᛂ
䕔 ᧯ స 䕔
Worksheet 䜸䝤䝆䜵䜽䝖䜢ᣦᐃ䛩䜛
䝽䞊䜽䝅䞊䝖䛂Sheet3䛃䛾䝉䝹 D3 䛻್䜢タᐃ䛩䜛䝥䝻䝅䞊䝆䝱䜢సᡂ䛧䜎䛩䚹
1. 䝥䝻䝅䞊䝆䝱䛂Koubun2䛃䜢సᡂ䛧䛶ḟ䛾䝁䞊䝗䜢ධຊ䛧䜎䛩䚹
2. Sheet3 ௨እ䛾䝽䞊䜽䝅䞊䝖䜢㑅ᢥ䛧䛶䚸䝥䝻䝅䞊䝆䝱䜢ᐇ⾜䛧䜎䛩䚹
3. Sheet3 䛾 D3 䛻䛂200䛃䛜ධຊ䛥䜜䛯䛣䛸䜢☜ㄆ䛧䜎䛩䚹
䕔 ᧯ స 䕔
Workbook 䜸䝤䝆䜵䜽䝖䜢ᣦᐃ䛩䜛
䝤䝑䜽䛂⦎⩦䠍.xlsx䛃䛾䝽䞊䜽䝅䞊䝖䛂Sheet3䛃䛾䝉䝹 D3 䛻್䜢タᐃ䛩䜛䝥䝻䝅䞊䝆䝱䜢సᡂ䛧䜎䛩䚹
1. 䝥䝻䝅䞊䝆䝱䛂Koubun3䛃䜢సᡂ䛧䛶ḟ䛾䝁䞊䝗䜢ධຊ䛧䜎䛩䚹
ͤ 䝤䝑䜽ྡ䛾඲ゅ䛸༙ゅ䛿༊ู䛥䜜䜎䛩䚹䛂⦎⩦䠍䛃䛾ᩘᏐ䛿඲ゅ䛷ධຊ䛧䛶䛟䛰䛥䛔䚹
2. 䝤䝑䜽䛂⦎⩦䠍.xlsx䛃䜢㛤䛝䜎䛩䚹
3. 䝤䝑䜽䛂ᇶᮏᵓᩥ.xlsm䛃䜢䜰䜽䝔䜱䝤䛻䛧䛶䚸䝥䝻䝅䞊䝆䝱䜢ᐇ⾜䛧䜎䛩䚹
4. 䝤䝑䜽䛂⦎⩦䠍.xlsx䛃䛾 Sheet3 䛾䝉䝹 D3 䛻䛂300䛃䛜ධຊ䛥䜜䛯䛣䛸䜢☜ㄆ䛧䜎䛩䚹
5. 䛂⦎⩦ 1.xlsx䛃䜢ಖᏑ䛫䛪䛻㛢䛨䜎䛩䚹
····· ୖ఩䜸䝤䝆䜵䜽䝖䛾┬␎ ·········································································································
Workbook 䜸䝤䝆䜵䜽䝖䜔䚸Worksheet 䜸䝤䝆䜵䜽䝖䜢┬␎䛧䛯ሙྜ䚸ᐇ⾜᫬䛻䜰䜽䝔䜱䝤䛻䛺䛳䛶䛔䜛 Workbook 䜸䝤䝆䜵䜽䝖
䜔䚸Worksheet 䜸䝤䝆䜵䜽䝖䛜᧯స䛾ᑐ㇟䛸䛺䜚䜎䛩䚹
·············································································································································
28
Microsoft Excel VBA ฼⏝䛾ᡭ䜋䛹䛝 2007/2010 ᑐᛂ
3.2 䝥䝻䝟䝔䜱
䝥䝻䝟䝔䜱䛿䚸䜸䝤䝆䜵䜽䝖䛾ᒓᛶ䠄≧ែ䜔≉ᚩ䠅䛷䛩䚹䜸䝤䝆䜵䜽䝖䛾✀㢮䛻䜘䛳䛶⏝ព䛥䜜䛶䛔䜛䝥䝻
䝟䝔䜱䛜␗䛺䜚䜎䛩䚹
3.2.1
䝥䝻䝟䝔䜱䛾฼⏝
䝥䝻䝟䝔䜱䛾฼⏝᪉ἲ䛿䚸ḟ䛾 2 ㏻䜚䛷䛩䚹
䝥䝻䝟䝔䜱䛻್䜢タᐃ䛩䜛
䝥䝻䝟䝔䜱䛾್䜢ྲྀᚓ䛩䜛
䕔 䝥䝻䝟䝔䜱䛻್䜢タᐃ
䜸䝤䝆䜵䜽䝖䛾䝥䝻䝟䝔䜱䛻್䜢タᐃ䛩䜛䛣䛸䛷䜸䝤䝆䜵䜽䝖䛾≧ែ䛜ኚ䜟䜚䜎䛩䚹
䝥䝻䝟䝔䜱䛾್䜢タᐃ䛩䜛䛻䛿䚸ḟ䛾᭩ᘧ䛷グ㏙䛧䜎䛩䚹
᭩ᘧ
䜸䝤䝆䜵䜽䝖䠊䝥䝻䝟䝔䜱 = タᐃ್
౛䠅㻌 䜰䜽䝔䜱䝤䝉䝹䛾್䜢䛂200䛃䛻䛩䜛䚹
ActiveCell.Value = 200
౛䠅 Sheet1 䛾䝅䞊䝖ྡ䜢䛂኎ୖ⾲䛃䛻䛩䜛䚹
Worksheets䠄䇾Sheet1”䠅.Name = 䇿኎ୖ⾲䇾
····· ௦ධ₇⟬Ꮚ ·························································································································
䝥䝻䝟䝔䜱䜔ኚᩘ䛻್䜢タᐃ䛩䜛䛻䛿䚸௦ධ₇⟬Ꮚ䛂䠙(䜲䝁䞊䝹)䛃䜢౑⏝䛧䜎䛩䚹௦ධ䛿䚸ྑ㎶䛛䜙ᕥ㎶䛻್䛜௦ධ䛥䜜䜎䛩䚹
ྑ㎶䛻ィ⟬ᘧ䛜タᐃ䛥䜜䛶䛔䜛ሙྜ䛿䚸ඛ䛻ィ⟬ฎ⌮䛜⾜䜟䜜䚸䛭䛾⤖ᯝ䛜ᕥ㎶䛻௦ධ䛥䜜䜎䛩䚹
౛䠅㻌 䝉䝹 B10 䛾್䜢 2 ಸ䛧䛶䝉䝹 C10 䛻௦ධ䛩䜛
Range(“C10”)䠊Value㻌 =㻌 Range(“B10”).Value * 2
ィ⟬⤖ᯝ䛜 Value 䛻௦ධ䛥䜜䜛
··············································································································································
䕔 䝥䝻䝟䝔䜱䛾್䜢ྲྀᚓ
䜸䝤䝆䜵䜽䝖䛾≧ែ䜢ㄪ䜉䜛䛻䛿䚸䝥䝻䝟䝔䜱䛾್䜢ྲྀᚓ䛧䜎䛩䚹
䝥䝻䝟䝔䜱䛾್䜢ྲྀᚓ䛩䜛䛻䛿䚸ḟ䛾᭩ᘧ䛷グ㏙䛧䜎䛩䚹
᭩ᘧ
ኚᩘ㻌 䠙 䜸䝤䝆䜵䜽䝖䠊䝥䝻䝟䝔䜱
䈜㻌 ኚᩘ䛸䛿䚸䛂ධ䜜≀䛃䛾䜘䛖䛺䜒䛾䛷䚸ᩘ್䜔ᩥᏐิ䜔䜸䝤䝆䜵䜽䝖䛺䛹䜢ධ䜜䜛䛣䛸䛜䛷䛝䜎䛩䚹
౛䠅 䜰䜽䝔䜱䝤䝉䝹䛾⾜␒ྕ䜢ྲྀᚓ䛧䚸ኚᩘ䛂myRow䛃䛻௦ධ䛩䜛
myRow 㻌 䠙㻌 ActiveCell.Row
౛䠅 䝽䞊䜽䝅䞊䝖䛾䝅䞊䝖ᩘ䜢ྲྀᚓ䛧䚸䝉䝹 A5 䛾್䛸䛧䛶タᐃ䛩䜛
Range(“A5”).Value㻌 䠙㻌 Worksheets.Count
····· ್䛾ྲྀᚓ䛾䜏ྍ⬟䛺䝥䝻䝟䝔䜱 ································································································
䝥䝻䝟䝔䜱䛻䛿್䛾タᐃ䛸ྲྀᚓ䛾୧᪉䛜ྍ⬟䛺䝥䝻䝟䝔䜱䛸䚸್䛾ྲྀᚓ䛾䜏ྍ⬟䛺䝥䝻䝟䝔䜱䛜䛒䜚䜎䛩䚹್䛾ྲྀᚓ䛾䜏ྍ⬟
䛺䝥䝻䝟䝔䜱䛻್䜢タᐃ䛩䜛䛸䜶䝷䞊䛻䛺䜚䜎䛩䚹
㻌 ౛䠅㻌 Row 䝥䝻䝟䝔䜱䠄⾜␒ྕ䠅䛿್䛾ྲྀᚓ䛿ྍ⬟䛷䛩䛜䚸್䛾タᐃ䛿䛷䛝䜎䛫䜣䚹
myRow㻌 = Rnage䠄”D5”䠅䠊Row㻌 㻌 䊻㻌 OK
Rnage䠄”D5”䠅䠊Row㻌 =㻌 10㻌 㻌 䊻㻌 NG
··············································································································································
29
Microsoft Excel VBA ฼⏝䛾ᡭ䜋䛹䛝 2007/2010 ᑐᛂ
䕔 ᧯ స 䕔
Range 䜸䝤䝆䜵䜽䝖䛾䝥䝻䝟䝔䜱䜢฼⏝䛩䜛
䝽䞊䜽䝅䞊䝖䛂䝥䝻䝟䝔䜱䛾฼⏝䛃䛾䝉䝹 D3 䛾್䠄Value䠅䚸ิ␒ྕ䠄Column䠅䚸⾜␒ྕ䠄Row䠅䜢ྲྀᚓ䛧䚸
䝉䝹 D5䚸D6䚸D7 䛾್䠄Value䠅䛻タᐃ䛩䜛䝥䝻䝅䞊䝆䝱䜢సᡂ䛧䜎䛩䚹
1. 䝥䝻䝅䞊䝆䝱䛂Prop1䛃䜢సᡂ䛧䚸ḟ䛾䝁䞊䝗䜢グ㏙䛧䜎䛩䚹
2. 䝽䞊䜽䝅䞊䝖䛂䝥䝻䝟䝔䜱䛾฼⏝䛃䜢䜰䜽䝔䜱䝤䛻䛧䚸䝥䝻䝅䞊䝆䝱䜢ᐇ⾜䛧䜎䛩䚹
3. 䝉䝹 D5䚸D6䚸D7 䛻䝉䝹 D3 䛾ྛ䝥䝻䝟䝔䜱䛾್䛜⾲♧䛥䜜䛯䛣䛸䜢☜ㄆ䛧䜎䛩䚹
····· Value 䝥䝻䝟䝔䜱 ··················································································································
ᘧ䛷ᣦᐃ䛥䜜䛯䝉䝹䛾್䜢⾲䛧䜎䛩䚹ྲྀᚓ䛚䜘䜃タᐃ䛜ྍ⬟䛷䛩䚹
ᵓᩥ
ᘧ䠊Value
ᘧ㻌 䠖 Range 䜸䝤䝆䜵䜽䝖䜢⾲䛩グ㏙䜢䛧䜎䛩䚹
Range 䜸䝤䝆䜵䜽䝖䛿䚸Value 䛜᪤ᐃ䛾䝥䝻䝟䝔䜱䛸䛺䛳䛶䛔䜛䛯䜑┬␎䛩䜛䛣䛸䛜䛷䛝䜎䛩䚹
Range(“A10”) =10 䛸 Range (“A10”).Value =10 䛿ྠ䛨ព࿡䛻䛺䜚䜎䛩䚹
·············································································································································
····· Column 䝥䝻䝟䝔䜱 ··············································································································
ᘧ䛷ᣦᐃ䛥䜜䛯䝉䝹䛾ิ䛾␒ྕ䜢ᩘ್䛷㏉䛧䜎䛩䚹್䛾ྲྀᚓ䛾䜏ྍ⬟䛷䛩䚹
ᵓᩥ
ᘧ 䠊Column
ᘧ㻌 䠖 Range 䜸䝤䝆䜵䜽䝖䜢⾲䛩グ㏙䜢䛧䜎䛩䚹
·············································································································································
····· Row 䝥䝻䝟䝔䜱 ···················································································································
ᘧ䛷ᣦᐃ䛥䜜䛯䝉䝹䛾⾜䛾␒ྕ䜢㏉䛧䜎䛩䚹್䛾ྲྀᚓ䛾䜏ྍ⬟䛷䛩䚹
ᵓᩥ
ᘧ 䠊Row
ᘧ㻌 䠖 Range 䜸䝤䝆䜵䜽䝖䜢⾲䛩グ㏙䜢䛧䜎䛩䚹
·············································································································································
30