that you can use to modify the data

你可以用它來修改數據

that's stored in your data tables.

儲存在你的數據表中。

Occasionally however, these won't be enough

然而，偶爾，這些將是不夠的。

to calculate exactly what you need.

來準確計算你的需求。

Or perhaps you want to save a complex calculation

或者，也許你想保存一個複雜的計算

to use over and over again.

來反覆使用。

In these cases, you'll want to create

在這些情況下，你會想要創建

your own custom functions in Visual Basic.

你自己在Visual Basic中的自定義函數。

We can start that process us by coming up to the Create tab

我們可以通過訪問 "創建 "選項卡來開始這一過程。

and coming over to the Macros and Code section

然後來到宏和代碼部分

on the far right and clicking on Module.

在最右邊，點擊模塊。

That'll open up a brand new program

這將開闢一個全新的項目

called Microsoft Visual Basic for Applications.

稱為Microsoft Visual Basic for Applications。

And this is where we get the acronym, VBA.

這就是我們得到的縮寫，VBA。

I want to make sure that we have three different windows open

我想確保我們有三個不同的窗口打開

here on our screen.

在我們的螢幕上。

We've got the Project Explorer, up here on the upper left,

我們有一個項目瀏覽器，在左上方。

the Properties Window on the lower left

在左下角的屬性窗口中

and the Immediate Window across the bottom.

和橫跨底部的即時窗口。

If you're missing any of these windows,

如果你缺少這些窗口中的任何一個。

just come up here to View and you can toggle them on

只要到這裡來查看，你就可以把它們切換成

with the Immediate Window option, Project Explorer

使用即時窗口選項，項目瀏覽器

or Properties Window.

或屬性窗口。

In the Properties Window,

在屬性窗口中。

I'm going to change the name of our module

我將改變我們模塊的名稱

from the default name to Myfunctions.

從默認名稱到Myfunctions。

You'll see that change is made up here

你會看到這個變化是由這裡組成的

in the Project Explorer and in the title bar of the window

在項目資源管理器中和窗口的標題欄中

that's currently open here.

這是目前在這裡開放的。

In this window, is where we're going to type in

在這個窗口中，我們要輸入的是

the different functions that we want to create.

我們想要創建的不同功能。

I'll come down to the line below, Option Compare Database

我下來看看下面這行，選項比較數據庫

and we'll type in the keywords, Public Function.

然後我們輸入關鍵詞，Public Function。

Public means that this function is going to be available

公開的意思是，這個功能將是可用的

outside of the code module.

在代碼模塊之外。

So, we can use it in queries and forms, for example.

是以，我們可以在查詢和表格中使用它，比如說。

The function that I want to create is going to calculate an age

我想創建的函數將計算出一個年齡

when we give it a date of birth

當我們給它一個出生日期

I'll name it simply, Age and open a parenthesis.

我把它簡單地命名為 "年齡"，並打開一個括號。

Inside of the parentheses,

在括號內。

we're going to type in the different arguments

我們將輸入不同的參數

or the data that we're going to pass in to the calculation.

或我們要傳遞到計算中的數據。

So, in order to calculate an age, we need a date of birth.

是以，為了計算年齡，我們需要一個出生日期。

I'll just call it DoB for short.

我就簡稱它為DoB吧。

Then, Access needs to know what type of data to expect.

然後，Access需要知道期待什麼類型的數據。

Now, obviously, date of birth is going to be a date data type,

現在，顯然，出生日期將是一個日期數據類型。

so I'll type in as Date.

所以我就打成了Date。

We'll finish the Public Function declaration

我們將完成公共函數的聲明

with a closing parenthesis and press enter

帶結尾的括號，然後按回車鍵

to come down to the next line.

下到下一行。

When I do so, Visual Basic adds in

當我這樣做時，Visual Basic會在

the End Function line down below.

下面的 "結束功能 "一行。

In between these two lines,

在這兩條線之間。

is where we're going to type in the different steps,

是我們要輸入不同步驟的地方。

to calculate an age given a date of birth.

來計算一個給定的出生日期的年齡。

We'll start that process by typing in Age equals.

我們將通過輸入年齡等號開始這一過程。

Now, there's lots of different ways to calculate an age

現在，有很多不同的方法來計算年齡

given a date of birth.

給出一個出生日期。

One way is to take today's date

一種方法是把今天的日期

and subtract the date of birth

並減去出生日期

and that'll give us the number of days that have elapsed

這將給我們提供已經過去的天數

between the two.

兩者之間的關係。

In order to get today's date,

為了得到今天的日期。

I'll use one of Access's built-in functions,

我將使用Access的一個內置函數。

that's simply called, Date.

那就是簡單地稱為 "日期"。

That'll give us today's date and we'll simply subtract DoB.

這將給我們今天的日期，我們將簡單地減去DoB。

This is the date that we pass in, when we run this function.

這是我們在運行這個函數時傳入的日期。

Now, I want to make sure that this calculation happens first,

現在，我想確保這個計算首先發生。

so, I'm going to wrap that in parentheses.

所以，我打算用括號把它包起來。

Then we'll come to the end

然後我們會走到盡頭

and I'll divide the whole thing by 365.25.

而我要把整個事情除以365.25。

This is the average number of days in a year.

這是一年中的平均天數。

At this point, the function is going to return

在這一點上，該函數將返回

a very precise fraction of a year

一個非常精確的一年的零頭

and I actually want to just drop off the remainder

而我實際上想把剩餘的東西扔掉。

and return the whole number of years.

並返回整數的年數。

So, we're going to wrap this whole thing here

所以，我們要在這裡把這整個事情包起來

in another function.

在另一個函數中。

I'll type a parenthesis at the very end,

我在最後打一個括號。

I'll come back to the beginning,

我再來談談開頭。

I'll type in another parenthesis

我再打一個括號

and this function is going to be called Int.

而這個函數將被稱為Int。

This will essentially just return the whole number of years

這實質上只是返回整個年數

that have elapsed without any extra days.

在沒有任何額外天數的情況下，已經過去了。

So, there's our entire calculation.

所以，這就是我們的整個計算過程。

Let's come down here to the immediate window

讓我們到這裡來看看眼前的窗口

and we'll test it out.

我們將對其進行測試。

We'll do that by typing in a question mark,

我們將通過輸入一個問號來做到這一點。

the name of the function, Age,

該函數的名稱，Age。

I'll open a parenthesis and we'll give it a date.

我將打開一個括號，我們將給它一個日期。

Remember in Access, we use the date delimiters

記住在Access中，我們使用日期分隔符

of the pound symbols around our dates.

我們的日期周圍的英鎊符號。

So, I'll type it a pound and then 10 slash 28 slash 1955.

是以，我將輸入一磅，然後是10個斜線28個斜線1955。

We'll finish it with another pound symbol

我們將用另一個英鎊的符號來完成它

and a closing parenthesis.

和一個結尾的括號。

This is the birth date for Microsoft co-founder, Bill Gates.

這是微軟聯合創始人比爾-蓋茨的出生日期。

And when I press enter,

而當我按下回車鍵時。

we'll see that at the time of the recording,

我們將在錄音時看到。

that he is 62 years old.

他已經62歲了。

So, we can see our age function is working,

是以，我們可以看到我們的年齡功能正在發揮作用。

let's go ahead and create another public function.

讓我們繼續前進，創建另一個公共函數。

I'll come back up here into my window,

我回到這裡進入我的窗口。

I'll press enter to come down to another empty line

我按下回車鍵，來到了另一個空行

and we'll start the process again with Public Function.

我們將從 "公共職能 "重新開始這個過程。

This function is going to take in two arguments,

這個函數將接收兩個參數。

the first name and the last name of a person

名和姓的人

and it's going to return a formatted string

它將會返回一個格式化的字符串

where we have last name, comma first initial.

其中，我們有姓氏，逗號第一個字母。

I'll name this function, FormattedName, open a parenthesis

我將這個函數命名為FormattedName，打開一個括號

and the first argument is firstName

而第一個參數是firstName

and we'll be passing that in as a string character type,

而我們將把它作為一個字符串字元類型傳入。

I'll type in a comma

我打上一個逗號

and I can type in the second argument, which is lastName

我可以鍵入第二個參數，即lastName

and that'll also be a string data type.

而這也將是一個字符串數據類型。

I'll type in a closing parenthesis

我打上一個結尾的括號

and this time we want to specify

而這一次我們要指定

that we want this returned as a string.

我們希望將其作為一個字符串返回。

So, I'll type in as string one more time.

所以，我再一次以字符串的形式輸入。

We'll come down to the next line

我們來看看下一行

and type in the calculation here,

並在此鍵入計算結果。

so, formatted name is equal to

所以，格式化的名稱等於

and it's simply going to return just the last name

它將簡單地只返回最後一個名字

and then we'll concatenate that

然後我們將串聯這個

or join that to the text string,

或將其加入到文本字符串中。

so, I'll type in an ampersand, a double quote,

所以，我將輸入一個安培號，一個雙引號。

we're going to join that to a comma and then a space,

我們要把它連接到一個逗號，然後再加上一個空格。

I'll type in another double quote and another ampersand.

我再打一個雙引號，再打一個安培號。

We're going to join that string to a Left function,

我們要把這個字符串連接到一個Left函數。

open a parenthesis.

打開一個小括號。

The Left function will process the first name

左邊的函數將處理第一個名字

and I'll type in a comma and a one.

然後我打上一個逗號和一個一。

We'll finish that with a closing parenthesis,

我們將用一個結尾的括號來結束這一切。

another ampersand, a double quote, a period

另一個安培符號，一個雙引號，一個句號

and a double quote.

和一個雙引號。

Essentially what we're doing here, is building a string

從本質上講，我們在這裡所做的是建立一個字符串

that'll take the full last name,

這將需要完整的姓氏。

join that to a comma and a space,

連接到一個逗號和一個空格。

join that to the first initial of the first name

連接到第一個名字的首字母

and join that to a period.

並將其與句號相連。

Let's come down to the immediate window again

讓我們再來看看眼前的窗口

and I'll click my mouse to insert the cursor

而我將點擊我的滑鼠來插入遊標

and we'll type in, FormattedName, open a parenthesis.

我們將輸入，FormattedName，打開一個括號。

This prompts me for the first name as a string

這提示我把名字變成一個字符串

and remember, we need to type that in double quotes,

並記住，我們需要用雙引號輸入。

I'll type in "Bill", I'll type in a comma

我將輸入 "比爾"，我將輸入一個逗號

and then the last name as a string again

然後再將姓氏作為一個字符串

in double quotes, "Gates".

在雙引號中，"蓋茨"。

I'll type in the closing double quote

我在結尾處打上雙引號

and a closing parenthesis and press enter.

和一個封閉的小括號，然後按回車鍵。

And I'm getting a compile error here,

而我在這裡得到了一個編譯錯誤。

that's actually because I forgot the question mark

這其實是因為我忘了問號。

at the very beginning.

在最開始的時候。

So, go ahead and type that question mark in, run it again

所以，繼續輸入那個問號，再運行一次

and it returns it as expected, Gates comma B.

並按預期返回，蓋茨的逗號B。

Let's go ahead and save our code module now,

讓我們繼續前進，現在保存我們的代碼模塊。

I'll press the disc icon here on the toolbar

我將按下工具條上的光盤圖標

and it's going to prompt me to save the module name Myfunction,

它將提示我保存模塊名稱Myfunction。

then I'll just simply say, okay.

那麼我就簡單地說，好吧。

So, that's a quick introduction

是以，這是一個快速的介紹

on how you can begin to create custom functions

關於你如何開始創建自定義函數

in Visual Basic.

在Visual Basic中。

(upbeat music)

(歡快的音樂)