Excel函數(shù)VBA教程基礎(chǔ)入門課件

Excel函數(shù)VBA教程基礎(chǔ)入門Excel函數(shù)VBA教程基礎(chǔ)入門Excel函數(shù)VBA教程基礎(chǔ)入門VBA簡易教程

（下）12021/2/21Excel函數(shù)VBA教程基礎(chǔ)入門Excel函數(shù)VBA教程基礎(chǔ)VBA簡易教程

（下）22021/2/21VBA簡易教程

（下）22021/2/21創(chuàng)建過程

創(chuàng)建第一個過程需要兩個基本步驟。首先，需要向工作簿中添加一個模塊。接著需要向模塊中添加一個工程。對于創(chuàng)建的每一個應(yīng)用程序，只需添加一次模塊。可以使用多個模塊，但這是不必要的。某些開發(fā)者喜歡使用多個模塊，以便根據(jù)他們的目的或者窗體對過程進行組織。在本練習中，創(chuàng)建的過程只顯示一個消息框。

在本練習中創(chuàng)建的過程只顯示一個消息框。在本練習中使用Msgbox是為了提供一個可見的例子，雖然我們還沒有介紹過Msgbox語句，但是在本例中將使用它。要創(chuàng)建該過程，請按如下步驟進行：打開一個新工作簿。選擇"工具"-"宏"-"Visual

Basic編輯器"，打開VBA編輯器窗口。32021/2/21創(chuàng)建過程創(chuàng)建第一個過程需要兩個基本步驟。打開一個新工作簿。在`VBA編輯器的左面，可以看到“工程資源管理器”窗口。在工程資源管理器窗口的“Thisworkbook"上單擊鼠標右鍵，選擇“插入”-“模塊”，這樣就將一個模塊添加到應(yīng)用程序中了。(如果你沒有看見“工程資源管理器”窗口，可以按Ctrl+R)。選擇“插入”“過程”，顯示“添加過程”對話框。輸入“第一個工程”作為過程名字。在“類型”分組框中，確認選擇了“子程序”。單擊“確定”按鈕。這樣一個新的過程就添加到模塊中了?？梢栽谀K中看到以

Public

Sub

第一個過程（）開始，以End

Sub結(jié)束的語句結(jié)構(gòu)。42021/2/21在`VBA編輯器的左面，可以看到“工程資源管理器”窗口。選擇6）在過程中插入光標，輸入以下語句并回車：Msgbox

"這是我的第一個過程"在輸入Msgbox后，會自動彈出一個消息框告訴你有關(guān)這條命令的信息，稱之為自動列表技術(shù)。輸入完成的過程如下所示：Public

Sub

第一個過程（）

Msgbox

"這是我的第一個過程"

End

SubVBA對子程序和函數(shù)有如下的命名規(guī)則：

*

名字中可以包含字母數(shù)字和下劃線。

*

名字中不能包含空格句號驚嘆號，也不能包含字符@

&

$

#.?

*

名字最多可以包含255個字符。52021/2/216）在過程中插入光標，輸入以下語句并回車：在輸入Msgbo運行宏

創(chuàng)建這個過程后，可以運行一下。運行一個過程有幾種方法：可以直接使用“運行”菜單，“運行子程序/用戶窗體”工具欄按鈕或按下F5鍵。要運行一個過程，可以按照如下步驟：

1）單擊“運行子程序/用戶窗體”工具欄按鈕，過程執(zhí)行并顯示一個消息框。

2）單擊消息框之中的“確定”按鈕，關(guān)閉該消息框。

62021/2/21運行宏創(chuàng)建這個過程后，可以運行一下。62021/2/21保存對模塊所做的改變

要保存新過程,需要保存過程所駐留的工作簿.可以用VBA編輯器保存工作簿.具體步驟如下:

選擇"文件"-"保存工作簿".因為本工作簿還沒有保存過,所以要給它命名.輸入“HOUR4”作為文件名并按回車鍵,則工作簿和模塊與過程都保存下來了.

72021/2/21保存對模塊所做的改變要保存新過程,需要保存過程所駐留的工作變量

變量是用于臨時保存數(shù)值的地方.每次應(yīng)用程序運行時,變量可能包含不同的數(shù)值,而在程序運行時,變量的數(shù)值可以改變.

為了說明為什么需要變量,可以按照如下步驟創(chuàng)建一個簡單的過程:

1)創(chuàng)建一個名為"你叫什么名字"的過程.

2)在過程中輸入如下代碼:

Inputbox

"輸入你的名字:"3)按下F5鍵運行過程,這時會顯示一個輸入框,要求輸入你的名字.

4)輸入你的名字并按"確定"按鈕,則結(jié)束該過程.

你輸入的名字到那里去了?如何找到用戶在輸入框中輸入的信息?在這種情況下,需要使用變量來存儲用戶輸入的結(jié)果.82021/2/21變量變量是用于臨時保存數(shù)值的地方.82021/2/21變量的數(shù)據(jù)類型

使用變量的第一步是了解變量的數(shù)據(jù)類型.變量的數(shù)據(jù)類型控制變量允許保存何種類型的數(shù)據(jù).表列出了VBA支持的數(shù)據(jù)類型,還列出了各種類型的變量所需要的存儲空間和能夠存儲的數(shù)值范圍.數(shù)據(jù)類型存儲空間數(shù)值范圍Byte1字節(jié)0

-

255Boolean2字節(jié)True或者FalseInteger2字節(jié)-32768

-

32767Long(長整型)4字節(jié)-2147483648

-

2147483647Single4字節(jié)負值范圍:-3.402823E38~

-1.401298E-45正值范圍:1.401298E-45

~

3.402823E38Object4字節(jié)任何引用對象String(長字符串）10字節(jié)+1字節(jié)/字符0

-

約20億92021/2/21變量的數(shù)據(jù)類型使用變量的第一步是了解變量的數(shù)據(jù)類型.變量的變量的數(shù)據(jù)類型數(shù)據(jù)類型存儲空間數(shù)值范圍Double8字節(jié)負值范圍:-1.79769313486232E308

~

-4947E-324正值范圍:4.947E-324

~

1.79769313486232E308Decimal14字不包括小數(shù)時:+/-79228162514264337593543950335包括小數(shù)時:+/7.9228162514264337593543950335Currency8字節(jié)-922337203685477~

922337203685477Date8字節(jié)1000年1月1日

-

9999年12月31日String(固定長度）字符串的長度1

~

約65400Varient（數(shù)字）16字節(jié)Double范圍內(nèi)的任何數(shù)值Varient（文本）22字節(jié)+1字節(jié)/字符數(shù)據(jù)范圍和變長字符串相同102021/2/21變量的數(shù)據(jù)類型數(shù)據(jù)類型存儲空間數(shù)值范圍Double8字節(jié)負值用Dim語句創(chuàng)建變量(聲明變量)

現(xiàn)在,你對變量可以使用的數(shù)據(jù)類型已經(jīng)比較熟悉了,以下我們將創(chuàng)建變量.創(chuàng)建變量可以使用Dim語句,創(chuàng)建變量通常成為"聲明變量"。Dim語句的基本語法如下:

Dim

變量名

AS

數(shù)據(jù)類型變量名必須以字母開始,并且只能包含字母數(shù)字和特定的特殊字符,不能包含空格句號驚嘆號,也不能包含字符@

&

$?

#.名字最大長度為255個字符。112021/2/21用Dim語句創(chuàng)建變量(聲明變量)現(xiàn)在,你對變量可以使用的數(shù)變量命名的慣例

下表給出了推薦的變量命名慣例

數(shù)據(jù)類型短前綴長前綴

數(shù)據(jù)類型短前綴長前綴ArrayaaryBooleanfbinBytebbitCurrencyccurDoubleddblDate/Timedtdtm/datIntegeriintLongllngObjectoobjSinglesngStringsstrVariantvvar122021/2/21變量命名的慣例下表給出了推薦的變量命名慣例

數(shù)據(jù)類型短前綴使用數(shù)組如果你使用過其他編程序語言,可能對數(shù)組已經(jīng)比較熟悉了.數(shù)組是具有相同數(shù)據(jù)類型并共同享有一個名字的一組變量的集合.數(shù)組中的元素通過索引數(shù)字加以區(qū)分,定義數(shù)組的方法如下:Dim

array_name(n)

As

type(其中n是數(shù)組元素的個數(shù))例如,如果要創(chuàng)建保存10個學生名字的數(shù)組,可以用以下語句:Dim

s學生名字(9)

As

Integer132021/2/21使用數(shù)組如果你使用過其他編程序語言,可能對數(shù)組已經(jīng)比較熟悉了使用數(shù)組聲明數(shù)組時的另一種方法是不給定大小?？梢栽诔绦蜻\行時定義其大小。通過創(chuàng)建動態(tài)數(shù)組就可以做到。例如，你的程序要創(chuàng)建一表格，可以提示用戶輸入表格的行和列的數(shù)目。聲明動態(tài)數(shù)組的語法如下：Dim

dyn_array()

As

type對數(shù)組聲明后可以在程序運行時用ReDim語句指定數(shù)組的大?。篟eDim

dyn_array()(array_size)參數(shù)array_size代表數(shù)組的新大小。如果要保留數(shù)組的數(shù)值，請在ReDim語句后使用保留字Preserve,具體語法如下：ReDim

Preserve

dyn_array(array_size)142021/2/21使用數(shù)組聲明數(shù)組時的另一種方法是不給定大小?？梢栽诔绦蜻\行時變量賦值

聲明變量后就可以給變量賦值。請注意下列語句中為數(shù)組變量賦值時索引數(shù)字的使用。

Dim

i人數(shù)

As

IntegerDim

i考試成績

As

IntegerDim

i

As

Integeri人數(shù)

=

inputbox("輸入學生的人數(shù)：")ReDim

Preserve

i考試成績(i數(shù)量)For

i

=

1

to

i人數(shù)i考試成績(i)

=

inputbox("輸入考試成績"&

i

)Next152021/2/21變量賦值聲明變量后就可以給變量賦值。請注意下列語句中為數(shù)組使用常量

現(xiàn)在,你已經(jīng)知道變量是存儲非靜態(tài)信息的存儲容器.當存儲靜態(tài)信息時可以創(chuàng)建常量.它可以供程序多次使用而且便于記憶.比如圓周率比3.1415926好理解得多.

要聲明常量并設(shè)置常量的值,需要使用const語句.常量聲明后,不能對它賦一個新的值.例如,假設(shè)需要聲明一個常量來保存銷項稅率,可以使用以下語句:

通常常量聲明時用全大寫字母以區(qū)分變量.

const

銷項稅率

As

Long

=

0.17162021/2/21使用常量現(xiàn)在,你已經(jīng)知道變量是存儲非靜態(tài)信息的存儲容器.當作用域

到現(xiàn)在為止,已經(jīng)學習了如何定義變量和常量,但是還不知道在何處定義.可以在兩個地

方定義常量和變量:

過程中定義和在模塊頂部一個名為“通用聲明”的區(qū)域內(nèi)定義.定義的位置決定租用域.在過程中聲明,該變量只能在該過程中使用,其他過程中不能使用這個變量和常量,這中變量成為局部變量或過程級變量；在模塊頂部聲明的變量稱為模塊級變量，該變量在該模塊的所有過程中都可以使用。172021/2/21作用域到現(xiàn)在為止,已經(jīng)學習了如何定義變量和常量,但作用域還有一種等級的作用域稱為公共級。公共級變量可以在應(yīng)用程序的任何過程中使用，不論過程和變量是否定義在一個模塊。這就使得公共級變量在使用中十分靈活，但是公共級變量在程序運行時一直保留在內(nèi)存中，這樣就占用了系統(tǒng)資源。要創(chuàng)建公共級變量，可以用Public語句，具體語法如下：Public

variablename

As

datatype要創(chuàng)建公共級常量，具體語法如下：Public

const

CONSTANAME

datatype

=

value※公共級變量或常量必須在模塊頂部的“通用聲明”區(qū)域中定義。182021/2/21作用域還有一種等級的作用域稱為公共級。Public

vari利用VBA設(shè)置工作表使用權(quán)限

Excel

Home

一般保護工作表采取的方法是用EXCEL菜單中的“保護”命令，有時這尚嫌不足，比如一些機密文件根本要讓某些使用者無法看到，但又需要他來操作工作簿中的其他表，怎么辦？

可以打開VBA編輯器，打開“工程資源管理器”，雙擊該工作表，現(xiàn)在出現(xiàn)的是設(shè)置該表的屬性的編輯窗口，單擊窗口左上的下拉列表框，選擇

worksheet

,這時再從該窗口右上方的列表框中選擇Active(“激活”），這時自動顯示如下的語句塊：Private

Sub

Worksheet_Activate()

End

Sub

192021/2/21利用VBA設(shè)置工作表使用權(quán)限Excel

Home

一般利用VBA設(shè)置工作表使用權(quán)限在其中加入代碼：(假設(shè)用“123”作為密碼,Sheet“機密文檔”為限制權(quán)限文檔,sheet"普通文檔"為工作簿中你認為任何適合的工作表)If

Application.InputBox("請輸入操作權(quán)限密碼:")

=

123

ThenRange("A1").SelectElseMsgbox

"密碼錯誤,即將退出!"Sheets("普通文檔").SelectEnd

if202021/2/21利用VBA設(shè)置工作表使用權(quán)限在其中加入代碼：(假設(shè)用“123利用VBA設(shè)置工作表使用權(quán)限完整的程序如下：Private

Sub

Worksheet_Activate()If

Application.

InputBox("請輸入操作權(quán)限密碼:")

=

123

ThenRange("A1").SelectElseMsgBox

"密碼錯誤,即將退出!"Sheets("普通文檔").SelectEnd

IfEnd

Sub212021/2/21利用VBA設(shè)置工作表使用權(quán)限完整的程序如下：Private

利用VBA設(shè)置工作表使用權(quán)限

這樣做仍有一個問題，就是越權(quán)使用者仍會看到一些文件的片段，即在提示密碼的那段時間。你可以這樣做，用上述方法選擇工作表的Deactivate事件,輸入以下代碼：Sheets("機密文檔").Cells.Font.ColorIndex

=

2這段程序使得此工作表在不被激活時，所有文字為白色。然后，在第一個程序中的Range("A1").Select后插入一行，寫入以下代碼：ActiveSheet.Cells.Font.ColorIndex

=

56這段程序，在你輸入正確密碼后，將該表所有文字轉(zhuǎn)變?yōu)樯罨疑?22021/2/21利用VBA設(shè)置工作表使用權(quán)限這樣做仍有一個問題，就是越利用VBA設(shè)置工作表使用權(quán)限完整的程序如下：Private

Sub

Worksheet_Activate()If

Application.InputBox("請輸入操作權(quán)限密碼:")

=

123

ThenRange("A1").SelectSheets("機密文檔").Cells.Font.ColorIndex

=

56ElseMsgBox

"密碼錯誤,即將退出!"Sheets("普通文檔").SelectEnd

If232021/2/21利用VBA設(shè)置工作表使用權(quán)限完整的程序如下：Private

提高VBA運行效率由于Microsoft

Office辦公套件的廣泛應(yīng)用，以及該軟件版本的不斷提升，功能不斷完善，在Office辦公套件平臺上開發(fā)出的VBA應(yīng)用程序越來越多，而VBA是一種宏語言，在運行速度上有很大的限制。因此VBA編程的方法直接關(guān)系到VBA程序運行的效率，本文列舉了一些提高VBA程序運行效率的方法。

方法1：盡量使用VBA原有的屬性、方法和Worksheet函數(shù)由于Excel對象多達百多個，對象的屬性、方法、事件多不勝數(shù)，對于初學者來說可能對它們不全部了解，這就產(chǎn)生了編程者經(jīng)常編寫與Excel對象的屬性、方法相同功能的VBA代碼段，而這些代碼段的運行效率顯然與Excel對象的屬性、方法完成任務(wù)的速度相差甚大。242021/2/21提高VBA運行效率由于Microsoft

Office辦公套提高VBA運行效率For

Each

c

InWorksheet(1).Range(〃A1:A1000〃)TotalValue

=

TotalValue

＋

c.ValueNextAverageValue

=

TotalValue

/

Worksheet(1).Range(〃A1:A1000〃).Rows.Count如求平均工資的例子：例如用Range的屬性

CurrentRegion來返回Range

對象，該對象代表當前區(qū)。（當前區(qū)指以任意空白行及空白列的組合為邊界的區(qū)域）。同樣功能的VBA代碼需數(shù)十行。因此編程前應(yīng)盡可能多地了解Excel對

象的屬性、方法。充分利用Worksheet函數(shù)是提高程序運行速度的極度有效的方法。252021/2/21提高VBA運行效率For

Each

c

InWorkshe提高VBA運行效率而下面代碼程序比上面例子快得多：AverageValue=Application.WorksheetFunction.Average(Worksheets(1).Range(〃A1:A1000〃))其它函數(shù)如Count,

Counta,

Countif,

Match,

Lookup等等，都能代替相同功能的VBA程序代碼，提高程序的運行速度。

尤其在循環(huán)中每一個Excel對象的屬性、方法的調(diào)用都需要通過OLE接口的一個或多個調(diào)用，這些OLE調(diào)用都是需要時間的，減少使用對象引用能加快VBA代碼的運行。方法2：盡量減少使用對象引用262021/2/21提高VBA運行效率而下面代碼程序比上面例子快得多：Avera提高VBA運行效率例如

1．使用With語句。Workbooks(1).Sheets(1).Range(〃A1:A1000〃).Font.Name=〃Pay〃Workbooks(1).Sheets(1).Range(〃A1:A1000〃).Font.FontStyle=〃Bold〃

...則以下語句比上面的快With

Workbooks(1).Sheets(1).Range(〃A1:A1000〃).Font.Name

=

〃Pay〃.FontStyle

=

〃Bold〃...End

With272021/2/21提高VBA運行效率例如

1．使用With語句。Workboo提高VBA運行效率2．使用對象變量。如果你發(fā)現(xiàn)一個對象引用被多次使用，則你可以將此對象用Set

設(shè)置為對象變量，以減少對對象的訪問。如：Workbooks(1).Sheets(1).Range(〃A1〃).Value

=

100Workbooks(1).Sheets(1).Range(〃A2〃).Value

=

200則以下代碼比上面的要快：Set

MySheet

=

Workbooks(1).Sheets(1)MySheet.Range(〃A1〃).Value

=

100MySheet.Range(〃A2〃).Value

=

200282021/2/21提高VBA運行效率2．使用對象變量。如果你發(fā)現(xiàn)一個對象引用被提高VBA運行效率3．在循環(huán)中要盡量減少對象的訪問。For

k

=

1

To

1000

Sheets(〃Sheet1〃).SelectCells(k,1).Value

=

Cells(1,1).ValueNext

k則以下代碼比上面的要快：Set

TheValue

=

Cells(1,1).Value

Sheets(〃Sheet1〃).SelectFor

k

=

1

To

1000Cells(k,1).Value

=

TheValueNext

k292021/2/21提高VBA運行效率3．在循環(huán)中要盡量減少對象的訪問。For

提高VBA運行效率方法3：減少對象的激活和選擇如果你的通過錄制宏來學習VBA的，則你的VBA程序里一定充滿了對象的激活和選擇，例如Workbooks(XXX).Activate,Sheets

(XXX).Select,Range(XXX).Select等,但事實上大多數(shù)情況下這些操作不是必需的。例如：Sheets(〃Sheet3〃).Select

Range(〃A1〃).Value

=

100

Range(〃A2〃).Value

=

200可改為：With

Sheets(〃Sheet3〃)

.Range(〃A1〃).Value

=

100

.Range(〃A2〃).Value

=

200End

With302021/2/21提高VBA運行效率方法3：減少對象的激活和選擇如果你的通過錄提高VBA運行效率方法4：關(guān)閉屏幕更新如果你的VBA程序前面三條做得比較差，則關(guān)閉屏幕更新是提高VBA程序運行速度的最有效的方法，縮短運行時間2/3左右。關(guān)閉屏幕更新的方法：Application.ScreenUpdate

=

False請不要忘記VBA程序運行結(jié)束時再將該值設(shè)回來：Application.ScreenUpdate

=

True312021/2/21提高VBA運行效率方法4：關(guān)閉屏幕更新如果你的VBA程序前面用過

Excel

97

里的加載宏

"定時保存"

嗎？可惜它的源程序是加密的，現(xiàn)在就上傳一篇介紹實現(xiàn)它的文檔。

在

Office

里有個方法是

application.ontime，具體函數(shù)如下：

expression.OnTime(EarliestTime,

Procedure,

LatestTime,

Schedule)

如

果想進一步了解，請參閱

Excel的幫助。

這個函數(shù)是用來安排一個過程在將來的特定時間運行，（可為某個日期的指定時間，也可為指定的時間段之后）。通過這個函數(shù)我們就可以在

Excel

里編寫自己的定時程序了。下面就舉兩個例子來說明它。

1.在下午

17:00:00

的時候顯示一個對話框。

Sub

Run_it()

Application.OnTime

TimeValue("17:00:00"),

"Show_