The Visual Basic Editor

 

Apa itu Visual Basic Editor ? Visual Basic Editor atau disingkat dengan VBE adalah area tempat dimana anda menuliskan kode macro ( kode VBA ), cara mengakses VBE bisa melalui tab developper atau dengan menekan kombinasi tombol alt+f11, interface editor benar benar berbeda dan terpisah dengan lembar kerja microsoft excel dan setiap aplikasi office seperti worl, power point dan lain-lain mempunyai jendela editor ini
Saya akan mengulas sedikit dasar dasar dari jendela editor ini

Project window

This window will show you all the files that you have opened. The Project Window uses a tree view where you can drill down into each file that you have open and see the areas in which you can insert VBA code. Notice that in my screenshot under that there are two files that are open in my Excel application: Book1 (a workbook) and VBHTMLMaker (an add-in). In Book1 you can see 3 subfolders

3 sub folder itu adalah Microsoft object, module dan form. tiga sub folder ini mempunyai fungsi masing masing yang saling terkait satu dengan yang lain

Microsoft Objects -This folder houses a code area for your workbook (ThisWorkbook), and your workbooks spreasheets (Sheet1)

Forms - This folder stores any userforms that you create. I touched a little bit on this in Lesson One so I won't repeat myself here. If you do not see this folder you can add it by right clicking anywhere within the projects folder tree and going to Insert -> Form.

Modules - A Module folder stores you macro and function code. If you do not see this folder then that means that the project most likely does not have any macro code in it (note: there can still be code stored in the forms folder or in the Objects folder). You can add this folder by right clicking anywhere within the projects folder tree and going to Insert -> Module

Properties window

Properties window adalah jendela untuk mengcustom form dan module, salah satu hal yang paling penting adalah bagian field name This is a good idea because you can give your modules or forms a more meaningful name than the default names that the Visual Basic Editor provides. Custom names in the Name field can only be one word in length.

Code Writing Area

This is where the magic happens! In this area you can actually write and edit your VBA code. Each macro must begin with a Sub statement (which is opened with Sub [insert your macro name] ( ) and closed with End Sub). Notice also that the VBA Editor color-codes some key words in a few different colors. This helps make your code more organized. I have two major tips that I like to share with people when they are first learning to write VBA code:

Use Indentations - Always try to use indentations (via the Tab key) within your code. There are various methodologies to tabulating code but as long as you are consistent and it makes sense it will help you enormously when you are trying to add to or debug your code. It also helps when someone else is trying to help you with your code.
Write in Lowercase - If you haven't noticed already, every word in the VBA language has at least one capitalize letter. How is this an advantage? Well, the Visual Basic Editor is not case sensitive and it likes to correct you when it can. This means that is you type in "workbook", the editor will automatically change it to "Workbook". My rule of thumb is to type everything in lowercase and if the VB editor doesn't capitalize at least one letter, I know that I either misspelled that word or that word is not defined. Having the Visual Basic Editor correct every word I type has really made my code less buggy and prevent a lot of frustration over the years.

Immediate Window

I like to refer to this area as my piece of scratch paper. The Immediate window lets you do all sorts of tests while writing and running your code. You can use the code Debug.Print to tell VBA to send the information that follows to the Immediate window. This could be the output value of a function, the value of a cell, or what a current application property is set to. When I first began writing VBA code I had no idea the Immediate window ever existed (it's usually hidden by default but you can use the shortcut Ctrl + g to view it), but once learned everything it could do I never stopped incorporating it's functionality into my code writing and testing processes.

Watch Window

The Watch Window is kind of like an X-ray machine. It will show you all the data that is stored inside a variable! Some variables (like the ones you create in your code) will not have very much data stored in them. However, if you were to "watch" a variable that was assigned to a cell, you would see a whole bunch of data (font color, value, height, fill color, etc...). This is mostly usefully when you are trying to debug your code and want to understand what value your variable has at any given point in your code.
In order to watch a variable you need to highlight your variables text and click the Add Watch button (this is the eyeglasses icon located on the debugging toolbar). You should then see your variable appear in the Watch Window. Once you start running through your code and load a value to your variable, you should see an option (plus sign) to drill down or expand out the contents that is now stored in the variable.