[¼ö°­±â°£] 2010. 11. 1 ~ 2011. 10. 31
½Åû¹®ÀÇ : À¥Å¸ÀÓ±³À°¼¾ÅÍ 02-3477-8300, ¿î¿µ°ü¸® : ¿ÀÇǽºÆ©ÅÍ 070-7098-2554

±³À°°úÁ¤ ³ªÀÇÇнÀ ¿ÀÇǽºÆÁ °Ë»ö ¹«·á°­ÁÂ
Home ·Î±×ÀΠȸ¿ø°¡ÀÔ °í°´¼¾ÅÍ »çÀÌÆ®¸Ê
¿ÀÇǽºÆ©ÅÍ ±â¾÷ASP¼­ºñ½º > ¹«·á°­ÁÂ
¿¢¼¿ 2000 µû¶óÇϱâ
Ȩ·¯´× ¿¢¼¿ 2000°­ÁÂ
À繫 ÇÔ¼ö °­ÁÂ
¿¢¼¿ 2002 »õ·Î¿î ±â´É
Ȳ±â¼º´ÔÀÇ VBA°­ÁÂ
°û½ÂÁÖ´ÔÀÇ VBAÀ̾߱â
Home > ¹«·á°­Á > ¿¢¼¿
¿¢¼¿, °û½ÂÁÖ´ÔÀÇ ¿ÀÆ©°ø±¸ÇÔ Á¦ÀÛÀ¸·Î ¹è¿öº¸´Â VBA À̾߱â, Excel
  

12. ±Ý¾×À» ÇѱÛ/ÇÑÀÚ/¿µ¾î·Î º¯È¯

ÀÚ·á´Ù¿î·Îµå : ¿ÀÆ©°ø±¸ÇÔ012.xls 

¾È³çÇϼ¼¿ä ¿ÀÆ©°¡Á· ¿©·¯ºÐ!

Áö³­ ½Ã°£¿¡´Â ±âÁ¸ÀÇ ¿µ¹®º¯È¯¼Ò½º¸¦ °¡Áö°í ÀÛ¾÷À» Çߴµ¥ À̹ø ½Ã°£¿¡´Â ¼ýÀڷΠǥ½ÃµÈ ±Ý¾×À» ÇÑ±Û ¶Ç´Â ÇÑÀÚ¿µ¾î·Î º¯°æÇÏ´Â ±â´ÉÀ» ¿Ï¼ºÇغ¸·Á°í ÇÕ´Ï´Ù. Çѱ۰ú ÇÑÀÚ·Î º¯È¯ÇÏ´Â ±â´ÉÀº ¿µ¹®À϶§¿Í´Â ¾à°£ ´Ù¸¨´Ï´Ù. ¿µ¹®¿¡¼­´Â ¼Ò¼öÁ¡ ÀÌÇÏ´Â ¼¾Æ®(Cent)·Î ó¸®ÇÏ¿´Áö¸¸ Çѱ۰ú ÇÑÀÚº¯È¯¿¡¼­´Â ¼Ò¼öÁ¡ ÀÌÇϸ¦ ¹Ý¿Ã¸²ÇÏ¿© º¯È¯ÇÕ´Ï´Ù. ±×¸®°í Çѱ۰ú ÇÑÀڴ ǥ½ÃÇÏ´Â ±ÛÀÚ¸¸ ´Ù¸£Áö ³í¸®ÀûÀÎ ¹æ¹ýÀº µ¿ÀÏÇÕ´Ï´Ù. ±×·¯³ª ¼Ò½º¸¦ °£ÆíÇÏ°Ô º¸ÀÌ°Ô Çϱâ À§ÇØ Çѱۺ¯È¯·çƾ°ú ÇÑÀÚº¯È¯·çƾÀ» °¢°¢ ¸¸µé¾ú½À´Ï´Ù.

¸ÕÀú Çѱۺ¯È¯·çƾ°ú ÇÑÀÚº¯È¯·çƾÀ» º¸¿©µå¸®°Ú½À´Ï´Ù.

'Àü¿ª»ó¼ö
Const ERR_TOOBIG = 6
Const ERR_NOTNUMBER = 13
Const NUM_HANGUL = "ÀÏÀÌ»ï»ç¿ÀÀ°Ä¥Æȱ¸"
Const NUM_HANJA = "ìììÃß³ÞÌçéë»öÒø¢Îú"

'Çѱۺ¯È¯·çƾ
Function ConvertCurrencyToHangul(NumberText As String) As String
     Dim curAmount As Currency
     Dim StringReturn As String
     Dim strAmount As String
     Dim TmpString As String
     Dim AmountLength As Integer
     Dim I As Integer

     On Error GoTo ErrHandler

     curAmount = Format(NumberText, "#")
     AmountLength = Len(CStr(curAmount))
     strAmount = ""

     For i = AmountLength To 1 Step -1
          strAmount = strAmount & Mid(CStr(curAmount), i, 1)
     Next I

     For i = 1 To AmountLength
           If Mid(strAmount, i, 1) = "0" Then
               TmpString = ""
           Else
                TmpString = Mid(NUM_HANGUL, Mid(strAmount, i, 1), 1)
            Select Case I
            Case 4, 8, 12
                 TmpString = TmpString & "õ"
            Case 3, 7, 11, 15
                 TmpString = TmpString & "¹é"
            Case 2, 6, 10, 14
                 TmpString = TmpString & "½Ê"
            End Select
        End If
        Select Case I
        Case 13
             TmpString = TmpString & "Á¶"
        Case 9
             TmpString = TmpString & "¾ï"
        Case 5
             TmpString = TmpString & "¸¸"
        End Select
          StringReturn = TmpString & StringReturn
     Next I
     ConvertCurrencyToHangul = "ÀÏ±Ý " & StringReturn & "¿øÁ¤"

ErrHandler:
     Select Case Err
     Case ERR_TOOBIG
          MsgBox "ÀÔ·ÂÇÑ ¼ýÀÚ°¡ ³Ê¹« Å®´Ï´Ù.", vbExclamation
     Case ERR_NOTNUMBER
          If txtNumber = "" Then Resume Next
          MsgBo   x "ÀÔ·ÂÇÑ ³»¿ëÀÌ ¼ýÀÚ°¡ ¾Æ´Õ´Ï´Ù.", vbExclamation
     End Select

End Function
'ÇÑÀÚº¯È¯·çƾ
Function ConvertCurrencyToHanja(NumberText As String) As String
     Dim curAmount As Currency
     Dim StringReturn As String
     Dim strAmount As String
     Dim TmpString As String
     Dim AmountLength As Integer
     Dim i As Integer

     On Error GoTo ErrHandler

     curAmount = Format(NumberText, "#")
     AmountLength = Len(CStr(curAmount))
     strAmount = ""

     For i = AmountLength To 1 Step -1
          strAmount = strAmount & Mid(CStr(curAmount), i, 1)
     Next I

     For i = 1 To AmountLength
          If Mid(strAmount, i, 1) = "0" Then
              TmpString = ""
          Else
              TmpString = Mid(NUM_HANJA, Mid(strAmount, i, 1), 1)
              Select Case i
          Case 4, 8, 12
               TmpString = TmpString & "ôÆ"
          Case 3, 7, 11, 15
               TmpString = TmpString & "ÛÝ"
          Case 2, 6, 10, 14
               TmpString = TmpString & "ä©"
          End Select
       End If
       Select Case I
       Case 13
            TmpString = TmpString & "ð¼"
       Case 9
            TmpString = TmpString & "åâ"
       Case 5
            TmpString = TmpString & "Ø¿"
        End Select
          StringReturn = TmpString & StringReturn
     Next I
     ConvertCurrencyToHanja = "ìéÐÝ " & StringReturn & "¿ø ïÚ"

ErrHandler:
     Select Case Err
     Case ERR_TOOBIG
           MsgBox "ÀÔ·ÂÇÑ ¼ýÀÚ°¡ ³Ê¹« Å®´Ï´Ù.", vbExclamation
     Case ERR_NOTNUMBER
           If txtNumber = "" Then Resume Next
           MsgBox "ÀÔ·ÂÇÑ ³»¿ëÀÌ ¼ýÀÚ°¡ ¾Æ´Õ´Ï´Ù.", vbExclamation
      End Select

End Function

Àü¿ª»ó¼ö¿¡ ´ëÇØ ¸»¾¸ µå¸®ÀÚ¸é ÀÌ°ÍÀº ¸ðµâÀÇ ¼±µÎ ¼±¾ðÇÏ¿© ÇöÀçÀÇ ¸ðµâ ³»¿¡¼­ ¸ðµç ÇÁ·Î½ÃÁ®¿Í ÇÔ¼ö°¡ ÀÌ¿ë °¡´ÉÇÑ »ó¼öÀÔ´Ï´Ù. ¿©±â¿¡¼­ »ç¿ëÇÏ´Â Àü¿ª»ó¼ö¿¡´Â ¿¡·¯¸¦ ó¸®Çϱâ À§ÇÑ »ó¼ö ERR_TOOBIG°ú ERR_NOTNUMBERÀÌ ÀÖ½À´Ï´Ù. ÀÌ°ÍÀº ±× À̸§¿¡¼­µµ ¾Ë ¼ö ÀÖµíÀÌ »ç¿ëÀÚ°¡ ÀÔ·ÂÇÏ´Â ³»¿ëÀÌ Ã³¸®Çϱ⠰ï¶õÇÑ Áö³ªÄ¡°Ô Å« ¼ýÀÚÀ̰ųª ¼ýÀÚ°¡ ¾Æ´Ñ °æ¿ì¸¦ ´ëºñÇÑ °ÍÀÔ´Ï´Ù.

Const ERR_TOOBIG = 6
Const ERR_NOTNUMBER = 13

¶ÇÇÑ º¯È¯ÇÒ Çѱ۰ú ÇÑÀÚ¸¦ »ó¼ö·Î Á¤ÀÇÇØ µÎ¾ú½À´Ï´Ù.
Const NUM_HANGUL = "ÀÏÀÌ»ï»ç¿ÀÀ°Ä¥Æȱ¸"
Const NUM_HANJA = "ìììÃß³ÞÌçéë»öÒø¢Îú"

Function ConvertCurrencyToHangul(NumberText As String)¿Í Function ConvertCurrencyToHanja(NumberText As String) ÇÔ¼ö´Â °ÅÀÇ µ¿ÀÏÇÑ °ÍÀ̹ǷΠFunction ConvertCurrencyToHangul(NumberText As String)¸¸ ¼³¸íÇغ¸°Ú½À´Ï´Ù. 

Function ConvertCurrencyToHangul(NumberText As String) As String
ÇÔ¼ö´Â ¸Å°³º¯¼ö·Î NumberText¶ó´Â ½ºÆ®¸µÇü µ¥ÀÌÅ͸¦ ¹Þ½À´Ï´Ù. ±×·¸Áö¸¸ ³»¿ëÀº ¼ýÀÚÀÌÁÒ. Ãʺ¸·Î¼­´Â ¹®ÀÚ¿­ÀڷḦ ¹Þ´Âµ¥, ³»¿ëÀº ¼ýÀÚ¶ó´Â ¸»ÀÌ ÀÌÇØ°¡ ¾È °¥Áöµµ ¸ð¸¨´Ï´Ù. "9"´Â ¼ýÀڷμ­´Â ¾ÆÈ© °³, ¾ÆÈ© ¹ø°¸¦ ÀǹÌÇÕ´Ï´Ù. ¼ýÀڷμ­ °öÇϱâ,»©±â,´õÇϱâ,³ª´©±â µîµîÀÇ ¿¬»êÀÇ ´ë»óÀÌ µË´Ï´Ù. ±×·¯³ª ÀÌ´Â ³²°ú ´Ù¸£´Ù´Â °ÍÀ» Ç¥½ÃÇÏ´Â ¹®ÀÚ·Î »ç¿ëµÇ±âµµ ÇÕ´Ï´Ù. "9"°¡ ½Ã³»¹ö½ºÀÇ ¹øÈ£¶óµç°¡, ÀÚ½ÅÀÌ »ç´Â ¾ÆÆÄÆ®µ¿ÀÇ ¼ýÀÚ¸¦ ÀǹÌÇÒ ¼ö µµ ÀÖ½À´Ï´Ù. ÀÌ°ÍÀº ¿¬»êÀÇ ´ë»óÀÌ ¾Æ´ÏÁÒ. ÀüÈ­¹øÈ£µµ ¼ýÀÚ·Î ÀÌ·ç¾îÁ³Áö¸¸ ÀÌ°ÍÀ¸·Î ¹«½¼ ´õÇϰųª »©´Â ÀÏÀ» ÇÏÁø ¾Ê½À´Ï´Ù. Áï ¼ýÀÚ´Â ¿¬»êÀÇ ´ë»óÀ̱⵵ ÇÏÁö¸¸ ¹®Àڷμ­´Â Ç¥½ÃÀÇ ¼ö´ÜÀ̱⵵ ÇÕ´Ï´Ù. 

     Dim curAmount    As Currency
curAmount´Â NumberText°ú °°Àº ³»¿ëÀ» °¡ÁöÁö¸¸ ¹®ÀÚ°¡ ¾Æ´Ñ ¼ýÀÚ·Î °¡Áý´Ï´Ù. ±×·±µ¥ µ¥ÀÌÅÍÇüÀÌ CurrencyÇüÀ̱º¿ä. óÀ½ VBA¸¦ ¹è¿ï ¶§ ½½Â½ Áö³ªÃĹö¸° µ¥ÀÌÅÍ ÇüÀÔ´Ï´Ù. ÀÌ°ÍÀº ¸» ±×´ë·Î È­Æó¸¦ Ç¥½ÃÇϱâ À§ÇÑ µ¥ÀÌÅÍ ÇüÀÔ´Ï´Ù. µµ¿ò¸»¿¡ µû¸£¸é, { Á¤¼ö À¯ÇüÀÇ 64 ºñÆ®(8 ¹ÙÀÌÆ®) ¼ýÀÚ·Î ÀúÀåµË´Ï´Ù. Á¤¼ö ºÎºÐ 15ÀÚ¸®¿Í ¼Ò¼öÁ¡ ÀÌÇÏ 4ÀÚ¸®ÀÇ °íÁ¤ ¼Ò¼öÁ¡À» ÁÖ±â À§ÇÏ¿©, 10,000 ´ÜÀ§·Î Àß¶ó¼­ ÀÌ °ªÀº 922,337,203,685,477.5808ºÎÅÍ 922,337,203,685,477.5807±îÁö Ç¥ÇöÀÌ °¡´ÉÇÕ´Ï´Ù} ¶ó°í µÇ¾î ÀÖ½À´Ï´Ù. 

     Dim StringReturn As String
     Dim strAmount As String
     Dim TmpString As String
     Dim AmountLength As Integer
     Dim I As Integer

     On Error GoTo ErrHandler

     curAmount = Format(NumberText, "#")
ÀԷ¹ÞÀº ¹®ÀÚ¿­À» ¼ýÀÚ·Î º¯È¯ÇÕ´Ï´Ù.

     AmountLength = Len(CStr(curAmount))
°¢ ÀÚ¸®¼öÀÇ ¼ýÀÚ¸¦ ¹®ÀÚ·Î º¯È¯Çϱâ À§ÇØ ¸î °³ÀÇ ÀÚ¸®¼öÀΰ¡¸¦ Çì¾Æ¸³´Ï´Ù. À̶§AmountLength = Len(NumberText)·Î ÇÏÁö ¾Ê°í ±»ÀÌ ¼ýÀÚ·Î º¯È¯ ÇÑ °ÍÀ» ´Ù½Ã ¹®ÀÚ·Î º¯È¯ÇÏ¿© ÀÚ¸®¼ö¸¦ ¼¼´Â ÀÌÀ¯´Â ","(½°Ç¥) ¶§¹®ÀÔ´Ï´Ù. Ȥ½Ã »ç¿ëÀÚ°¡ õ´ÜÀ§¸¶´Ù ","Ç¥½Ã¸¦ ÇØ µÎ¾îNumberText¿¡ "123,456"ÀÌ ÀÖ´Ù¸é À̸¦ Len()ÇÔ¼ö¸¦ »ç¿ëÇÏ¿© ¼¼¾îº¸¸é 6ÀÚ¸®°¡ ¾Æ´Ï¶ó 7ÀÚ¸®°¡ µË´Ï´Ù. ±×·¯³ª À̸¦ curAmount = Format(NumberText, "#") °ú °°ÀÌ ¼ýÀÚ·Î º¯È¯ÇÑ ÈÄ ´Ù½Ã ¹®ÀÚ·Î º¯È¯Çϸé "123456"ÀÌ µÇ¹Ç·Î Á¤È®ÇÏ°Ô 6ÀÚ¸®¼ö°¡ ³ª¿É´Ï´Ù.

     strAmount = ""

     For i = AmountLength To 1 Step -1
          strAmount = strAmount & Mid(CStr(curAmount), i, 1)
     Next I
º¯È¯À» ¾Æ·§ÀÚ¸® ¼öºÎÅÍ Çϱâ À§ÇØ "123456"À» "654321"ó·³ µÚÁý½À´Ï´Ù. ¿©·¯ºÐµµ "ÀÏ, ½Ê, ¹é, õ, ¸¸, ¡¦"À» Áß¾ó°Å¸®¸ç ¼ýÀÚ¸¦ ¼¼¾î³ª°¡´Â °Íó·³ ¿©±â¿¡¼­µµ ³·Àº ´ÜÀ§ºÎÅÍ ¹®ÀÚ·Î º¯È¯ÇÏ´Â °ÍÀÌ Æí¸®ÇÏ¿© ¹®ÀÚ¸¦ µÚÁý½À´Ï´Ù.

     For i = 1 To AmountLength
¿©±â¼­ºÎÅÍ´Â µÚÁý¾î³õÀº ¹®ÀÚ¿­À» Çϳª Çϳª ²¨³»¾î ¹®ÀÚ·Î º¯È¯ÇÕ´Ï´Ù. 
           If Mid(strAmount, i, 1) = "0" Then
               TmpString = ""
¸¸ÀÏ "0"À̶ó¸é ¼¼¾îº¼ ÇÊ¿ä°¡ ¾øÀ¸¹Ç·Î ¾Æ¹« º¯È¯¾øÀÌ ³Ñ¾î°©´Ï´Ù.
           Else
                TmpString = Mid(NUM_HANGUL, Mid(strAmount, i, 1), 1)
ÇØ´ç¹®ÀÚ¿Í À§ÀÇ ¹®ÀÚ¿­ »ó¼ö³»ÀÇ À§Ä¡¸¦ ¸ÂÃß¾î Çϳª¸¸ °ñ¶ó³À´Ï´Ù. °¡·É Mid(strAmount, i, 1) °ªÀÌ 7À̶ó¸é NUM_HANGULÀÇ 7¹ø° ¹®ÀÚÀÎ "Ä¥"À» »Ì¾Æ µ¹·ÁÁÝ´Ï´Ù. 

            Select Case I
            Case 4, 8, 12
                 TmpString = TmpString & "õ"
            Case 3, 7, 11, 15
                 TmpString = TmpString & "¹é"
            Case 2, 6, 10, 14
                 TmpString = TmpString & "½Ê"
            End Select
        End If
        Select Case I
        Case 13
             TmpString = TmpString & "Á¶"
        Case 9
             TmpString = TmpString & "¾ï"
        Case 5
             TmpString = TmpString & "¸¸"
        End Select

À§ÀÇ Select~End Select¹®Àº ´ÜÀ§¿¡ ¸ÂÃß¾î ÀÚ¸®¼ö¸¦ ³Ö¾îÁÝ´Ï´Ù. "Ä¥"À̶ó°í Çϸé ÀÌ°ÍÀÌ ½Ê´ÜÀ§ÀÎÁö, õ´ÜÀ§ÀÎÁö¸¦ ´ÙÀ½¿¡ ³Ö¾îÁÖ¾î¾ß °ÚÁÒ.

          End If

          StringReturn = TmpString & StringReturn
¹®ÀÚ¿­À» °è¼Ó ½×¾Æ°©´Ï´Ù.

     Next i
     ConvertCurrencyToHangul = "ÀÏ±Ý " & StringReturn & "¿øÁ¤"
¸ðµÎ º¯È¯µÇ¾úÀ¸¸é ¹®ÀÚ¿­·Î º¯È¯µÈ °ªÀ» µ¹·ÁÁÝ´Ï´Ù. À̶§ ±Ý¾×À̹ǷΠ"ÀϱÝ~ ¿øÁ¤"À̶ó´Â Á¢µÎ¾î¿Í Á¢¹Ì¾î¸¦ ºÙ¿©ÁÝ´Ï´Ù.

ErrHandler:
     Select Case Err
     Case ERR_TOOBIG
          MsgBox "ÀÔ·ÂÇÑ ¼ýÀÚ°¡ ³Ê¹« Å®´Ï´Ù.", vbExclamation
     Case ERR_NOTNUMBER
          If txtNumber = "" Then Resume Next
          MsgBo   x "ÀÔ·ÂÇÑ ³»¿ëÀÌ ¼ýÀÚ°¡ ¾Æ´Õ´Ï´Ù.", vbExclamation
     End Select

¿¡·¯¿¡ ´ëÇÑ Ã³¸®·çƾÀÔ´Ï´Ù. 
End Function

Áö³­ ¹ø°ú ºñ±³ÇØ º¯°æµÈ ÇÁ·Î½ÃÁ®¿¡ ´ëÇØ ¼³¸íÇÏ°Ú½À´Ï´Ù. 
Const KEY_DOT = 46
¼Ò¼öÁ¡À» ÀÔ·ÂÇÏ´Â °æ¿ì "."(ÂÀ)À» ó¸®Çϱâ À§ÇÑ »ó¼öÀÔ´Ï´Ù. "."ÀÇ ¾Æ½ºÅ°Äڵ尪Àº 46ÀÔ´Ï´Ù.

Private Sub txtNumber_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
txtNumber¶ó´Â ÅؽºÆ®»óÀÚ¿¡ ¹º°¡¸¦ ÀÔ·ÂÇÏ´Â °æ¿ì ¹ß»ýÇÏ´Â À̺¥Æ®ÇÁ·Î½ÃÁ®ÀÔ´Ï´Ù. ¸Å°³º¯¼ö·Î´Â ¾Æ½ºÅ°Äڵ尪À» ¹Þ½À´Ï´Ù. 

     Select Case KeyAscii
     Case vbKey0 To vbKey9
¹ÞÀº ¾Æ½ºÅ°Äڵ尪ÀÌ 0~9±îÁöÀÇ ¼ýÀÚ¶ó¸é ±×´ë·Î Åë°ú½ÃÄÑÁÝ´Ï´Ù.
     Case KEY_DOT
          KeyAscii = IIf(InStr(1, txtNumber.Text, "."), 0, 46)
"."À̶ó¸é Çѹø¸¸ Åë°ú½ÃÄÑÁÝ´Ï´Ù. ¼ýÀÚ¿¡¼­´Â ¿©·¯ °³ÀÇ "."ÀÌ ÀÖÀ» ¼ö ¾øÀ¸¹Ç·Î ÀÌ¹Ì "."ÀÌ ÀÖ´Ù¸é 0°ªÀ» °­Á¦·Î ³Ö¾îÁÖ¾î ÀÔ·ÂÀ» ¹«È¿È­½Ãŵ´Ï´Ù.
     Case Else
±âŸÀÇ °æ¿ìÀε¥ ¹®ÀÚ¸¦ ÀÔ·ÂÇÏ´Â °æ¿ì ¿ª½Ã Å° °ªÀ» 0À¸·Î ¼³Á¤ÇØ ¹«È¿È­ÇÕ´Ï´Ù.

     End Select
End Sub

¿À´ÃÀº ¿©±â±îÁöÀÔ´Ï´Ù. ±×·³ À̸¸¡¦

¸ñÂ÷ | ÀÌÀü | ´ÙÀ½

¡¡

¿ÀÇǽºÆ©ÅÍ ±â¾÷ASP¼­ºñ½º 135-880 ¼­¿ï½Ã °­³²±¸ »ï¼ºµ¿ 157-3 ¿¤ÁöÆ®À©ÅÚ 2Â÷ 1603È£ ¢Ï070-7098-2554
Copyright ¨Ï 1999-2008 Officetutor.com All rights reserved
¡¡