[B! VBA] kakku22ã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

kakku22 id:kakku22

VBAã«é–¢ã™ã‚‹kakku22ã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (6)

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

${{author_name}}$
{{author_name}}{{created}}
{{ #comment }}{{ comment }}{{ /comment }}
- {{ label }}

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

xlsãƒ•ã‚¡ã‚¤ãƒ«ã‚’é–‹ã„ãŸã¨ãã«è‡ªå‹•çš„ã«å®Ÿè¡Œâˆ’Workbook_Open:ã‚¨ã‚¯ã‚»ãƒ«ãƒžã‚¯ãƒãƒ»Excel VBAã®ä½¿ã„æ–¹/ã‚¤ãƒ™ãƒ³ãƒˆãƒ—ãƒã‚·ãƒ¼ã‚¸ãƒ£
xlsãƒ•ã‚¡ã‚¤ãƒ«ï¼ˆãƒ¯ãƒ¼ã‚¯ãƒ–ãƒƒã‚¯ï¼‰ãŒé–‹ã‹ã‚Œã‚‹ã¨ãã«ã¯ã€Openã‚¤ãƒ™ãƒ³ãƒˆãŒç™ºç”Ÿã™ã‚‹ã™ã‚‹ã®ã§ã€Workbook_Openã‚¤ãƒ™ãƒ³ãƒˆãƒ—ãƒã‚·ãƒ¼ã‚¸ãƒ£ã«ã‚³ãƒ¼ãƒ‰ã‚’è¨˜è¿°ã™ã‚Œã°ã€xlsãƒ•ã‚¡ã‚¤ãƒ«ãŒé–‹ã‹ã‚ŒãŸã¨ãã«ãã®å‡¦ç†ãŒè¡Œã‚ã‚Œã‚‹ã‚ˆã†ã«ãªã‚Šã¾ã™ã€‚ â–¼æ“ä½œæ‰‹é †ï¼šãƒ–ãƒƒã‚¯ãŒé–‹ã‹ã‚ŒãŸã¨ãã«ä½•ã‚‰ã‹ã®å‡¦ç†ã‚’å®Ÿè¡Œã™ã‚‹ â€»ãƒ–ãƒƒã‚¯ã‚’é–‹ã„ãŸã¨ãã«ã€ã€ŒWorkbook_Openã‚¤ãƒ™ãƒ³ãƒˆãƒ—ãƒã‚·ãƒ¼ã‚¸ãƒ£ã«ã‚ˆã£ã¦è¡¨ç¤ºã•ã‚ŒãŸãƒ¡ãƒƒã‚»ãƒ¼ã‚¸ã§ã™ã€‚ã€ã¨ã„ã†ãƒ¡ãƒƒã‚»ãƒ¼ã‚¸ã‚’è¡¨ç¤ºã•ã›ã‚‹ä¾‹ Visual Basic Editorã®ãƒ—ãƒã‚¸ã‚§ã‚¯ãƒˆã‚¨ã‚¯ã‚¹ãƒ—ãƒãƒ¼ãƒ©ã§ ã€ŒThisWorkbookã€ã‚’ãƒ€ãƒ–ãƒ«ã‚¯ãƒªãƒƒã‚¯ â†“ ã€ŒWorkbook_Openã€ãƒ—ãƒã‚·ãƒ¼ã‚¸ãƒ£ã‚’ä»¥ä¸‹ã®ã‚ˆã†ã«è¨˜è¿°ã™ã‚‹ Private Sub Workbook_Open() MsgBox "Workbook_Openã‚¤ãƒ™ãƒ³ãƒˆãƒ—ãƒã‚·ãƒ¼ã‚¸ãƒ£ã«ã‚ˆã£ã¦è¡¨ç¤ºã•ã‚ŒãŸãƒ¡ãƒƒã‚»ãƒ¼ã‚¸ã§ã™ã€‚" End Sub
kakku22 2011/07/06
Workbook_Openã§ã„ã˜ã‚ã‚‹ã™ã‚‹ç¬‘

EXCEL

VBA
ãƒªãƒ³ã‚¯
ã‚ªãƒ¼ãƒˆãƒ•ã‚£ãƒ«ã‚¿(AutoFilter)ã§ã®ãƒ‡ãƒ¼ã‚¿æŠ½å‡º:Excel VBAå…¥é–€
ã‚ªãƒ¼ãƒˆãƒ•ã‚£ãƒ«ã‚¿(AutoFilterãƒ¡ã‚½ãƒƒãƒ‰)ã§ã®ãƒ‡ãƒ¼ã‚¿æŠ½å‡ºã€€ã€€ã€€Topã¸ ãƒ‡ãƒ¼ã‚¿ãƒªã‚¹ãƒˆã‹ã‚‰å¿…è¦ãªãƒ‡ãƒ¼ã‚¿ã‚’è¡¨ç¤ºã—ã€ä»–ã®ãƒ‡ãƒ¼ã‚¿ã¯éžè¡¨ç¤ºã«ã—ã¾ã™ã€‚ expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown) Field å¿…ãšæŒ‡å®šã—ã¾ã™ã€‚ãƒ•ã‚£ãƒ«ã‚¿ã®å¯¾è±¡ã¨ãªã‚‹ãƒ•ã‚£ãƒ¼ãƒ«ãƒ‰ç•ªå·ã‚’æ•´æ•°ã§æŒ‡å®šã—ã¾ã™ã€‚ ãƒ•ã‚£ãƒ¼ãƒ«ãƒ‰ç•ªå·ã¯ã€ãƒªã‚¹ãƒˆã®å·¦ç«¯åˆ—ãŒã€Œ1ã€ã¨ãªã‚Šã¾ã™ã€‚ Criteria1 çœç•¥å¯èƒ½ã€‚æŠ½å‡ºæ¡ä»¶ã¨ãªã‚‹æ–‡å—åˆ— ("101" ãªã©) ã‚’æŒ‡å®šã—ã¾ã™ã€‚ "=" ã¨æŒ‡å®šã™ã‚‹ã¨ã€ç©ºç™½ã‚»ãƒ«ãŒæŠ½å‡ºã•ã‚Œã¾ã™ã€‚ "<>" ã¨æŒ‡å®šã™ã‚‹ã¨ç©ºç™½ä»¥å¤–ã®ãƒ•ã‚£ãƒ¼ãƒ«ãƒ‰ãŒæŠ½å‡ºã•ã‚Œã¾ã™ã€‚ ã“ã®å¼•æ•°ã‚’çœç•¥ã™ã‚‹ã¨ã€æŠ½å‡ºæ¡ä»¶ã¯ALLã«ãªã‚Šã¾ã™ã€‚ å¼•æ•°Operatorã«xlTop10It emsãŒæŒ‡å®šã•ã‚Œã¦ã„ã‚‹å ´åˆã¯ã€å¼•æ•°Criteria1ã«é …ç›®æ•°ã‚’æŒ‡å®šã—ã¾ã™
kakku22 2011/02/02
.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet2").Range("A1")

VBA
ãƒªãƒ³ã‚¯
VBAåŸºç¤Žæ–‡æ³•æœ€é€Ÿãƒžã‚¹ã‚¿ãƒ¼ - ã„ã‚ã„ã‚è§£æžæ—¥è¨˜
VBAã®æ–‡æ³•ä¸€è¦§ã§ã™ã€‚ä»–ã®è¨€èªžã‚’ã‚ã‚‹ç¨‹åº¦çŸ¥ã£ã¦ã„ã‚‹äººã¯ã“ã‚Œã‚’èªã‚ã°VBAã®åŸºç¤Žã‚’ãƒžã‚¹ã‚¿ãƒ¼ã—ã¦VBAã‚’æ›¸ãã“ã¨ãŒã§ãã‚‹ã‚ˆã†ã«ãªã£ã¦ã„ã¾ã™ã€‚ç°¡æ˜“ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹ã¨ã—ã¦ã‚‚åˆ©ç”¨ã§ãã‚‹ã¨æ€ã„ã¾ã™ã®ã§ã€ã“ã‚Œã¯è¶³ã‚Šãªã„ã¨æ€ã†ã‚‚ã®ãŒã‚ã‚Œã°æ•™ãˆã¦ãã ã•ã„ã€‚ 1. åŸºç¤Ž Visual Basic Editorã®èµ·å‹• VBAã¯Visual Basic Editorã§ç·¨é›†ãƒ»å®Ÿè¡Œã—ã¾ã™ã€‚Visual Basic Editorã¯æ¬¡ã®ã‚ˆã†ã«èµ·å‹•ã—ã¾ã™ã€‚ Excelã‚„Wordã®ãƒ¡ãƒ‹ãƒ¥ãƒ¼ã§[ãƒ„ãƒ¼ãƒ«]-[ãƒžã‚¯ãƒ]-[Visual Basic Editor]ã‚’é¸æŠžã™ã‚‹ æ¨™æº–ãƒ¢ã‚¸ãƒ¥ãƒ¼ãƒ«ã®è¿½åŠ VBAã¯æ¨™æº–ãƒ¢ã‚¸ãƒ¥ãƒ¼ãƒ«ã«è¨˜è¿°ã—ã¾ã™ã€‚æ¨™æº–ãƒ¢ã‚¸ãƒ¥ãƒ¼ãƒ«ã¯æ¬¡ã®ã‚ˆã†ã«è¿½åŠ ã—ã¾ã™ã€‚ Visual Basic Editorã®ãƒ¡ãƒ‹ãƒ¥ãƒ¼ã§[æŒ¿å…¥]-[æ¨™æº–ãƒ¢ã‚¸ãƒ¥ãƒ¼ãƒ«]ã‚’é¸æŠžã™ã‚‹ã€‚ å¤‰æ•°å®£è¨€ã®å¼·åˆ¶ å¤‰æ•°å®£è¨€ã‚’å¼·åˆ¶ã™ã‚‹ãŸã‚ã€ãƒ¢ã‚¸ãƒ¥ãƒ¼ãƒ«ã®å…ˆé ã«å¿…ãšä»¥ä¸‹ã®è¡Œã‚’æ›¸ã
kakku22 2010/02/01
è‰¯ã‚¨ãƒ³ãƒˆãƒªãƒ¼ï¼Žã€ŒExcel VBA ãƒ™ãƒ¼ã‚·ãƒƒã‚¯ã€å¯¾ç–ã«ãªã£ãŸï¼Ž

*ã¾ã¨ã‚

VBA

EXCEL
ãƒªãƒ³ã‚¯
Excel VBAå…¥é–€
Excel ã§ã“ã‚Œã‹ã‚‰ VBA ã‚’ä½¿ã£ã¦ã¿ãŸã„ã¨ã„ã†æ–¹ã‚’å¯¾è±¡ã«ã—ãŸ VBA å…¥é–€ã§ã™ã€‚ VBA ã¨ã¯ Visual Basic for Applications ã®ç•¥ã§ Excel ã‚„ Access ãªã©ã§åˆ©ç”¨ã§ãã‚‹ãƒ—ãƒã‚°ãƒ©ãƒŸãƒ³ã‚°è¨€èªžã®1ã¤ã§ã™ã€‚ VBA ã‚’ä½¿ã†ã“ã¨ã§æ‰‹å‹•ã§è¡Œã£ã¦ã„ã‚‹å®šåž‹çš„ãªæ¥å‹™ãªã©ã‚’è‡ªå‹•åŒ–ã•ã›ãŸã‚Šã€ç‹¬è‡ªã®ãƒ•ã‚©ãƒ¼ãƒ ãªã©ã‚’ä½œæˆã—ã¦ Excel ã‚’ã‚¢ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³ã®ã‚ˆã†ã«è¦‹ã›ã‚‹ã“ã¨ãªã©ãŒã§ãã¾ã™ã€‚ã“ã“ã§ã¯ Excel ã§ã® VBA ã®ä½¿ã„æ–¹ã«ã¤ã„ã¦ã‚µãƒ³ãƒ—ãƒ«ã‚’ä½¿ã„ãªãŒã‚‰è§£èª¬ã—ã¦ã„ãã¾ã™ã€‚ â€» Excel ã§ãƒžã‚¯ãƒã‚’è¨˜éŒ²ã™ã‚‹æ–¹æ³•ãªã©ã«ã¤ã„ã¦ã¯ã€ŒExcelãƒžã‚¯ãƒå…¥é–€ã€ã‚’å‚ç…§ã•ã‚Œã¦ãã ã•ã„ã€‚
kakku22 2009/03/31
ã‚ªãƒ–ã‚¸ã‚§ã‚¯ãƒˆã¾ã§çµ‚äº†

Excel

VBA

ãƒžã‚¯ãƒ
ãƒªãƒ³ã‚¯
Excel VBA å…¥é–€è¬›åº§
ã“ã®ã‚µã‚¤ãƒˆã¯Excelã®å…¥é–€æ›¸ãƒ¬ãƒ™ãƒ«ã®å¦ç¿’ã‚’ä¸€é€šã‚Šçµ‚ãˆã€æ›´ã«Excel VBAã®çŸ¥è˜ã‚’èº«ã«ä»˜ã‘ãŸã„ã¨è€ƒãˆã¦ã„ã‚‹æ–¹ã‚’å¯¾è±¡ã¨ã—ã¦ã„ã¾ã™ã€‚ ã“ã®ã‚µã‚¤ãƒˆã«ã‚ˆã‚ŠExcel VBAã®é…åŠ›ã‚’ä½“é¨“ã—ã¦ã„ãŸã ã‘ã‚Œã°ç®¡ç†äººã¨ã—ã¦ã¯æœ€é«˜ã®å–œã³ã§ã™ !! (*^ ^*)
kakku22 2009/03/31
ãƒžã‚¯ãƒã®å‹‰å¼·ã—ãªã„ã¨ï½¥ï½¥ï½¥ï¼Žæœ¬æ¬²ã—ã„ã‘ã©ï¼Œã¨ã‚Šã‚ãˆãšã‚¦ã‚§ãƒ–ã®è³‡æ–™ã§å‹‰å¼·ã‚’å§‹ã‚ã‚ˆã†ã‹ãªï½¥ï½¥ï½¥ï¼Ž

Excel

VBA

ãƒžã‚¯ãƒ
ãƒªãƒ³ã‚¯
Excel(ã‚¨ã‚¯ã‚»ãƒ«)æ—¥æœ¬ä¸€ã‚„ã•ã—ã„ãƒžã‚¯ãƒè¬›åº§
ã‚¯ãƒ©ã‚¹ãƒ¢ã‚¸ãƒ¥ãƒ¼ãƒ«ã‚’ä½¿ã£ãŸå®Ÿç”¨ãƒžã‚¯ãƒ ã‚¯ãƒ©ã‚¹ãƒ¢ã‚¸ãƒ¥ãƒ¼ãƒ«ã¯ä½¿ã„æ–¹ãŒã‚ã‹ã‚Œã°ã¨ã¦ã‚‚ä¾¿åˆ©ãªä»•çµ„ã¿ã§ã™ã€‚ ã‘ã‚Œã©ã‚‚ãªã‹ãªã‹ãã‚Œã‚’ä½¿ã£ã¦ã„ã‚‹ ãƒ¦ãƒ¼ã‚¶ãƒ¼ã¯ã„ã¾ã›ã‚“ã€‚ ä½¿ã„é“ãŒåˆ†ã‹ã‚‰ãªã„ã¨ã‹ã€ä½¿ã‹ãªãã¦ã‚‚ä½•ã¨ã‹ãªã‚‹ã¨ã„ã†ã®ãŒç†ç”±ã®ã²ã¨ã¤ã§ã™ã€‚ã“ã®è¨˜äº‹ã§ã¯å®Ÿéš›ã«ä½¿ã†äº‹ã®ã§ãã‚‹ã‚³ãƒ¼ãƒ‰ã‚’ç´¹ä»‹ã—ã¾ã™ã€‚ æ£è¦è¡¨ç¾ã¯ã©ã†ã„ã†ã‚‚ã®ã§ã™ã‹ï¼Ÿ æ£è¦è¡¨ç¾ã¯ã€ç‰¹å®šã®ãƒ‘ã‚¿ãƒ¼ãƒ³ã«ä¸€è‡´ã™ã‚‹æ–‡å—åˆ—ã‚’è¦‹ã¤ã‘ã‚‹ãŸã‚ã®ä¾¿åˆ©ãªãƒ„ãƒ¼ãƒ«ã§ã™ã€‚ é›»è©±ç•ªå·ã®ãƒªã‚¹ãƒˆã®ç‰¹å®šã®ç•ªå·ã‚’æŠ½å‡ºã—ãŸã‚Šã€è¨˜å…¥ãƒŸã‚¹ã®ã‚ã‚‹ç•ªå·ã‚’æŽ¢ã—ãŸã‚Šã™ã‚‹ã®ã«å½¹ã«ç«‹ã¤ä¾‹ã‚’ã¿ã¦ã¿ã¾ã—ã‚‡ã†ã€‚ ã‚¯ãƒ©ã‚¹ãƒ¢ã‚¸ãƒ¥ãƒ¼ãƒ«ã®åŸºç¤Žã¨ä½¿ã„æ–¹1 ã‚ã‚‹ç¨‹åº¦ VBA ã®çµŒé¨“ã‚’ç©ã‚“ã§ãã‚‹ã¨ã‚¯ãƒ©ã‚¹ã€ã‚¯ãƒ©ã‚¹ãƒ¢ã‚¸ãƒ¥ãƒ¼ãƒ«ã‚ã‚‹ã„ã¯ã‚ªãƒ–ã‚¸ã‚§ã‚¯ãƒˆæŒ‡å‘ã€ãªã©ã¨ã„ã£ãŸè¨€è‘‰ãŒç›®ã«ã¤ãã‚ˆã†ã«ãªã‚Šã¾ã™ã€‚ ã—ã‹ã—ã“ã‚Œã‚‰ã«ã¤ã„ã¦è©³ã—ãæ›¸ã‹ã‚Œã¦ã„ã‚‹å‚è€ƒæ›¸ã¯ã‚ã‚Šã¾ã›ã‚“ã€‚ ãªãœã§ã—ã‚‡ã†ã€‚ãã‚Œã¯ã‚¯ãƒ©ã‚¹ã‚’ä½¿ã‚ãªãã¦ã‚‚ã€ãã“ãã“VBAãŒä½¿ãˆã‚‹ã‹ã‚‰ã§ã™ã€‚
kakku22 2009/02/25
ã‚¨ãƒ³ã‚¸ãƒ‹ã‚¢ã®é›‘ç”¨ã¯ãƒžã‚¯ãƒï¼Žã“ã‚Œåè¨€ï¼Žãƒžã‚¯ãƒã¯ã‚¤ãƒ³ãƒ†ãƒªã‚¸ã‚§ãƒ³ã‚¹ã§ã™ï¼Ž

EXCEL

VBA
ãƒªãƒ³ã‚¯
1