@TutorialHorizon
Excel-Macro-VBA
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:
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:
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
Steps:
Note: If you wont provide the password then your cells will not be locked.
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.
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:
Description:
The Timer() function returns the Number of seconds and MiliSeconds since 12:00 AM.
Format:
Timer()
Example:
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:
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
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
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.
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)
Arguments:
Description:
The DateDiff() function returns the specified time interval difference between the two dates provided.
Format:
DateDiff(interval,date1,date2[,firstdayofweek[,firstweekofyear]])
Arguments:
To Add or Insert Multiple objects from a folder in Microsoft Excel, you need to follow the steps below:
Description:
Filter() Function returns one dimensional array containing the filtered array elements based upon the filter options provided
Format:
Filter(arrArrayName,FilterValue [, Include[, vbCompare]])
Arguments:
Description:
Join () Function returns a String that consist of array elements separated by the delimiter provided.
Format:
Join(arrArrayName[,delimiter])
Arguments:
Description:
IsArray() Function determines whether the specified variable is array or not and based upon that returns the Boolean value.
Format:
IsArray(varVariable)
Arguments:
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:
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:
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
Function FnFillValues()