wdmexcel / 待分类 / VBA常用代码解析(第四十五讲)

分享

   

VBA常用代码解析(第四十五讲)

2015-10-27  wdmexcel



160 判断是否为数值

使用IsNumeric函数可以判断表达式的运算结果是否为数值,如下面的代码所示。

Sub Numeric()

Dimi As Integer

Dimn As String

Dims As String

WithSheet1

For i = 1 To .Range('A65536').End(xlUp).Row

If IsNumeric(.Cells(i1)) Then

n = n & .Cells(i1).Address(00) & Chr(9)& .Cells(i1) & Chr(13)

Else

s = s & .Cells(i1).Address(00) & Chr(9)& .Cells(i1) & Chr(13)

End If

Next

EndWith

MsgBox'A列中数值单元格:' & Chr(13) & n & Chr(13) _

& 'A列中非数值单元格:' & Chr(13) & s

End Sub

代码解析:

Numeric过程使用IsNumeric函数判断工作表的A列单元格是否为数值,并使用消息框显示。

7行代码判断工作表的A列单元格是否为数值。IsNumeric函数返回Boolean值,指出表达式的运算结果是否为数,语法如下:

IsNumeric(expression)

参数expression是必需的,Variant类型,包含数值表达式或字符串表达式。

如果参数expression的运算结果为数字,则IsNumeric返回True,否则返回False

8行代码将数值单元格的地址和数值保存在变量 e中。

10行代码将非数值单元格的地址和内容保存在变量 s中。在保存时插入制表符对数据列进行分隔,使之排列整齐,请参阅▲73-5

161 格式化数值、日期和时间

Format函数是VBA中的常用函数,可以实现数值、日期和时间格式的转变,示例代码如下:

Sub FromatCurrent()

MsgBoxFormat(123456.789'0.00') & Chr(13) _

& Format(123456.789'0.00%') & Chr(13) _

& Format(123456.789'####0.00') & Chr(13) _

& Format(-123456.789'$###0.00;($###0.00)')& Chr(13) _

& Format(-123456.789'###0.00;(###0.00)') & Chr(13) _

& Format(Date'yyyy-mm-dd') & Chr(13) _

& Format(Date'yyyymmdd') & Chr(13) _

& Format(Date'Long Date') & Chr(13) _

& Format(Now'hh:mm:ss') & Chr(13) _

& Format(Now'hh:mm:ss AMPM')

End Sub

代码解析:

FromatCurrent过程使用消息框显示格式化后的数值、日期和时间。

Format函数根据格式表达式中的指令来格式化的数值、日期和时间,语法如下:

Format(expression[format[firstdayofweek[firstweekofyear]]])

其中参数expression是必需的,任何有效的表达式。

参数format是可选的,有效的命名表达式或用户自定义格式表达式。

2行代码将数值格式化为两位小数格式显示。

3行代码将数值格式化为两位小数的百分比格式显示。

4行代码将数值格式化为千位分隔符显示。

5行代码将数值格式化为以美元符号显示的两位小数,以千位分隔符分隔,如果是负值则以小括号显示。

6行代码将数值格式化为以人民币符号显示的两位小数,以千位分隔符分隔,如果是负值则以小括号显示。

7行代码将系统日期格式化为“yyyy-mm-dd”格式显示。

8行代码将系统日期格式化为“yyyymmdd”格式显示。

9行代码将系统日期格式化为长日期格式显示。

10行代码将系统时间格式化为24小时、分钟和秒的格式显示。

11行代码将系统时间格式化为分12小时、分钟和秒的格式显示。

运行FromatCurrent过程结果。

162 计算个人所得税

在财务工作中经常需要计算个人所得税,而在Excel中并没有计算个人所得税的函数,此时可以使用自定义函数来计算,如下面的代码所示。

Public Function PITax(IncomeOptional Threshold) As Single

DimRate As Single

DimDebit As Single

DimTaxliability As Single

If IsMissing(Threshold)Then Threshold = 2000

Taxliability= Income - Threshold

SelectCase Taxliability

Case 0 To 500

Rate = 0.05

Debit = 0

Case 500.01 To 2000

Rate = 0.1

Debit = 25

Case 2000.01 To 5000

Rate = 0.15

Debit = 125

Case 5000.01 To 20000

Rate = 0.2

Debit = 375

Case 20000.01 To 40000

Rate = 0.25

Debit = 1375

Case 40000.01 To 60000

Rate = 0.3

Debit = 3375

Case 60000.01 To 80000

Rate = 0.35

Debit = 6375

Case 80000.01 To 10000

Rate = 0.4

Debit = 10375

Case Else

Rate = 0.45

Debit = 15375

EndSelect

If Taxliability<= 0 Then

PITax = 0

Else

PITax = Application.Round(Taxliability* Rate - Debit2)

EndIf

End Function

代码解析:

自定义PITax函数根据应纳税额计算应纳的个人所得税额。

5行代码设置个人所得税的起征点为2000元,如果以后需要调整起征点,可把2000元改为调整后的起征点。

6行代码设置应纳税所得额等于应纳税收入减去起征点。

7行到第35行代码根据全月应纳税所得额取得税率和速算扣除数。税率和速算扣除数根据如表格所示的工资、薪金所得适用个人所得税九级超额累进税率表计算。

36行到第40行代码根据应纳税所得额、税率和速算扣除数计算应纳的个人所得税额。其中第39行代码中使用工作表函数Round对计算结果进行四舍五入运算,在工作表中使用自定义PITax函数结果。

163 人民币大写函数

VBA中没有内置的函数进行人民币大写转换,此时可以编写自定义函数进行人民币大写转换,如下面的代码所示。

Public Function RMBDX(M)

RMBDX= Replace(Application.Text(Round(M + 0.000000012)'[DBnum2]')'.''')

RMBDX= IIf(Left(Right(RMBDX3)1) = ''Left(RMBDXLen(RMBDX)- 1) & '' & Right(RMBDX1) & ''IIf(Left(Right(RMBDX2)1) = ''RMBDX & '角整'IIf(RMBDX = ''““RMBDX & '元整')))

RMBDX= Replace(Replace(Replace(Replace(RMBDX'零元零角'““)'零元'““)'零角''')'-''')

End Function

代码解析:

2行代码首先使用Round函数对小写数字加上极小值后进行四舍五入运算,关于Round函数请参阅▲157-1。其次使用工作表Text函数将数值转换成人民币大写格式表示的文本。Text函数将数值转换为按指定数字格式表示的文本,语法如下:

TEXT(valueformat_text)

Value参数为数值、计算结果为数值的公式,或对包含数值的单元格的引用。

Format_text参数为“单元格格式“对话框中”数字“选项卡上”分类框中的文本形式的数字格式。

最后使用Replace函数将人民币大写格式表示的文本中的小数点替换成“元”。Replace函数返回一个字符串,该字符串中指定的子字符串已被替换成另一子字符串,并且替换发生的次数也是指定的,语法如下:

Replace(expressionfindreplace[start[count[compare]]])

其中参数expression是必需的,包含要替换的子字符串。

参数find是必需的,要搜索到的子字符串。

参数replace是必需的,用来替换的子字符串。

参数start是可选的,在表达式中子字符串搜索的开始位置。

3行代码使用了IIF函数、Left函数、Right函数根据第2行代码返回的人民币大写格式表示的文本中的“元”的位置在文本中插入正确的“元”、“角”、“分”字符,使之符合人民币大写习惯。

IIf函数根据表达式的值,来返回两部分中的其中一个,语法如下:

IIf(exprtruepartfalsepart)

参数expr是必需的,用来判断真伪的表达式。

参数truepart是必需的,如果exprTrue,则返回这部分的值或表达式。

参数falsepart是必需的,如果exprFalse,则返回这部分的值或表达式。

LeftRight函数请参阅▲158

4行代码使用Replace函数将人民币大写格式表示的文本中可能出现的“零元零角”、“零元”替换成空白字符;可能出现的“零角”替换成“零”。如果输入负数的话,将“-”替换成“负”。在工作表中使用自定义RMBDX函数转换人民币大写的效果。


    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多
    喜欢该文的人也喜欢 更多

    ×
    ×

    ¥.00

    微信或支付宝扫码支付:

    开通即同意《个图VIP服务协议》

    全部>>