Exceller Home > VBA °­ÁÂ > VBA ÀÔ¹®°­ÁÂ

Exceller's VBA ÀÔ¹®°­ÁÂ

- ÃÖÃÊ ÀÛ¼ºÀÏ : 2008-08-13
- ÃÖÁ¾ ¼öÁ¤ÀÏ : 2008-08-13
- °­Á ÀÐÀ½¼ö : 17,269ȸ
- ÀÚ·á ÀÛ¼ºÀÚ :


- °­Á Á¦¸ñ : CheckBox¿Í EditBox ÄÁÆ®·Ñ

µé¾î°¡±â Àü¿¡...

¹Ì±¹ Ä«³×±â¸È·Ð´ëÇÐ ÄÄÇ»ÅͰøÇаú ·£µð Æ÷½Ã ±³¼ö. ±×´Â 50¼¼°¡ ä µÇÁö ¾ÊÀº 2006³â ¿©¸§¿¡ ÃéÀå¾Ï ¼±°í¸¦ ¹Þ¾Ò½À´Ï´Ù. ±×´Â Á×±â Àü¿¡ "¸¶Áö¸· °­ÀÇ"¸¦ ³²°åÀ¸¸ç, 10°³ÀÇ ¾Ç¼º Á¾¾çÀÌ Ç¥½ÃµÈ ÀÚ½ÅÀÇ ÃéÀå CT »çÁøÀ» ¸¶Áö¸· °­ÀÇ ½½¶óÀ̵忡 ¼Ò°³Çϸ鼭µµ »î¿¡ ´ëÇÑ °­ÇÑ ÀÇ¿åÀ» º¸¿©ÁÖ¾ú½À´Ï´Ù.

±æ¾î¾ß 6°³¿ù À̶ó´Â ½ÃÇѺΠ»îÀ» ¼±°í¹Þ°íµµ ³«ÃµÀûÀÌ°í ¿­Á¤ÀûÀÎ ¸¶Áö¸· °­ÀÇ¿Í ¿ÀÇÁ¶óÀ©ÇÁ¸® ¼î¸¦ ÅëÇØ ¹Ì±¹°ú Àü¼¼°è »ç¶÷µéÀÇ ½É±ÝÀ» ¿ï·È½À´Ï´Ù(ÃéÀå¾ÏÀÇ °æ¿ì ¼±°í¹Þ°í³ª¼­ 5³â À̳» »ç¸ÁÈ®·üÀÌ 95% ÀÌ»óÀ̶ó°í Çϴ±º¿ä).

À庮ÀÌ °Å±â ¼­ ÀÖ´Â °ÍÀº °¡·Î¸·±â À§Çؼ­°¡ ¾Æ´Ï¶ó ¿ì¸®°¡ ¾ó¸¶³ª °£ÀýÈ÷ ¿øÇÏ´ÂÁö º¸¿©ÁÙ ±âȸ¸¦ ÁÖ±â À§ÇØ °Å±â ¼­ ÀÖ´Â °ÍÀÌ´Ù. À庮¿¡´Â ´Ù ÀÌÀ¯°¡ ÀÖ´Ù. À庮Àº Àý½ÇÇÏ°Ô ¿øÇÏÁö ¾Ê´Â »ç¶÷À» °É·¯³»·Á°í Á¸ÀçÇÑ´Ù.À庮Àº ¿ì¸®°¡ ¾ó¸¶³ª Àý½ÇÇÏ°Ô ¿øÇÏ´ÂÁö ±ú´ÞÀ» ¼ö ÀÖµµ·Ï ±âȸ¸¦ Á¦°øÇÏ´Â °ÍÀÌ´Ù.

<¸¶Áö¸· °­ÀÇ>, ·£µð Æ÷½Ã

ºÒÈ®½ÇÇÑ Àΰ£»ç¿¡ È®½ÇÇÑ °ÍÀÌ Çϳª ÀÖ´Ù¸é ±×°ÍÀº ¹Ù·Î '¸ðµç Àΰ£Àº ¾ðÁ¨°¡´Â Á״´Ù'´Â °ÍÀ̰ÚÁö¿ä. ¾ÕÀ¸·ÎÀÇ ¸ðµç »ýÀÏ¿¡ ´õ ÀÌ»ó ³»°¡ ÇÔ²²ÇÒ ¼ö ¾øÀ» °Å¶õ »ý°¢, »ç¶ûÇÏ´Â »ç¶÷µéÀ» ³²°ÜµÐ ä È¥ÀÚ ¶°³ª¾ß ÇÑ´Ù´Â ±ØÇÑÀÇ °íÅëÀ» Àý¸ÁÀÌ ¾Æ´Ñ ¿¡³ÊÁö·Î ½ÂÈ­½ÃŲ ·£µð Æ÷½Ã ±³¼ö¸¦ º¸¸ç, ³»°Ô ³²°ÜÁø ¾ÕÀ¸·ÎÀÇ »îÀ» ¾î¶»°Ô º¸³»¾ß Çϴ°¡ »ý°¢ÇØ º¸¾Ò½À´Ï´Ù. ±×·¯¸é¼­, ±×¿¡°Ô ±âÀûÀÌ ÀϾ±â¸¦ ±â¿øÇß½À´Ï´Ù¸¸...

2008³â 7¿ù 25ÀÏ. ·£µð Æ÷½Ã ¾Ï°úÀÇ ÅõÀïÀ» ³¡³»´Ù...


¿¹Á¦ ÆÄÀÏ ³»·Á¹Þ±â


À̹ø ½Ã°£¿¡´Â CheckBox¿Í EditBox ÄÁÆ®·ÑÀ» ÅÇ¿¡ Ãß°¡ÇØ º¸°Ú½À´Ï´Ù. À̹ø ½Ã°£¿¡ ¸¸µé¾î º¼ <¿Ï¼º ¿¹>¸¦ ¸ÕÀú º¸µµ·Ï ÇÏÁÒ.

<¿Ï¼º ¿¹>

¾î¶²°¡¿ä? Áö±Ý±îÁö VBA °­Á¸¦ Á¤»óÀûÀ¸·Î µû¶ó¿Â ºÐÀ̶ó¸é ´©±¸³ª ¸¸µé ¼ö ÀÖ´Â ÆòÀÌÇÑ ¼öÁØÀÇ ¿¹Á¦ÀÌÁö ¾Ê³ª¿ä?... ¶ó°í ÇÏ¸é ¿äÁò°°Àº ºÐÀ§±â¿¡ ÀÚÄ© ¹°´ëÆ÷¸¦ ¸ÂÀ» ¼ö ÀÖÀ¸¹Ç·Î ¸¸¼ö¹«°­À» À§ÇØ ÀÌ ¹ß¾ðÀº öȸÇÕ´Ï´Ù. (Ãë¼Ò!!~~) °¢ ¸Þ´º¿¡ ´ëÇØ °£´ÜÈ÷ ¼Ò°³ÇØ µå¸®¸é...

  • Show Gridlines: ´«±Ý¼±À» Ç¥½ÃÇϰųª ¼û±é´Ï´Ù(Åä±ÛŰ).

  • Show Page Breaks: ÆäÀÌÁö ³ª´©±â¸¦ ¼³Á¤Çϰųª Ãë¼ÒÇÕ´Ï´Ù(Åä±ÛŰ).

  • Toggle Reference Styles: ¼¿ ÂüÁ¶ Çü½ÄÀ» º¯°æÇÕ´Ï´Ù.

  • Change Sheetname to: ½ÃÆ® À̸§À» º¯°æÇÕ´Ï´Ù.

ÀüüÀûÀÎ ÀÛ¼º ¼ø¼­´Â Áö±Ý±îÁöÀÇ °úÁ¤°ú ºñ½ÁÇÕ´Ï´Ù. Áï, 1) ¿¢¼¿ ÆÄÀÏÀ» ¸¸µé°í, 2) XML·Î ¸®º»X Äڵ带 ¸¸µç ´ÙÀ½, 3) Äݹé ÇÁ·Î½ÃÀú¸¦ ÀÛ¼ºÇÏ´Â °ÍÀÌÁö¿ä.

ºó Áý ¸¸µé±â

¿ª½Ã³ª... ù¹øÂ° ´Ü°è´Â ¹®ÆÐ¸¦ ´Þ ÁýÀ» ¸¶·ÃÇÏ´Â °ÍÀÔ´Ï´Ù.

1. 'Office ´ÜÃß-»õ·Î ¸¸µé±â'¸¦ Ŭ¸¯ÇÏ¿© »õ·Î¿î ÆÄÀÏÀ» Çϳª ¸¸µì´Ï´Ù.

2. 'Office ´ÜÃß-´Ù¸¥ À̸§À¸·Î ÀúÀå-Excel ¸ÅÅ©·Î »ç¿ë ÅëÇÕ ¹®¼­'¸¦ ¼±ÅÃÇÑ ´ÙÀ½ Àû´çÇÑ À̸§À¸·Î ÀúÀåÇÏ°í ÆÄÀÏÀ» ´Ý½À´Ï´Ù.

3. 'Office 2007 Custom UI Editor'¸¦ ½ÇÇàÇÏ¿© ¾Õ¿¡¼­ ÀÛ¼ºÇÑ ¸ÅÅ©·Î »ç¿ë ÅëÇÕ ¹®¼­¸¦ ºÒ·¯¿É´Ï´Ù. XML·Î ´ÙÀ½°ú °°Àº ¸®º»X Äڵ带 ÀÛ¼ºÇÕ´Ï´Ù. ´Ù¸¥ °ÍÀº ÀÌÀü ½Ã°£ÀÇ °Í°ú °°°í... ÅÇÀÌ »ðÀԵǴ À§Ä¡°¡ 'Ȩ' ÅÇ µÚ¶ó´Â °Í¸¸ ´Ù¸¨´Ï´Ù.

<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui'>
<ribbon>
    <tabs>
      <tab id="MyTab"
        label="Exceller's Tab"
        insertAfterMso="TabHome">
      </tab>
    </tabs>
</ribbon>
</customUI>

¸®º»X ÄÚµå ÀÛ¼ºÇϱâ

¾Õ¼­ ¸¸µé¾î µÎ¾ú´ø '¸ÅÅ©·Î »ç¿ë ÅëÇÕ ¹®¼­'¸¦ UI Editor¿¡¼­ ºÒ·¯¿Â ´ÙÀ½ ¸®º»X Äڵ带 ÀÛ¼ºÇÕ´Ï´Ù. Á÷Á¢ ÀÔ·ÂÇϱ⠾î·Á¿î ºÐÀº ¾Æ·¡ Äڵ带 º¹»çÇØ¼­ ºÙ¿©³Ö±â ÇØµµ µË´Ï´Ù.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<
ribbon>
<
tabs>
    <
tab id="MyTab"
        label="Exceller's Tab"
        insertAfterMso="TabHome">
        <
group id="grpCheckBox"
            label="My CheckBox">
            <
checkBox idMso="ViewGridlinesToggleExcel"
                label="Show Gridlines"/>
                <
checkBox id="chkPBreak"
                    label="Show Page Breaks"
                    onAction="TogglePageBreakDisplay"
                    getPressed="chkPBreak_GetPressed"
                    getEnabled="chkPBreak_GetEnabled"/>
            <
checkBox id="chkR1C1"
                label="Toggle Reference Styles"
                getPressed="chkR1C1_getPressed"
                onAction="chkR1C1_click"/>
        </
group>
        <
group id="grpEditBox"
            label="My EditBox">
            <
editBox id="edtBox1"
                label="Change Sheetname to:"
                sizeString="123456789012345"
                onChange="edtBox1_Click" />
        </
group>
    </
tab>
</
tabs>
</
ribbon>
</
customUI
>

Á» º¹ÀâÇØ º¸À̴±º¿ä. UI Editor¿¡¼­ Á÷Á¢ ¿­¾îº¸¸é »ö±ò°ú ÅÇÀ¸·Î ±¸ºÐµÇ¹Ç·Î À§ÀÇ °Íº¸´Ù´Â Á¶±Ý´õ ÀÌÇØÇϱ⠽±½À´Ï´Ù.

ÄÚµå ÀÔ·ÂÀÌ ³¡³µÀ¸¸é 'Validate' ¾ÆÀÌÄÜÀ» Ŭ¸¯ÇÕ´Ï´Ù. Á¦´ë·Î ÀÔ·ÂÀÌ µÇ¾ú´Ù¸é 'Custom UI XML is well formed!'¶ó´Â ´ëÈ­»óÀÚ°¡ ³ªÅ¸³³´Ï´Ù.

Äݹé(CallBack) ÇÁ·Î½ÃÀú ¸¸µé±â

1. 'Generate Callbacks' ¾ÆÀÌÄÜÀ» Ŭ¸¯Çϸé 6°³ÀÇ Äݹé ÇÁ·Î½ÃÀú°¡ »ý¼ºµË´Ï´Ù.

2. ¹üÀ§¸¦ ÁöÁ¤ÇÑ ´ÙÀ½ ¸¶¿ì½º ¿À¸¥ÂÊ ¹öưÀ» Ŭ¸¯Çϰí 'Copy' ¸Þ´º¸¦ ¼±ÅÃÇÕ´Ï´Ù.

3. 'Save' ¹öưÀ» Ŭ¸¯ÇÏ¿© ÇöÀç ÆÄÀÏÀ» ÀúÀåÇÕ´Ï´Ù. ÀÌ ¶§ µ¿ÀÏÇÑ ÆÄÀÏÀÌ ¿¢¼¿¿¡ ¿­·Á ÀÖ´Ù¸é ¿À·ù ¸Þ½ÃÁö°¡ Ç¥½ÃµË´Ï´Ù. ´ÝÀº ´ÙÀ½ ´Ù½Ã Save ¹öưÀ» Ŭ¸¯Çϼ¼¿ä!

4. ÀÌÁ¦ ¿¢¼¿·Î µÇµ¹¾Æ¿Í¼­ ÇØ´ç ÆÄÀÏÀ» ¿±´Ï´Ù. '°³¹ß µµ±¸' ÅÇÀÇ 'Visual Basic' ÄÁÆ®·ÑÀ» Ŭ¸¯ÇÏ¿© ¸ðµâ ½ÃÆ®¸¦ »ðÀÔÇϰí, ¾Õ¿¡¼­ º¹»çÇÑ Äݹé ÇÁ·Î½ÃÀú¸¦ ºÙ¿© ³Ö½À´Ï´Ù.

'Callback for Checkbox1 getEnabled
Sub chkPBreak_GetEnabled(control As IRibbonControl, ByRef returnedVal)
    returnedVal = TypeName(ActiveSheet) = "Worksheet"
End Sub
'Callback for editBox1 onChange
Sub edtBox1_Click(control As IRibbonControl, text As String)
    If ChangeSheetName(text) = False Then
        MsgBox "À¯È¿ÇÏÁö ¾ÊÀº À̸§ÀÔ´Ï´Ù. È®ÀÎ ÈÄ ´Ù½Ã ÀÔ·ÂÇϼ¼¿ä!"
    End If
End Sub
Private Function ChangeSheetName(strCallSheet As String)
    On Error Resume Next
    ActiveSheet.Name = strCallSheet
    If Err.Number = 0 Then ChangeSheetName = True
End Function
'Callback for chkR1C1 getPressed
Sub chkR1C1_getPressed(control As IRibbonControl, ByRef returnedVal)
    If Application.ReferenceStyle = xlR1C1 Then returnedVal = True
End Sub
'Callback for chkR1C1 onAction
Sub chkR1C1_click(control As IRibbonControl, pressed As Boolean)
    Select Case pressed
        Case True
            Application.ReferenceStyle = xlR1C1
        Case Else
            Application.ReferenceStyle = xlA1
    End Select
End Sub
Public Sub RenameSheet()
    Dim strNewSheetName As String
    strNewSheetName = InputBox("There was a problem. Please try again!")

    If ChangeSheetName(strNewSheetName) = False Then
        MsgBox "À¯È¿ÇÏÁö ¾ÊÀº À̸§ÀÔ´Ï´Ù. È®ÀÎ ÈÄ ´Ù½Ã ÀÔ·ÂÇϼ¼¿ä!"
    End If
End Sub
'Callback for Checkbox1 onAction
Sub TogglePageBreakDisplay(control As IRibbonControl, pressed As Boolean)
    On Error Resume Next
    ActiveSheet.DisplayPageBreaks = pressed
End Sub
'Callback for Checkbox1 getPressed
Sub chkPBreak_GetPressed(control As IRibbonControl, ByRef returnedVal)
    On Error Resume Next
    returnedVal = ActiveSheet.DisplayPageBreaks
End Sub

ÇÁ·Î½ÃÀú ¼ö´Â Á» ¸¹½À´Ï´Ù¸¸, ´ëºÎºÐÀÌ ¸î ÁÙ À̳»ÀÇ °£´ÜÇÑ °ÍÀÌ´Ï±î º°µµÀÇ ÇØ¼³Àº »ý·«ÇÕ´Ï´Ù.

´ÙÀ½ ½Ã°£¿¡ ¶Ç...


Previous

Next

Creative Commons License

¡¡