VBA常用代碼解析（第四十五講）

wdmexcel 2015-10-27

展開全文

160 判斷是否為數(shù)值

使用IsNumeric函數(shù)可以判斷表達式的運算結(jié)果是否為數(shù)值，如下面的代碼所示。

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

EndWith

MsgBox'A列中數(shù)值單元格：' & Chr(13) & n & Chr(13) _

& 'A列中非數(shù)值單元格：' & Chr(13) & s

End Sub

代碼解析：

Numeric過程使用IsNumeric函數(shù)判斷工作表的A列單元格是否為數(shù)值，并使用消息框顯示。

第7行代碼判斷工作表的A列單元格是否為數(shù)值。IsNumeric函數(shù)返回Boolean值，指出表達式的運算結(jié)果是否為數(shù)，語法如下：

IsNumeric(expression)

參數(shù)expression是必需的，Variant類型，包含數(shù)值表達式或字符串表達式。

如果參數(shù)expression的運算結(jié)果為數(shù)字，則IsNumeric返回True，否則返回False。

第8行代碼將數(shù)值單元格的地址和數(shù)值保存在變量 e中。

第10行代碼將非數(shù)值單元格的地址和內(nèi)容保存在變量 s中。在保存時插入制表符對數(shù)據(jù)列進行分隔，使之排列整齊，請參閱▲73-5。

▲161 格式化數(shù)值、日期和時間

Format函數(shù)是VBA中的常用函數(shù)，可以實現(xiàn)數(shù)值、日期和時間格式的轉(zhuǎn)變，示例代碼如下：

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過程使用消息框顯示格式化后的數(shù)值、日期和時間。

Format函數(shù)根據(jù)格式表達式中的指令來格式化的數(shù)值、日期和時間，語法如下：

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

其中參數(shù)expression是必需的，任何有效的表達式。

參數(shù)format是可選的，有效的命名表達式或用戶自定義格式表達式。

第2行代碼將數(shù)值格式化為兩位小數(shù)格式顯示。

第3行代碼將數(shù)值格式化為兩位小數(shù)的百分比格式顯示。

第4行代碼將數(shù)值格式化為千位分隔符顯示。

第5行代碼將數(shù)值格式化為以美元符號顯示的兩位小數(shù)，以千位分隔符分隔，如果是負值則以小括號顯示。

第6行代碼將數(shù)值格式化為以人民幣符號顯示的兩位小數(shù)，以千位分隔符分隔，如果是負值則以小括號顯示。

第7行代碼將系統(tǒng)日期格式化為“yyyy-mm-dd”格式顯示。

第8行代碼將系統(tǒng)日期格式化為“yyyymmdd”格式顯示。

第9行代碼將系統(tǒng)日期格式化為長日期格式顯示。

第10行代碼將系統(tǒng)時間格式化為24小時、分鐘和秒的格式顯示。

第11行代碼將系統(tǒng)時間格式化為分12小時、分鐘和秒的格式顯示。

運行FromatCurrent過程結(jié)果。

▲162 計算個人所得稅

在財務工作中經(jīng)常需要計算個人所得稅，而在Excel中并沒有計算個人所得稅的函數(shù)，此時可以使用自定義函數(shù)來計算，如下面的代碼所示。

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函數(shù)根據(jù)應納稅額計算應納的個人所得稅額。

第5行代碼設置個人所得稅的起征點為2000元，如果以后需要調(diào)整起征點，可把2000元改為調(diào)整后的起征點。

第6行代碼設置應納稅所得額等于應納稅收入減去起征點。

第7行到第35行代碼根據(jù)全月應納稅所得額取得稅率和速算扣除數(shù)。稅率和速算扣除數(shù)根據(jù)如表格所示的工資、薪金所得適用個人所得稅九級超額累進稅率表計算。

第36行到第40行代碼根據(jù)應納稅所得額、稅率和速算扣除數(shù)計算應納的個人所得稅額。其中第39行代碼中使用工作表函數(shù)Round對計算結(jié)果進行四舍五入運算，在工作表中使用自定義PITax函數(shù)結(jié)果。

▲163 人民幣大寫函數(shù)

在VBA中沒有內(nèi)置的函數(shù)進行人民幣大寫轉(zhuǎn)換，此時可以編寫自定義函數(shù)進行人民幣大寫轉(zhuǎn)換，如下面的代碼所示。

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函數(shù)對小寫數(shù)字加上極小值后進行四舍五入運算，關于Round函數(shù)請參閱▲157-1。其次使用工作表Text函數(shù)將數(shù)值轉(zhuǎn)換成人民幣大寫格式表示的文本。Text函數(shù)將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本，語法如下：

TEXT(value，format_text)

Value參數(shù)為數(shù)值、計算結(jié)果為數(shù)值的公式，或?qū)Π瑪?shù)值的單元格的引用。

Format_text參數(shù)為“單元格格式“對話框中”數(shù)字“選項卡上”分類框中的文本形式的數(shù)字格式。

最后使用Replace函數(shù)將人民幣大寫格式表示的文本中的小數(shù)點替換成“元”。Replace函數(shù)返回一個字符串，該字符串中指定的子字符串已被替換成另一子字符串，并且替換發(fā)生的次數(shù)也是指定的，語法如下：

Replace(expression，find，replace[，start[，count[，compare]]])

其中參數(shù)expression是必需的，包含要替換的子字符串。

參數(shù)find是必需的，要搜索到的子字符串。

參數(shù)replace是必需的，用來替換的子字符串。

參數(shù)start是可選的，在表達式中子字符串搜索的開始位置。

第3行代碼使用了IIF函數(shù)、Left函數(shù)、Right函數(shù)根據(jù)第2行代碼返回的人民幣大寫格式表示的文本中的“元”的位置在文本中插入正確的“元”、“角”、“分”字符，使之符合人民幣大寫習慣。

IIf函數(shù)根據(jù)表達式的值，來返回兩部分中的其中一個，語法如下：

IIf(expr，truepart，falsepart)

參數(shù)expr是必需的，用來判斷真?zhèn)蔚谋磉_式。

參數(shù)truepart是必需的，如果expr為True，則返回這部分的值或表達式。

參數(shù)falsepart是必需的，如果expr為False，則返回這部分的值或表達式。

Left、Right函數(shù)請參閱▲158 。

第4行代碼使用Replace函數(shù)將人民幣大寫格式表示的文本中可能出現(xiàn)的“零元零角”、“零元”替換成空白字符；可能出現(xiàn)的“零角”替換成“零”。如果輸入負數(shù)的話，將“-”替換成“負”。在工作表中使用自定義RMBDX函數(shù)轉(zhuǎn)換人民幣大寫的效果。