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(i,1)) Then n = n & .Cells(i,1).Address(0,0) & Chr(9)& .Cells(i,1) & Chr(13) Else s = s & .Cells(i,1).Address(0,0) & Chr(9)& .Cells(i,1) & 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(Income,Optional 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 - Debit,2) 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.00000001,2),'[DBnum2]'),'.','元') RMBDX= IIf(Left(Right(RMBDX,3),1) = '元',Left(RMBDX,Len(RMBDX)- 1) & '角' & Right(RMBDX,1) & '分',IIf(Left(Right(RMBDX,2),1) = '元',RMBDX & '角整',IIf(RMBDX = '零',““,RMBDX & '元整'))) RMBDX= Replace(Replace(Replace(Replace(RMBDX,'零元零角',““),'零元',““),'零角','零'),'-','负') End Function 代码解析: 第2行代码首先使用Round函数对小写数字加上极小值后进行四舍五入运算,关于Round函数请参阅▲157-1。其次使用工作表Text函数将数值转换成人民币大写格式表示的文本。Text函数将数值转换为按指定数字格式表示的文本,语法如下: TEXT(value,format_text) Value参数为数值、计算结果为数值的公式,或对包含数值的单元格的引用。 Format_text参数为“单元格格式“对话框中”数字“选项卡上”分类框中的文本形式的数字格式。 最后使用Replace函数将人民币大写格式表示的文本中的小数点替换成“元”。Replace函数返回一个字符串,该字符串中指定的子字符串已被替换成另一子字符串,并且替换发生的次数也是指定的,语法如下: Replace(expression,find,replace[,start[,count[,compare]]]) 其中参数expression是必需的,包含要替换的子字符串。 参数find是必需的,要搜索到的子字符串。 参数replace是必需的,用来替换的子字符串。 参数start是可选的,在表达式中子字符串搜索的开始位置。 第3行代码使用了IIF函数、Left函数、Right函数根据第2行代码返回的人民币大写格式表示的文本中的“元”的位置在文本中插入正确的“元”、“角”、“分”字符,使之符合人民币大写习惯。 IIf函数根据表达式的值,来返回两部分中的其中一个,语法如下: IIf(expr,truepart,falsepart) 参数expr是必需的,用来判断真伪的表达式。 参数truepart是必需的,如果expr为True,则返回这部分的值或表达式。 参数falsepart是必需的,如果expr为False,则返回这部分的值或表达式。 Left、Right函数请参阅▲158 。 第4行代码使用Replace函数将人民币大写格式表示的文本中可能出现的“零元零角”、“零元”替换成空白字符;可能出现的“零角”替换成“零”。如果输入负数的话,将“-”替换成“负”。在工作表中使用自定义RMBDX函数转换人民币大写的效果。 |
|