学习日志 全民一起VBA提高篇 第九回 万千数据划归同组，排列有序统一命名

学习日志

全民一起VBA提高篇

第九回 万千数据划归同组，排列有序统一命名

相关知识点

1、dim a(9) as long  这样定义的数组有10个元素 从 a(0)开始到a(9)
  
  dim a(3 to 6)  从a(3) 到 a(6)

  对于数组中每个元素循环时 可用 

  for i = lbound(a) to upbound(a) 

  next i 

公元2019年转换大写汉字，无单位

Option Explicit
Sub testyear()
    
    Dim s$, a(9) As String, i%, j%, sc As String
    
    s = CStr(Trim(Cells(7, 2)))
    
    a(0) = "零": a(1) = "壹": a(2) = "贰": a(3) = "叁": a(4) = "肆": a(5) = "伍" & _
    a(6) = "陆": a(7) = "柒": a(8) = "捌": a(9) = "玖"
    
    j = Len(s)
    
    sc = ""
    
    For i = 1 To j
    
        If IsNumeric(Mid(s, i, 1)) Then
        
            sc = sc + a(Mid(s, i, 1))
            
        Else
            
            sc = sc + Mid(s, i, 1)
            
        End If
    
    Next i
    
    Cells(7, 3) = sc

End Sub

公元2019年转换大写汉字，带单位

option explicit
Sub testyear2()
    Dim s$, a(9) As String, i%, j%, sc As String, b(3), k, m
'将单元格数据去除两端空格，并转换为字符串，存于变量 s 中
    s = CStr(Trim(Cells(7, 2)))
'定义与阿拉伯数字对应的大写数字的数组
    a(0) = "零": a(1) = "壹": a(2) = "贰": a(3) = "叁": a(4) = "肆": a(5) = "伍" & _
    a(6) = "陆": a(7) = "柒": a(8) = "捌": a(9) = "玖"
'将字符串s的长度赋给变量 j
    j = Len(s)
'将输出字符串清空
    sc = ""
'定义单位数组
    b(0) = "仟": b(1) = "佰": b(2) = "拾": b(3) = ""
'for循环用于判断给定字符串中代表年份的数字有几位，存在k中
    k = 0
    For i = 1 To j
        If IsNumeric(Mid(s, i, 1)) Then
            k = k + 1
        End If
    Next i
'循环扫描字符串中每一个字符，若为数字，则将其换为大写，并根据其数字位数匹配
'对应的单位；若不是数字，则照抄
    m = 0
    For i = 1 To j
    
        If IsNumeric(Mid(s, i, 1)) Then
        
            If k = 4 Then
                sc = sc + a(Mid(s, i, 1)) & b(m)
                m = m + 1
            ElseIf k = 3 Then
                sc = sc + a(Mid(s, i, 1)) & b(m + 1)
                m = m + 1
            ElseIf k = 2 Then
                sc = sc + a(Mid(s, i, 1)) & b(m + 2)
                m = m + 1
            Else
               sc = sc + a(Mid(s, i, 1))
            End If
        Else
            sc = sc + Mid(s, i, 1)
        End If
    Next i
    Cells(7, 4) = sc
End Sub