Tech2 Touch

Category: Excel VBA

  • Password protecting Excel VBA macros

    We will be facing many circumstance where we need to password protect the excel VBA code we have written. Some times it may be for avoiding unintentional changes to the VBA codes or for hiding our codes from the others. Excel VBA provides a simple way to protect Excel VBA macro using password. This will not only protects from making changes also it protects the code from viewing. Lets start learning on how to password protect Excel VBA codes.

    Step to protect Excel VBA codes using password

    Below are steps to be followed to password protect the VBA codes.

    • First open VBA editor. Shortcut key for opening VBA editor is Alt+F11
    • Go to tools-> Select “VBA project Properties”
    • There new screen pops up. Select “Protection” Tab. You will get a screen similar to given below

    • Select the check box beside “Lock project for viewing” to hide the codes from viewing without password. If you do not want to hide the macro code and just want to protect your codes, then do not select the check box.
    • Enter the desired password in the “Password” labeled text box. Once again type the same password in the “confirmation password field “too.
    • You are done. Save the sheet and close the excel sheet.
    • Next time if you open VBA explorer you will prompted for password to view or modify the codes.

    This is method of password protecting of Excel VBA codes works on almost all current version of Excel.

  • Using Loops in Excel VBA

    Looping is used to repeatedly running a set of codes till it completes given condition. The loops are the fundamental component of any programming Language. In Excel VBA too there are many loop constructs. Let us have a close look into basic loop constructs.

    Basic Loop constructs of Excel VBA:

    • For…..next
    • Do……While
    • Do……Until
    • While……….Loop
    • For Each……Next
    For……Next Loop:

    This loop is used when there is a need to execute a statement or a block of statements for certain number of times.
    Example for For…..Next Loop:
    For i =1 to 10
         Total=Total+i
    Next i

    This code will calculate the sum of numbers from 1 to 10.  In this example statement ‘Total=Total+i’ repeats 10 times.  ‘i’ and ‘Total’ are the variable used in the code.
    In this example for For….Next loop following steps took place:
    This loop will work as long as the value of ‘i’ is more than 1 and less than 10.
    1. The For loop initializes the value of ‘i’ as 1. As 1 is less than, 10 the next statement executed.
    2. The code   ‘Total=Total+i’ is executed for first time.
    3. The code ‘Next’ increments the value of ‘i’ by 1
    4. The control shifts to the begining of the loop, where the value of the variable ‘i’ value is checked
    5. Till value of ‘i’ is less than 10 step 2 and 3  are executed.
    6. As soon as value of ‘i’ becomes more than 10  the loop terminates.