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)