Current Cell Content

Sometimes we need to know what the cell contains ie dates, text or formulas before taking a course of action. In this example a message box is displayed. Replace this with a macro should you require another course of action.
Sub ContentChk()

If Application.IsText(ActiveCell) = True Then

MsgBox "Text" 'replace this line with your macro

Else

If ActiveCell = "" Then

MsgBox "Blank cell" 'replace this line with your macro

Else

End If

If ActiveCell.HasFormula Then

MsgBox "formula" 'replace this line with your macro

Else

End If

If IsDate(ActiveCell.Value) = True Then

MsgBox "date" 'replace this line with your macro

Else

End If

End If

End Sub

VBA - Check Extension of File

When looping through all files in a folder, it's often necessary to check the extension of each file, so you only mess with Excel files, for example.
The code below shows how to retrieve the extension of a file, define an array with "allowed" extensions, and match the extension of the file to the array.

Sub KorImport()

Dim MinExtensionX

Dim Arr() As Variant

Dim lngLoc As Variant

'Retrieve extension of file

MinExtensionX = Mid(MyFile.Name, InStrRev(MyFile.Name, ".") + 1)

Arr = Array("xls", "xlsx") 'define which extensions you want to allow

On Error Resume Next

lngLoc = Application.WorksheetFunction.Match(MinExtensionX, Arr(), 0)

If Not IsEmpty(lngLoc) Then '

'DO STUFF if it's an xls/xlsx file. Otherwise, chose next file in folder

End If

VBA - Retrieve Last Row From More Than One Column

Normally, its enough to know the last row in a specific column. In those cases I normally just use this widely method:

`Getting last row from column A

LastRow = Range("A" & Rows.Count).End(xlUp).row

But when you have several columns (for example 1 to 10) and you need to retrieve the last cell in use in any of these columns, we need something else.

VBA - Get name of file without extension

The easiest way to get the name of a file is of course to use ThisWorkbook.Name. It will supply you with the name and the extension of the file (for example "MyWorkbook.xlsx").But if you want to retrieve only the name the workbook and not the extension, you’ll need this:



Dim NameOfWorkbook

NameOfWorkbook = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))



It uses the function InStrRev to find the last occurance of "." and the Left() function is then used to assign all chars left of this position to the NameOfWorkbook variable.

MS Excel: VBA Functions - Logical Functions

CASE (VBA)

IF-THEN-ELSE (VBA)

Information Functions

ISDATE (VBA)

ISERROR (WS, VBA)

ISNULL (VBA)

ISNUMERIC (VBA)

MS Excel: VBA Functions - Numeric / Mathematical Functions

ABS (WS, VBA)


ATN (VBA)

COS (WS, VBA)

EXP (WS, VBA)

FIX (VBA)

FORMAT Numbers (VBA)

INT (WS, VBA)

LOG (WS, VBA)

RND (VBA)

ROUND (VBA)

SGN (VBA)

SIN (WS, VBA)

TAN (WS, VBA)

MS Excel: VBA Functions - String Functions

ASC (VBA)


CHR (VBA)

Concatenate with & (WS, VBA)

CURDIR (VBA)

FORMAT Strings (VBA)

INSTR (VBA)

INSTRREV (VBA)

LCASE (VBA)

LEFT (WS, VBA)

LEN (WS, VBA)

LTRIM (VBA)

MID (WS, VBA)

REPLACE (VBA)

RIGHT (WS, VBA)

RTRIM (VBA)

SPACE (VBA)

STR (VBA)

STRCONV (VBA)

TRIM (WS, VBA)

UCASE (VBA)

VAL (VBA)

GETATTR Function (VBA)

In Microsoft Excel, the GETATTR function returns an integer that represents the attributes of a file, folder, or directory.


Syntax

The syntax for the GETATTR function is:

GetAttr ( path )path is the path to a file, folder, or directory that you wish to retrieve the attributes for.

VBA Function Example

The GETATTR function can only be used in VBA code. Here are some examples of what the GETATTR function would return:

GetAttr("C:\Chithu.doc") would return 0

GetAttr("H:\Documents\Chithu.xls") would return 1

REPLACE function (VBA)

In Microsoft Excel, the REPLACE function replaces a sequence of characters in a string with another set of characters. Please note that the worksheet version of the REPLACE function has different syntax.


Syntax:-

 

The syntax for the REPLACE function is:

 

Replace ( string1, find, replacement, [start, [count, [compare]]] )string1 is the string to replace a sequence of characters with another set of characters.

• find is the string that will be searched for in string1.
• replacement will replace find in string1.
• start is optional. This is the position in string1 to begin the search. If this parameter is omitted, the REPLACE function will begin the search at position 1.
• count is optional. This is the number of occurrences to replace. If this parameter is omitted, the REPLACE function will replace all occurrences of find with replacement.

 

VBA Function Example:-

 

The REPLACE function can be used in VBA code. Here are some examples of what the REPLACE function would return:

Replace("cpalani1", "palani1", "hidambaram") would return "chidambaram"

Relink Sections

Sub RelinkSections()

Dim myRng As Word.Range
Set oDoc = ActiveDocument
For i = 1 To oDoc.Sections.Count

With oDoc.Sections(i)

For j = 1 To 3

.Headers(j).LinkToPrevious = True
.Footers(j).LinkToPrevious = True

Next j

End With

Next i

lbl_Exit:

Exit Sub

End Sub

Add Section & Kill Link 2 Previous

Option Explicit


Dim i As Long
Dim j As Long
Dim oDoc As Word.Document

Sub AddSectionAndKillLinkToPrevious()

Dim myRng As Word.Range
Set oDoc = ActiveDocument
Selection.InsertBreak Type:=wdSectionBreakNextPage

'Get the index number of the added section

i = oDoc.Range(0, Selection.Sections(1).Range.End).Sections.Count
With oDoc.Sections(i)
For j = 1 To 3
.Headers(j).LinkToPrevious = False
.Footers(j).LinkToPrevious = False
Next j
End With

'Note: j provides the constant value to unlink all three header\footer types.

lbl_Exit:

Exit Sub

End Sub

Joining Excel Column Text Together

Joining Excel Column Text Together:-
==============================

There are times where we import text file into Excel an we get text that are separated. I received an email asking how put these text together. Select across your cells first and run this VBA macro.

Sub JoinColumnTexts()

myCol = Selection.Columns.Count

For i = 1 To myCol

ActiveCell = ActiveCell.Offset(0, 0) & ActiveCell.Offset(0, i)

ActiveCell.Offset(0, i) = ""

Next i
End Sub

Send eMail though excel workbook

eMail Workbook:-

To email your current workbook the following code.

Sub Email()

ActiveWorkbook.SendMail recipients:=chithu.palaniappan@gmail.com

End Sub

Highlight duplicate data in your worksheet.

There are times you need to highlight duplicate data in your worksheet.

Sub DupsRecord()


Application.ScreenUpdating = False

Rng = Selection.Rows.Count

For i = Rng To 1 Step -1

myCheck = ActiveCell

ActiveCell.Offset(1, 0).Select

For j = 1 To i

If ActiveCell = myCheck Then

Selection.Font.Bold = True

Selection.Font.ColorIndex = 3

End If

ActiveCell.Offset(1, 0).Select

Next j

ActiveCell.Offset(-i, 0).Select

Next i

Application.ScreenUpdating = True

End Sub

VBA coding and template designing for 2013?

How to enable excel 2013 developer tab for VBA coding and template designing.


1. First ope the `backstage view' of excel 2013 by clicking the "file" option in the ribbon.
2. Click Options -> a Pop Up apprears.
3. Cick "Customize ribbon" Option.
4. In the right column choose the "Main Tabs" from the dropdown (by default it will be there).
5. please check the ‘Developer’ cehck box.
6. click OK -> close and reopen your excel.
you have successfully enabled the Developer tab and have access to all kind of developer tools in excel 2013 to begin your development.

Identify Browser Version using Excel VBA:-

Identify Browser Version using Excel VBA:-




Sub CheckWebOptions()

Dim wkbOne As Workbook
Set wkbOne = Application.Workbooks(1)

‘ Determine if IE8 is the target browser.


If wkbOne.WebOptions.TargetBrowser = msoTargetBrowserIE6 Then

MsgBox "The target browser is IE6 or later."

ElseIf wkbOne.WebOptions.TargetBrowser = msoTargetBrowserIE5 Then

MsgBox "The target browser is IE5 or later."

ElseIf wkbOne.WebOptions.TargetBrowser = msoTargetBrowserIE4 Then

MsgBox "The target browser is IE4 or later."

ElseIf wkbOne.WebOptions.TargetBrowser = msoTargetBrowserV4 Then

MsgBox "Microsoft Internet Explorer 4.0, Netscape Navigator 4.0, or later."

ElseIf wkbOne.WebOptions.TargetBrowser = msoTargetBrowserV3 Then

MsgBox "Microsoft Internet Explorer 3.0, Netscape Navigator 3.0, or later."

Else

MsgBox "The target browser is not in the given list"

End If


End Sub