Excel-Macro-VBA

Excel-Macro-VBA

Excel-VBA : Open a Excel File using Another Excel File using Browse Option.

by

This tutorial will teach you how to Open a Excel File using Another Excel File using Browse Option. We will be using Application.GetOpenFile() function to accomplish this.

NOTE: You can define any type file you want to , just set the proper filter.

FileFilter:=”Excel Files *.xls* (*.xls*) – to open Excel files

FileFilter:=”Excel Files *.txt* (*.txt*) – To open Text File

NOTE :Do not provide any filter if you want to select any type of file as attachment

Complete Code:

Read moreExcel-VBA : Open a Excel File using Another Excel File using Browse Option.

Excel-VBA : Insert Multiple Images from a Folder to Excel Cells

by

Say you have many images in a folder and you want to insert all these images in your excel work book, one image in one cell. You can do it manually, insert and resize the image and drag it to the particular cell, but think of a scenario where you have more than 100 images or may be more. I am sure you don’t want to do it manually. IF you are looking for solutions for problems like this, this is the tutorial for you.

This tutorial will teach you about how to insert multiple images from a folder into your excel.

Steps:

Excel-VBA : Range – Lock/Unlock Specific Cells

by

There are two different cases you might deal with,

  • Lock only few specific cells and rest of the cells in the work sheet are not locked(means you can edit them)
  • Lock the Enitre worksheet except few cells

Lock only few specific cells and rest of the cells in the work sheet are not locked

Steps:

  • Lock the selected cells
  • Provide the password

Note: If you wont provide the password then your cells will not be locked.

Read moreExcel-VBA : Range – Lock/Unlock Specific Cells

Excel-VBA : Change Passwords for all the WorkSheets in one shot

by

Objective : Suppose you have many worksheets in an excel and you want to change all the passwords in one shot rather doing it manually for every sheet, which might be very bad way to do specially when you have a power of doing it using VBA.

NOTE: you need a place to store your password, most of the timewe store it in our mind but computer doesn’t have mind, it has memory :), but the point is where, it could be a file, DB . Why not use your excel for that.

Store it in a separate sheet and make it very hidden in your code.

Read moreExcel-VBA : Change Passwords for all the WorkSheets in one shot

VBA-Excel – Merger – Merge or Combine Many Word Documents Into One

by

Download Link:WordMerger

If you want to combine or merger many word document into one file and you don’t want to do it manually, This piece of software will allow you merge as many word document you want, say 500-1000 word documents. This tool will provide you to option to select ot de-select word documents before merging.

How to Use it:

  1. Down­load the WordMerger.xlsm from the link pro­vided at the top and at the bot­tom of this article.
  2. Place all the Word documents, which you want to com­bine, into one folder (make sure all files are closed).

Read moreVBA-Excel – Merger – Merge or Combine Many Word Documents Into One

VBA-Excel: Date-Time Functions – WeekDay() and WeekDayName()

by

WeekDay()

Description:

The WeekDay function takes Date as a parameter and returns a number between 1 and 7, that is the week day of the date provided.

Format:

WeekDay (strDate [Firstdayofweek])

Arguments:

VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)

by

This tutorial will teach you how to get all the working days or weekdays in a specified date range(excluding Satudays and Sundays), simple but very useful when you have come up with a excel where you have to work with all the business working days. Example: If the date range is mentioned between 8-Jan-2014 to … Read more VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)

VBA-Excel: User Forms

by

User Forms are one of the very important features in Excel Macro. It provides and an interface to the user for interaction with the workbook. It has many controls which makes user’s life easy for entering the data from excel in a specific manner or reading the particular data from the excel.

Getting Started : Create a Blank User Form

  1. To open or create a new user form, follow the steps below
  2. Open an Excel file
  3. Press “Alt+F11” to open the Microsoft Visual Basic Editor.
  4. Make Project Explorer and Property Window visible from “view menu” (If not already visible)
  5. Select the Project from the Project Explorer.
  6. Form “Insert” menu, select “User Form”

  7. Read moreVBA-Excel: User Forms

VBA-Excel: User Define Functions

by

Microsoft Excel has lot of built in formulas like Sin(), Avg() and many more, which we discuss in detail in future articles, but apart from that you can write your own functions which will act as any other built-in formula in excel , Which is one of the powerful  feature of Excel.

Like other formulas, you can apply your function in each cell or range of cells.

Syntax:

Public Function functionName (Arg As dataType,……….) As dataType

or

Private Function functionName (Arg As dataType,……….) As dataType

Arg As dataType -> data type of argument

The second data type is the return type of function.

Public : The function is applicable to the whole project.

Read moreVBA-Excel: User Define Functions

VBA-Excel: Application.Wait OR Wait Method

by 
Description:

The Application.Wait () function pauses the running code or macro for the time specified and once that time is passed, it returns True value.

Format:

Expression.Wait (Time)

  • Expression : A variable that represents an Application Object.
  • The Wait method stops all the Microsoft Excel activities and prevent all the major operations.
Arguments:

VBA-Excel: Date-Time Functions – DateDiff()

by 
Description:

The DateDiff() function returns the specified time interval difference between the two dates provided.

Format:

DateDiff(interval,date1,date2[,firstdayofweek[,firstweekofyear]]) 

Arguments:
  • interval
    • Manda­tory
    • Type: String expression
    • The time interval you want the difference between two dates.

Read moreVBA-Excel: Date-Time Functions – DateDiff()

VBA-Excel: Add/Insert multiple objects from a folder in an Excel Document.

by

To Add or Insert Multiple objects from a folder in Microsoft Excel, you need to follow the steps below:

  • Create the object of FileSystemObject(Link)
  • Create Folder object using FileSystemObject and GetFolder (link) method and count the number of files.
  • Get the files Object
  • Run the Loop for each files in folder
  • Insert the File objects , using OLE objects.
  • Insert the added files name list in another sheet(just for reference)
  • Save the workbook

Read moreVBA-Excel: Add/Insert multiple objects from a folder in an Excel Document.

VBA-Excel: Array Functions – Join() – Converts Array to String

by 
Description:

 Join () Function returns a String that consist of array elements separated by the delimiter provided.

Format:

Join(arrArrayName[,delimiter])

Arguments:
    • arrArrayName
      • Manda­tory
      • Type: Array
      • Array Name which needs to be converted into string.

    Read moreVBA-Excel: Array Functions – Join() – Converts Array to String

    VBA-Excel: Array Functions – LBound and UBound()

    by 
    Description:

     LBound and UBound() Functions returns the starting index ( Lower Bound) and Ending index (Upper Bound) of an array.

    Format:

    LBound(arrArrayName [, dimension])

    UBound(arrArrayName [, dimension])

     

    Arguments:

    VBA-Excel: Create Array using Array() Function

    by 
    Description:

    You can create an array using Array() function. You need to provide all the elements which you want in an array inside Array() and store it in a variable and that variable will be converted into array.

    Format:

    Array(Element1, Element2, Element3…..ElementN)

    Arguments:

    • Elements
      • Manda­tory
      • Type: Any Data Type

    Read moreVBA-Excel: Create Array using Array() Function

    VBA-Excel: Fill Excel Range Values in a 2D Array

    by

    You can fill a range values from an excel work book into a two dimensional array. All you have to do is assign range values to a variable and then that variable will become array

    Two Dimensional Dynamic array -1
    Two Dimensional Dynamic array -1

    Function FnFillValues()

    Read moreVBA-Excel: Fill Excel Range Values in a 2D Array