Subs in Macros

Macro codes need to begin with Subs and ends with End Subs.

Example:



Every macro must have a name (of course!). The name of the macro will be in the beginning Sub. The format is like following:

Sub <Macro Name>()

Example:

Sub Macro_Satu()
Sub Report()
Sub RunAll()
Sub Primary()

In the screenshot above, the macro name is Macro_1. You can put in what ever name you wish. A name is required else the macro will not run.

When you create any Subs, it will also appear in the Macro prompt.



Please note that the macro name cannot have any space between them. The name must be one single word. If it is multiple words, you would need to use underscore _ .

Example:

Cannot	Can
Macro Satu	Macro_Satu
Run This Report	Run_This_Report
Report 1	Report_1

In any workbook, there can be multiple macros. Each must have a unique name or the computer get's confused on which macro to run.



This example shows two macros in the workbook which are Macro_1 and Macro_2. In the Macro prompt, it would appear like below:



The Macro prompt will enable Users to select which macro to run. Hence it is advisable to always create names that Users can understand what the macro is for. Macros can of course be triggered from a button or a shape that you put into the sheet, but that will be covered in another post.

Excel and Macro: VLOOKUP (simple)

The formula for VLOOKUP is to search for a value in a cell in the first column and return the value of another cell that is in the same row but different column.

Simple example is like in table below:



We want to know what is Andy's favorite fruit. From the table, immediately we can find that the answer is Orange. The cell that hold's the answer is in the same row but in different column. The function of VLOOKUP is to find information in such a manner.

Formula of VLOOKUP:

=VLOOKUP(lookup_value, table array, col_index_num, [range_lookup])

In simplified form:

=VLOOKUP( A , B , C , D )

A - the value you want to search for. In the example above, we want to find Andy.

B - the table you are looking in to find Andi. In the example above, we select the whole table.

C - the column number (from the left) of where the value you want returned is located. In the example above, we are looking for Favorite Fruit, hence the 2nd column from the left.

D - this is to provide exact or approximate value...in most instance people just put the number zero for this one.

Using the above example, the input in the formula would look like the following in Excel:

=VLOOKUP( "Andi", A1:C6 , 2 , 0 )

This will return you the value "Orange".

Note that A can be cell number or text.

In the above example, if we use cell number, it will be A2. This is because Andy's name is in cell A2.



If we use text, we must type the text and enclose it in double quotes "Andi".



This is perhaps an the simplest way to explain how VLOOKUP works. Hope it is helpful for beginners. In later posts, we will show how to use VLOOKUP in more advanced manner.