Hi, this is Randy with Excel for Freelancers, and in today's VBA basic training, we're going to go over VBA best practices so you can optimize and streamline your coding in order to create best applications you can in as little time with as little effort as possible and without those headaches.

大家好，我是 Excel for Freelancers 的蘭迪，在今天的 VBA 基礎培訓中，我們將介紹 VBA 的最佳實踐，這樣你就可以優化和簡化你的編碼，從而在儘可能短的時間內創建出最好的應用程序，而且不需要花費太多精力，也不會讓人頭疼。

It's going to be a great training, so let's get started.

這將是一次精彩的培訓，讓我們開始吧。

Just a note before, I do create these VBA basic trainings each and every Saturday, along with Tuesday complete application development, so make sure you do get subscribed and also turn on the notification icon.

請注意，我每週六都會舉辦這些 VBA 基礎培訓，週二還會舉辦完整的應用程序開發培訓，是以請確保您訂閱了這些培訓，並打開了通知圖標。

That'll ensure that you do get these trainings each and every week.

這將確保你每週都能得到這些培訓。

I create additional training on our Patreon platform.

我在我們的 Patreon 平臺上創建了額外的培訓。

Make sure you join us on Patreon, and I also have 300 of my best Excel templates that you can download with a link down below, so that'll also help you create applications.

請務必加入我們的 Patreon，我還有 300 個最好的 Excel 模板，你可以通過下面的鏈接下載，這也能幫助你創建應用程序。

All right, so today I want to go over VBA best practices.

好了，今天我要介紹的是 VBA 最佳實踐。

We're going to go over what's the best way to write code, how to comment in, what are the correct names to use, how to develop programming modularly so you can easily view, edit, and update your code, and also what techniques on how to optimize your code.

我們將介紹編寫代碼的最佳方法、如何註釋、正確使用的名稱、如何模塊化開發編程以便於查看、編輯和更新代碼，以及如何優化代碼的技巧。

So let's get into it right away.

那麼，讓我們馬上開始吧。

First thing we want to do is make sure that we get inside the actual VBA editor, and to do that, we're going to go into the developer and the visual basic here, and then what we're going to do, if we don't have the developer, of course, you can just simply right-click here, and then we're going to customize the ribbon, and then just make sure developer selected.

我們要做的第一件事就是確保我們進入實際的 VBA 編輯器，為此，我們要進入開發人員和 visual basic，然後我們要做的是，如果我們沒有開發人員，當然，你可以只是簡單地在這裡單擊右鍵，然後我們要自定義功能區，然後確保開發人員被選中。

You can also use a shortcut key, Alt F11.

您還可以使用快捷鍵 Alt F11。

Once you're in the VBA, you will see something like this, and this is the VBA editor, and inside this VBA editor, we have our sheets.

進入 VBA 後，你會看到類似下面這樣的內容，這就是 VBA 編輯器，在這個 VBA 編輯器中，有我們的工作表。

We only have one sheet right now, and we don't have any modules.

我們現在只有一張紙，沒有任何模塊。

So when we're writing our code, most of the time we're going to be writing our code inside a module.

是以，當我們編寫代碼時，大多數情況下都是在模塊內編寫代碼。

So the first thing we want to do is to make sure that we do add a module, and so we're going to right-click anywhere, and we're going to insert module.

是以，我們要做的第一件事就是確保我們添加了一個模塊，所以我們要右擊任何地方，然後插入模塊。

You can also, from here, insert the module, and the first thing you want to do, as far as best practices are concerned, is naming that module.

你還可以在這裡插入模塊，就最佳實踐而言，你要做的第一件事就是為模塊命名。

When I first started developing, I didn't know how to name these modules.

剛開始開發時，我不知道如何給這些模塊命名。

I would have modules one, two, three, so if we insert another one here, and so now we have module two, and I didn't know what was inside each.

我會有模塊一、二、三，所以如果我們在這裡再插入一個，那麼現在我們就有了模塊二，我不知道每個模塊裡面都有什麼。

I would have to click on each just to know, so the first thing you want to do, especially if you're going to be developing a larger application with a lot of modules, is to name your module, and to do that, you can go into the properties here, or click F4, and it's going to launch this properties.

是以，首先要做的是為模塊命名，尤其是在開發包含大量模塊的大型應用程序時，可以進入屬性，或者點擊 F4，就會啟動屬性。

Now this window may be snapped here, it may be down here, it may be somewhere, depending upon your screen, but what you want to do is you want to give it a name.

現在，這個窗口可能在這裡，可能在下面，也可能在某個地方，這取決於你的螢幕，但你要做的是給它取個名字。

Whatever the application you're developing is, let's say a customer application, we might want to do customer macros, right?

不管你開發的是什麼應用程序，比方說客戶應用程序，我們可能都想做客戶宏，對嗎？

So we have different macros, maybe we have a scheduling macro, and then so the first thing you want to do is name your modules, and that's going to make things a lot easier, so let's just do something like scheduling macros.

所以我們有不同的宏，也許我們有一個調度宏，然後你要做的第一件事就是給你的模塊命名，這會讓事情變得簡單很多，所以我們就做調度宏這樣的事情吧。

Now keep in mind that when we do naming, we can't have any spaces, so if we try to add a module name with space, it won't allow us, so we want to make sure we're going to use underscores for spaces, so something like this, and you can abbreviate as long as it's clear to you when you go in exactly what the names are and what the consistencies.

是以，我們要確保使用下劃線來代替空格，所以類似這樣的內容，你可以縮寫，只要你在輸入時清楚名稱和一致性就可以了。

Now while we're on naming, let's say we're in the customer macros, we're inside this module here, we want to also name our subroutines clear and easily, we want to use consistency throughout.

在命名的時候，假設我們在客戶宏中，我們在這個模塊中，我們也想清晰、簡單地命名我們的子程序，我們想在整個過程中使用一致性。

If you're developing a large application, those names are going to come in handy.

如果你正在開發一個大型應用程序，這些名稱就會派上用場。

When we need to then assign those names to different buttons and associates, we want to be able to quickly and easily remember them.

當我們需要將這些名稱分配給不同的按鈕和同事時，我們希望能夠快速、輕鬆地記住它們。

Also when you're naming, we want to make sure that we use a combination of capital letters and smaller letters, so let's say I'll do customer, something like add new, and then maybe we'll do customer, and then we'll do save or update.

此外，在命名時，我們要確保使用大寫字母和小寫字母的組合，比方說，我將使用 "客戶"，類似於 "添加新內容"，然後也許我們將使用 "客戶"，然後我們將使用 "保存 "或 "更新"。

Now one of the things that I like, let's do the sub first, now when I want to add this new customer somewhere else, and if I'm in another module here, and I write sub schedule, and then add new, so maybe I want to add that new customer, and a good way if I want to remember it, I don't want to necessarily look over, using the combination of lowercase and uppercase is going to be really helpful, because it helps us let us know when we have the right one.

現在，我喜歡的一件事是，讓我們先做子模塊，當我想在其他地方添加這個新客戶時，如果我在這裡的另一個模塊中，我寫了子計劃，然後添加新的，所以也許我想添加那個新客戶，如果我想記住它，我不想一定要看一遍，使用小寫和大寫的組合將是非常有用的，因為它可以幫助我們讓我們知道，當我們有正確的。

So if I type in add new, you see how it automatically goes to the capital letters, the proper name here.

是以，如果我輸入添加新內容，你可以看到它是如何自動轉到大寫字母的，也就是這裡的正式名稱。

However, if I get anything wrong inside the name, something like this, and I go over, look how it doesn't change, that's going to tell me right there, I've got something wrong with the name, so it kind of helps us, we don't always have to look back, so that tells me right there, because it didn't go to capital letters, I know that I might have an issue with the macro, so that kind of avoids errors, and makes things really clear.

不過，如果我在名稱中發現了什麼問題，比如這樣的問題，我再看一遍，看看它是如何保持不變的，這就會告訴我，我的名稱出了問題，所以它可以幫助我們，我們不必總是回頭看，這樣就可以告訴我，因為它沒有變成大寫字母，我就知道我的宏可能出了問題，這樣就可以避免錯誤，讓事情變得非常清楚。

So when we actually create our macro names, we want to use a combination of upper and lowercase, and that way when we write them out, and we're on another module, we don't quite remember, we type in all lowercase, and we make sure that it goes to the proper.

是以，當我們創建宏名時，我們希望使用大寫和小寫的組合，這樣當我們寫出宏名時，如果我們在另一個模塊上，我們不太記得，我們就可以輸入全小寫的宏名，這樣就能確保宏名是正確的。

Now along the lines of naming, we've got naming the modules, we've got naming the macros, we also want to name our variables.

沿著命名的思路，我們已經命名了模塊，命名了宏，我們還想命名我們的變量。

I see a lot when people program something like this, let's say we're going to do dimension

我看到很多人在編程時都會這樣做，比如說我們要做維度計算

I as long, X as long, and then we'll do dimension C as string.

I 為長，X 為長，然後我們再把維度 C 做成字符串。

For me, that doesn't really help me, I don't remember, maybe it's my memory, but when I get back into this application, I'm not going to remember what I is, or X is.

對我來說，這對我沒有什麼幫助，我不記得了，也許是我的記憶力問題，但當我重新回到這個應用程序時，我不會記得我是什麼，或者 X 是什麼。

And then we'll do something like for I equals 1 to 10, next I.

然後我們會做一些類似於 I 等於 1 到 10 的操作，下一個 I。

So here we see a loop, but I don't understand what I is, what is it?

是以，我們在這裡看到了一個循環，但我不明白我是什麼，它是什麼？

Is it a row?

是一排嗎？

Is it a number?

是一個數字嗎？

Is it a customer?

是顧客嗎？

I don't understand.

我不明白。

So we want to make sure that when we're naming our variables, it's very, very clear to us and everybody else.

是以，我們要確保在命名變量時，我們和其他人都非常清楚。

Even if you're not teaching like I am, you want to make sure that when you come back to the application, we understand exactly the purpose of every variable.

即使你不像我一樣教書，你也要確保當你回到應用程序時，我們能準確理解每個變量的目的。

And that's why naming variables is so critical.

這就是變量命名如此重要的原因。

I would do something like this, customer row, again, using the combination of capital and lowercase letters is going to really help us.

我會這樣做，客戶行，再次強調，使用大寫字母和小寫字母的組合會對我們很有幫助。

Customer row as long, and we'll do customer column as long.

客戶行一樣長，客戶列一樣長。

And then we can do something like customer name as string.

然後，我們就可以將客戶名稱轉換為字符串。

So it's very clear the purpose of our variables simply by looking at the variables.

是以，只要看一下變量，我們就能非常清楚地知道變量的目的。

And I think that's a really critical component because when we're looking at our code or somebody else is looking at our code, or even if we're looking at our own code, a few years later, we want it to be very, very easy.

我認為這是一個非常關鍵的組成部分，因為當我們查看自己的代碼，或者別人查看我們的代碼，甚至幾年後我們查看自己的代碼時，我們都希望它非常非常簡單。

So once again, we can also use the variable.

是以，我們還可以再次使用變量。

If I say for customer row equals 1 to 10, next customer row, again, see, I'm typing in small letters.

如果我說客戶行等於 1 到 10，那麼下一個客戶行就等於 1 到 10。

If it then goes to the upper, I know that I've got the right variable.

如果它進入上層，我就知道我找到了正確的變量。

So it's a great way.

所以這是一個很好的方法。

So if I get it wrong, it's going to tell me right away that it hasn't changed the uppercase here.

是以，如果我弄錯了，它會立即告訴我這裡的大寫字母沒有改變。

So I know that there's something wrong with the variable.

所以我知道變量出了問題。

So not only using correct names, but using a combination of upper and lowercase to help us quickly understand that means as we're typing the code, we know that we've got an incorrect variable and that happens all the time.

是以，不僅要使用正確的名稱，還要使用大小寫字母的組合來幫助我們快速理解，這意味著當我們鍵入代碼時，我們知道我們得到了一個不正確的變量，這種情況經常發生。

So we want to use not only naming the correct names and clear names, but using a combination of upper and lowercase or using some type of a proper name so that we can quickly see when we type incorrect variables.

是以，我們不僅要使用正確的名稱和清晰的名稱，還要使用大小寫字母的組合，或者使用某種類型的專有名稱，這樣當我們輸入不正確的變量時，就能很快看出來。

And that happens to me a lot, even during while I'm recording, I'll type an incorrect variable.

這種情況經常發生在我身上，甚至在錄音時，我也會輸入一個錯誤的變量。

As soon as I moved to the lower line, I see that didn't move to uppercase.

當我移動到下一行時，我發現它並沒有移動到大寫。

So I know right away something is wrong with my variable.

所以我馬上就知道我的變量出了問題。

So that's a great way to understand clearly how to properly name variables.

是以，這是清楚瞭解如何正確命名變量的好方法。

We want to make sure that we're actually naming that properly.

我們要確保我們的命名是正確的。

Now, whatever way you use, it's more of a personal effort.

現在，無論你用什麼方式，這都更像是一種個人努力。

I've seen people use things like a lowercase long as variable.

我見過有人用小寫的 long 作為變量。

So however you want to remember your variables is fine, but as long as it's very clear to you, and that when you return back to your code, you quickly, very quickly understand the purpose of those variables and how they fit within your macro and how those macros fit within your application.

是以，無論你想以何種方式記住變量都沒問題，只要你能清楚地記住變量，而且當你返回代碼時，你能很快、很快地理解這些變量的用途，以及它們如何與你的宏匹配，這些宏如何與你的應用程序匹配。

So notice here, I've got customer row and customer column as long variables and customer name.

請注意，我在這裡將客戶行和客戶列設置為長變量，並設置了客戶名稱。

So everything is within a customer add new or customer saver update macro, which is within a module called customer macros.

是以，一切都在客戶添加新內容或客戶保存更新宏中進行，而這些宏都在名為客戶宏的模塊中。

So everything is modular and everything fits together properly.

是以，一切都是模塊化的，所有東西都能很好地組合在一起。

It makes reading your code and understanding your code a lot easier.

這樣閱讀代碼和理解代碼就容易多了。

So naming is a critical component.

是以，命名是至關重要的一環。

We also use named ranges inside our code, and that can be helpful too.

我們還在代碼中使用命名範圍，這也很有幫助。

Another thing we want to make sure of, and let's just say, for example, we have application where we've got, let's say a customer list here and we go into the formulas name manager.

我們要確保的另一件事是，比方說，我們的應用程序中有一個客戶列表，然後我們進入公式名稱管理器。

And let's say I have a, let's go ahead and cancel this.

比方說，我有一個，讓我們取消這個。

I'm just going to create a new sheet real quickly.

我要快速創建一個新工作表。

And let's say we have a list of customer names here and I'll just put in customer names.

比方說，我們這裡有一個客戶姓名列表，我將直接輸入客戶姓名。

Fred will make his appearance and Lisa, and let's see Mary.

弗雷德會出場，還有麗莎，讓我們看看瑪麗。

So I've got a list of customer names.

我有一份客戶名單。

Now what we want to make sure of also is our named ranges that we possibly will use inside the code should also be named appropriately.

現在我們要確保的是，我們可能會在代碼中使用的命名範圍也應該命名得當。

So for example, if I create a brand new one, so let's say customer, and then we'll do names.

舉例來說，如果我創建一個全新的名稱，比方說客戶，然後我們再創建名稱。

Once again, we may have different variables under customer, let's say customer names or customer balances or customer address, different name range.

同樣，我們可能在客戶下有不同的變量，比方說客戶名稱、客戶餘額或客戶地址，不同的名稱範圍。

So we want to use that same modular type of programming, and then we can write a named range.

是以，我們要使用同樣的模塊化編程方式，然後就可以編寫命名範圍了。

So it's something like offset.

所以是類似偏移量的東西。

And then we'll start at the header row.

然後我們從標題行開始。

We'll move one row down.

我們向下移動一行。

So that's just in case we don't have any data, no columns over, we're going to use count a.

是以，萬一我們沒有任何數據，沒有列，我們將使用計數 a。

And then what we're going to do is again, using the header row and a large row, we're using the header row in case there's no data.

然後，我們要做的就是再次使用標題行和大行，我們使用標題行是為了防止沒有數據。

We want to make sure.

我們要確保

I need to subtract one for the header row, a single column, and we're going to use the tab key out.

我需要為標題行減去一列，再減去一列，然後使用製表符鍵退出。

And we're going to use the shift tab key.

我們將使用 shift tab 鍵。

We're going to make sure that it's dancing outs around those.

我們將確保它能在這些周圍跳舞。

So now I've got a name called customer names.

所以，現在我有了一個名字，叫做 "顧客姓名"。

If I edit that, I can copy it using control C. Now, if I want to refer to that inside the code for any reason, I can do something like this.

現在，如果我想以任何理由在代碼中引用它，我可以這樣做。

We can dimension customer range, let's do name range as a range.

我們可以將客戶範圍作為維度，將名稱範圍作為一個範圍。

And then we can also set the customer name range equal to, and then we can use brackets for that named range and we can paste it in there, which is customer names.

然後，我們還可以將客戶名稱範圍設置為等於，然後使用括號將命名範圍粘貼進去，這就是客戶名稱。

So here we've got a named range.

是以，我們有了一個命名範圍。

Now what we do want to make sure of is that we don't match the name.

現在我們要確保的是，我們不會匹配名稱。

In other words, our named range called customer names should not match exactly any variable referring to that.

換句話說，我們名為客戶名稱的命名範圍不應與任何引用該名稱的變量完全匹配。

We want to keep them very, very different and very separate.

我們想讓它們保持非常、非常不同和非常獨立的狀態。

So here, what we've done is we've again created a dynamic named range and we're referring it to within the code.

在這裡，我們再次創建了一個動態命名範圍，並在代碼中引用它。

So that can be extremely helpful when we have clear names for one, our variables, clear names for our macros, clear names for our named ranges and clear names for our modules.

是以，當我們的變量、宏、命名範圍和模塊都有明確的名稱時，這將非常有用。

So naming is a critical component of any VBA application.

是以，命名是任何 VBA 應用程序的關鍵組成部分。

Next up, when we're writing code, we want to actually write it in a way that's readable for us and other people that might be looking at our code.

接下來，當我們編寫代碼時，我們要真正做到讓我們自己和其他可能會查看我們代碼的人都能讀懂。

And we do that with proper spacing and indentation.

而且，我們要做到適當的間距和縮進。

So the best way to do that is when we're using for next loops or we're using any kind of a loop or if then statements, we want to make sure to indent properly.

是以，最好的辦法是，當我們使用 for next 循環或使用任何類型的循環或 if then 語句時，確保正確縮進。

Notice that this is going to make it clear that anything we write in here is inside that for next loop.

注意，這將清楚地表明，我們在這裡寫的任何內容都在 for next 循環中。

So no matter what, let's just do customer row.

所以，無論如何，我們都要做好客戶排查工作。

We can do something like sheet one dot range a and customer row dot value equals customer row.

我們可以這樣做：工作表 1 點範圍 a 和客戶行點值等於客戶行。

So in this code, we see that everything we write was within that.

是以，在這段代碼中，我們可以看到我們所寫的一切都在其中。

Now, if we do an if then statement, if customer row is less than five, then so notice now, once we're in the if then statement, we also want to indent everything within there.

現在，如果我們做一個 if then 語句，如果客戶行小於 5，那麼請注意，一旦我們進入 if then 語句，我們還要縮進其中的所有內容。

So that is clearly stated.

所以這一點說得很清楚。

Let me go ahead and show you a little bit larger project that I did so we can see how that would be on a larger project.

讓我先給你們看一個我做過的稍大一點的項目，這樣我們就能知道在更大的項目中會是怎樣。

So I'm going to open up one of my recent projects.

所以，我要打開我最近的一個項目。

Let's just do this one, this ERP software here.

就用這個吧，這個企業資源規劃軟件。

So this is a pretty good one.

是以，這是一個相當不錯的選擇。

And we can see some of the way that we do the code inside these larger applications.

我們可以看到這些大型應用程序中的一些代碼編寫方式。

Once this code opens up, now we see here inside this large code, we have lots of different sheets and those sheets also must be named too.

打開代碼後，我們可以看到在這一大段代碼中，有許多不同的工作表，這些工作表也必須命名。

And inside these individual codes, let's go ahead and pull in one of those codes here.

在這些單獨的代碼中，讓我們在這裡輸入其中一個代碼。

We can take a look.

我們可以去看看。

So we've also got a list of modules and we notice that each individual module, customer macros, employee macros, logging out, everything is clearly labeled inside the customer macros.

是以，我們還得到了一個模塊列表，我們注意到每個模塊、客戶宏、員工宏、註銷，所有內容都在客戶宏中清晰標註。

We can see that inside a macro, we also have proper indentation.

我們可以看到，在宏內部，我們也有適當的縮進。

Here we have inside the width, we've moved it over.

在這裡，我們把它移到寬度內側。

We have inside our customer width, we have a width statement.

我們在客戶寬度內有一個寬度聲明。

Inside there, we have a for next.

在那裡，我們有一個下一個。

So everything has been properly indentated.

是以，所有內容都已正確縮進。

We also have, again, clear macro names.

同樣，我們也有明確的宏觀名稱。

And another thing that I wanted to share with you is the ability to have sheet code names.

另外，我還想和大家分享一個功能，那就是設置工作表代號。

We'll take a look inside here and we see not only do we have the sheet names such as admin chart data customer, we also have a sheet code name.

我們來看看裡面，不僅有工作表名稱，如管理圖表數據客戶，還有工作表代碼名稱。

And that's a very, very helpful to when we're referring.

這對我們的參考非常非常有幫助。

Let's go back inside our original sample, which is right here.

讓我們回到原始樣本中，就在這裡。

Now maybe we want sheet one as a customer sheet.

現在，也許我們想把第一頁作為客戶頁。

We also want to make sure that inside that application, we clearly label the sheet name so we can do it from here.

我們還要確保在該應用程序中明確標註工作表名稱，這樣我們就可以在這裡進行操作了。

Customer if it's customers or whatever it is, customers, or maybe we'll just do customer form, make it a little clearer.

如果是客戶或其他什麼，客戶，或者我們就用客戶表單，這樣更清楚一些。

So the more clear now sheet names, we can use characters such as spacing and so on and so forth.

是以，現在工作表的名稱越發清晰，我們可以使用間距等字元等等。

So that will help us identify the specific sheet.

是以，這將有助於我們確定具體的床單。