Idea, Design, Engineering, Architecture, etc

ExcelVBAŠ¿”Žš‚𐔒l‚É•ÏŠ·‚·‚éƒ}ƒNƒ

ƒJƒeƒSƒŠ: ‹Zp | ƒ^ƒO: EXCEL VBA Š¿”Žš ”’l NUMBERSTRING ŠÖ” •��Žš—ñ ’l

¦[ƒuƒbƒNƒ}[ƒN]ƒ{ƒ^ƒ“‚Ì’†‚É delicious, reddit, digg, Google+(G+) ‚̃{ƒ^ƒ“‚à‚ ‚è‚Ü‚·B --> Sep. 2nd, 2011’Ç‹LF‹vX‚ÉŒ©‚È‚�N‚µ‚Ä‚Ý‚½‚çAçA•SA\‚Ȃǂ̏��‚³‚È‚­‚ç‚��‚̏ˆ—‚ɃoƒO‚ðŒ©‚Â‚¯‚½BC³‚µ‚½‚à‚Ì‚ð github ‚ÉŠi”[‚µ‚Ä‚��‚éB‰º‹L‚̃R[ƒh‚àXV‚µ‚½B

‚Ü‚½‚�}‚̏C³‚É”º‚��AV‚½‚ɁAˆëäÝ‚È‚Ç‚ÌŠ¿Žš•\‹L‚É‚æ‚銿”Žš‚àƒTƒ|[ƒg‚·‚éŽd—l’ljÁ‚ðs‚Á‚½B

Š¯Œö’¡‚Ì”­•\‚µ‚Ä‚��‚é•��‘‚ð“Ç‚ñ‚Å‚��‚é‚Æ‚«‚ɁAƒOƒ‰ƒt‚É‚µ‚Ä‚Ý‚½‚��ƒf[ƒ^‚ª—L‚Á ‚½B‚»‚̃f[ƒ^•”•ª‚ðƒRƒsƒy‚ŁAExcel‚É’��‚è•t‚¯‚Ä‚��‚½B

‚Æ‚�}‚낪A‚»‚̃f[ƒ^‚ÍŠ¿”Žš‚Å•\‹L‚³‚ê‚Ä‚��‚½‚Ì‚¾B“–‘R‚�}‚̃f[ƒ^‚́AƒZƒ‹ ã‚Å•��Žš—ñ(String)‚Æ‚µ‚Ä”FŽ¯‚³‚ê‚éBƒOƒ‰ƒt‚ðì��‚·‚é‚Ì‚½‚߂ɂ́A”’lƒf[ ƒ^‚ª•K—v‚¾„Ÿ„ŸŠ¿”Žš‚ðA‚��‚¿‚��‚¿Žè‘Å‚¿‚Ő”’l‚ɏC³‚·‚é‚̂̓oƒJƒoƒJ‚µ‚��c ‰ºŒü‚��‚¿‚Ⴄ‚µB

@ ‚�}‚̂悤‚ÈŽv‚��‚©‚çAExcel‚ŁAŠ¿”Žš‚𐔒l‚É•ÏŠ·‚·‚é•û–@‚ɂ‚��‚Ä‚®‚®‚Á‚Ä‚Ý‚½‚Æ‚�}‚ëAŽ—‚½‚悤‚ȃj[ƒY‚ª‚ ‚Á‚½‚悤‚ŁA‚��‚­‚‚©ŽQl‚Æ‚È‚éVBAƒ}ƒNƒ‚ðŒ©‚Â‚¯‚é‚�}‚Æ‚ª‚Å‚«‚½B ‚µ‚©‚µA‚�}‚Ì—á‚̏ꍇ‚Í

• 4Œ…‚܂ł̐”Žš{ˆÊ‚ð•\‚·Š¿”Žš‚Æ‚��‚¤Œ`Ž®
• ˆÊ‚ªŒÅ’è
• ƒf[ƒ^‚ÌŽw’è•û–@‚ªƒZƒ‹‚Ì‚Ý

‚È‚Ç‚Ì“_‚ŁAˆê”ʐ«‚É‚©‚¯‚é‚悤‚ÉŽv‚í‚ꂽB   ‚»‚�}‚ŁA“–•û‚Å

• “ñç\A“ñZˆêZA2ç\A‚��‚¸‚ê‚Ì•\‹L‚à‰Â
• Z`‹ž‚Ì’PˆÊ‚܂ŁA_“î‚ÉŽg—p‰Â
• ˆø”‚́AƒZƒ‹A‚à‚µ‚­‚Í•��Žš—ñ‚��‚¸‚ê‚àŽg—p‰Â

  ‚Å‚ ‚é‚悤‚ȁAƒ}ƒNƒŠÖ”(Function): STRINGNUMBER ‚ðì‚Á‚Ä‚Ý‚½B  

---

---

Attribute VB_Name = "KanNum"
'''' KanNum.bas
'
'
'Author: mephistobooks (http://voidptr.seesaa.net)
'Date: 2010 Aug. 13
'Updated: Sep. 1st, 2011 bugfix (ç, •S, \)
'


''' as use stricts in Perl;
Option Explicit

'Private Const DEBUG_KANNUM = True
Private Const DEBUG_KANNUM = False


'NAME
'  STRINGNUMBER
'
'SYNOPSIS
'  =STRINGNUMBER(v)
'  v ‚́AŠ¿”Žš‚ŏ‘‚©‚ꂽAƒZƒ‹‚Ü‚½‚Í•��Žš—ñ
'
'
'DESCRIPTION
'  Š¿”Žš‚𐔒l‚É•ÏŠ·‚·‚éB”’l‚ðŠ¿”Žš‚É•ÏŠ·‚·‚éŠÖ”FNUMBERSTRING ‚Ì‹t‚ðs‚¤B
'  Š¿”Žš‚́A‹ž‚Ì’PˆÊ‚Ü‚ÅŽw’è‚Å‚«‚éB
'  Š¿”Žš‚ÌŽw’è•û–@‚́AƒZƒ‹‚Ü‚½‚Í•��Žš—ñ‚ÅŽw’è‚Å‚«‚éB
'  Š¿”Žš‚Ì•\‹L‚́A‰º‹L‚Ì—á‚Ì‚��‚¸‚ê‚É‚à‘ΉžF
'
'  —á.
'       STRINGNUMBER("ˆê‹ãŽµ˜Z") => 1,976
'   STRINGNUMBER("ç‹ã•SŽµ\˜Z") => 1,976
'        STRINGNUMBER("56–œ3ç") => 563,000
'  STRINGNUMBER("ŽQ蔌ޕSä݈ëE") => 35,000,010
'
'REFERENCES
'  Q.gExcel‚ÅŠ¿”Žš‚𐔒l‚É•ÏŠ·‚·‚é•û–@‚ð‹³‚¦‚Ä‚­‚¾‚³‚��Bh
'  http://q.hatena.ne.jp/1268555767
'
Public Function STRINGNUMBER(ByVal varcl As Variant) As Variant
    '''
    Dim str As String       'kanji-number string (working variable)
    Dim str_org As String   'kanji-number string (original)
    
    '
    Dim tmp As String
    
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    '''' (0) Initialize the variables
    '
    tmp = TypeName(varcl)
    
    If DEBUG_KANNUM Then
        Call MsgBox("TypeName(varcl)[" & tmp & "]" & vbCrLf & _
                    "str[" & str & "]")
    End If
    
    
    ' Process the argument due to its type.
    If TypeName(tmp) = "String" Then
        str_org = varcl
    Else
        str_org = varcl.Value
    End If
    
    'Kanji-number string.
    str = str_org
        
    If DEBUG_KANNUM Then
        Call MsgBox("TypeName(varcl)[" & tmp & "]" & vbCrLf & _
                    "str[" & str & "]")
    End If
    
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Algorithm:
    '
    '*Assumption
    '  assume that kanji-number string (Š¿”Žš•��Žš—ñ) consists of three type of
    '  number string: compound units, semi-compound units, and literal
    '  numbers.
    '
    '  compound units (‹ž,’›,‰­,–œ) consists of semi-compound units
    '  and literal number string.
    '
    '  semi-compound units (ç,•S,\) consists of themselves (ex. ç\),
    '  and also they sometimes includes literal number string (ex. 3ç4•S).
    '
    '  literal number string (Z`‹ã) is able to convert to number (value)
    '  directly.
    '
    '*Way of getting the value of kanji-number string
    '  generate the expression from kanji-number string like the following:
    '    ((n_1)‰­)+(n_2)–œ+(n_3))
    '  where n_? is a*1000+b*100+c*10+d, and a-d here indicates 0-9.
    '
    '  after the step (4), we can obtain the above expression which
    '  consists from only numbers, parentheses, product(*), and addition(+).
    '
    '  and finally, the value is obtained by evaluation of the generated
    '  expression at the step (5)
    '
    
    '''' (1) Normalize the value.
    '@@`normalize' means that hankaku, and all characters of
    ' [‚O-‚X],[Z-‹ã] to [0-9].
    '
    str = StrConv(str, vbNarrow) '”¼Šp
    
    'for semi-compound units
    str = Replace(str, "E", "\")
    str = Replace(str, "è”", "ç")
    str = Replace(str, "äÝ", "–œ")
        
    'for the literal number strings.
    str = Replace(str, "‹ã", "9")
    str = Replace(str, "ӻ", "8")
    str = Replace(str, "Žµ", "7")
    str = Replace(str, "˜Z", "6")
    
    str = Replace(str, "Ά", "5")
    str = Replace(str, "ŒÞ", "5")
    
    str = Replace(str, "Žl", "4")
    
    str = Replace(str, "ŽO", "3")
    str = Replace(str, "ŽQ", "3")
    
    str = Replace(str, "“ñ", "2")
    str = Replace(str, "“ó", "2")
    
    str = Replace(str, "ˆê", "1")
    str = Replace(str, "ˆë", "1")
    
    str = Replace(str, "Z", "0")
    
    
    '''' (2) Structurize for the compound units: ‹ž,’›,‰­,–œ.
    '
    '  (.*)’›+(.*)‰­+(.*)–œ+(.*)
    '
    str = "(" + str
    
    '
    str = Replace(str, "‹ž", ")‹ž+(")
    str = Replace(str, "’›", ")’›+(")
    str = Replace(str, "‰­", ")‰­+(")
    str = Replace(str, "–œ", ")–œ+(")
      
    str = str + ")"
    
       
    ''' (3) Numerize the value
    
    'semi-compound units
    str = Replace(str, "ç", "*1000+")
    str = Replace(str, "•S", "* 100+")
    str = Replace(str, "\", "*  10+")
    
    'compound units
    str = Replace(str, "‹ž", "*10000000000000000+")
    str = Replace(str, "’›", "*    1000000000000+")
    str = Replace(str, "‰­", "*        100000000+")
    str = Replace(str, "–œ", "*            10000+")
    
    
    ''' (4) correct the expression of the value.
    str = Replace(str, "()", "0")
    
    str = Replace(str, "++", "+")
    str = Replace(str, "+)", "+0)")
          
    str = Replace(str, "(*", "(1*")
    str = Replace(str, "+*", "+1*")
    
    If DEBUG_KANNUM Then
        Call MsgBox("org:" & str_org & vbCrLf & "str:" & str)
    End If
    
    
    ''' (5) Eval the generated expression!
    STRINGNUMBER = Application.Evaluate(str)
    
End Function

Še\��’PˆÊ‚²‚ƂɁAŠÛŠ‡ŒÊ‚Å‚­‚­‚é‚�}‚Æ‚ªA‚Ý‚»‚¾B ÅI“I‚ɁAŠ¿”Žš•”•ª‚͐”Žš‚É’u‚«Š·‚¦‚ç‚êA”Ž®‚ÌŒ`‚É‚È‚éB‚»‚µ‚āA‚»‚ꂪApplication.Evaluate()‚É‚æ‚èA”’l‚É•ÏŠ·‚³‚ê‚éB

Have fun!!