VBA has in-built function SetAttr to change attribute of a
file or folder. This Function is classified as File or Directory function.
Below is the syntax of SetAttr and it takes two parameters.
Syntax: SetAttr (FilePath, Attribute)
FilePath is the fully qualified path of the file and
Attribute is the parameter of a file which you wish to change. In this example...
Showing posts with label VBA. Show all posts
Showing posts with label VBA. Show all posts
How to add line break and tab space in outlook using VBA?
in VBA
with
No comments
When working on email automation in Outlook, the content of the mail can be of type Body or HTMLBody. Depending on the type we select we can use html tags or VBA constants to add Line Break or Tab Space in the body of the mail.
Line Break:
Assume MailObj is the Outlook mail object. To add line break between two texts in the body of the mail we can use either VBA constant (vbCrLf) or html tag (<br/>)...
How to convert mailing list in Excel column into a string using Collections?
Let us assume you have say 20 email Ids in an Excel sheet to which a common mail communication is to be sent. One way of combining all the emails into a string is using CONCATENATE function. However it is difficult if the no. of email IDs to be combined are more. Another way to accomplish this is by using Collections in VBA. Collections are nothing but group of related objects.
First store all...
How to activate other Microsoft application using VBA
in VBA
with
No comments
If you are working on a project that involves interaction with multiple MS Office application, then VBA has a method to invoke other office applications within Excel. For example,
Below code invokes or activates Word application.
Application.ActivateMicrosoftApp (xlMicrosoftWord)
Similarly, replace xlMicrosoftWord to xlMicrosoftMail to activate Outlook. So one can create keyboard...
Static variable in VBA
in VBA
with
No comments
A static variable is a local variable which retain its value even after the execution of procedure. Static variable can be declared by placing static keyword before the declaration.
For example,
static username as string
static counter as integer
In below example static variable "m" retain its value everyime a program is executed
Sub StVar()
Static m As Integer
m = m + 1
MsgBox (m)
End...
How to create and use Dynamic Arrays in VBA
in VBA
with
No comments
Arrays are important elements of any programming language. An array can be one, two or multi dimensional. An Array declared without a specific size (re-sizable) is called a Dynamic Array. Dynamic Array can be declared as shown below.
Dim NewArray () as Integer
However, to use the Array in application, We must define its size. To change size of an Array we use ReDim keyword.
ReDim NewArray (4)
Now,...
How to repeat the Header row in each page using VBA?
Consider an Excel document which has employee details with 5000 employees. The 1st row of the sheet has details, such as Name, Age, Designation, Date of Birth and Date of Joining of employees.
Now, If we want to take print of these employee list, and if we want the header row to appear in each page of the print out then, select the sheet which has the data and use the below VBA code to...
How to enter formula with relative and absolute reference using VBA
Relative vs Absolute:
To enter a formula using VBA into a single cell, We use Range().Formula property and assign it to worksheet function.
For Example, VBA code to enter VLOOKUP formula in cell B1 is
RANGE("B1").FORMULA = "=VLOOKUP(A1,SHEET2!A:B,2,0)"
Suppose,...
How to get only Integer portion of a division using VBA operator
Excel has a built in function QUOTIENT() to return integer portion of a division. The similar result can be obtained using ROUND() function. However, VBA has operator to return only integer portion of a division. The symbol for getting division with integer portion as result is "\".
Below is an an...
How to make Excel UserForm Immovable using VBA
By default excel userforms are movable. This will let the user to view the Excel sheet by dragging the user form aside. In case if the the creator of the user form does not want the user to view Sheets, then UserForm position can be fixed using a VBA code as given below.
...
The Difference between ByVal and ByRef in Excel VBA
The Difference between ByVal and ByRef in Excel VBA:
In VBA, ByVal (By Value) and ByRef (By Reference) are two different ways of passing arguments to the procedure. When a parameter is passed to the procedure as ByRef, the value of the parameter can change at runtime. However, if the parameter is passed...
How to copy only visible cells in Excel using VBA
in VBA
with
No comments
Consider an Excel sheet which has list of customers from two different regions, say A and B. If we have to copy only the customers from region A into sheet 2, Apply filter to region column for criteria A, select the visible data and use the below code to copy only the visible cells:
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").select
ActiveSheet.Paste
This...
Snake and Ladder Game in Excel
in VBA
with
2 comments
Snake and Ladder is a most popular classic board game. Kids enjoy playing it. I have created Excel version of this classic board game with 2 players. Below is a video demonstration of it.
Learn Financial Management Courses online
Use Coupon Code 580EDUNF83 to get additional 20% discount
...
How to convert an Excel sheet into PDF in Office 2007
in VBA
with
No comments
There are many web based application available to convert an Excel sheet into PDF document. However, In most of these
applications only limited features are available for free and are also subjected to daily usage limit. Follow the below steps to convert anExcel sheet into PDF document.
1) Download and Install Excel to PDF AddIn from Microsoft's official website.
...