Wednesday, March 18, 2009

Excel VBA: Determining whether a path exists

To check whether a path exists or not, we can use Excel VBA's GetAttr function. The following function returns True if a specified path exists, and return False otherwise:

Function isPathExist(ByVal pathname As String) As Boolean
 On Error Resume Next
 isPathExist = GetAttr(pathname) And vbDirectory = vbDirectory
End Function

Related posts:

---
If you like posts in this blog, you can to support me :)

Tuesday, March 10, 2009

Excel VBA: Determining Whether A File Exists Or Not

There are several ways to check whether a file exists. By using Microsoft Excel VBA statements and functions, or by using FileSystemObject (Microsoft Scripting Library). The following function returns True if a particular file exist, and return False if file does'nt exist. This function uses Dir function to check whether a file exists or not.

Function isFileExist(ByVal fname As String) As Boolean
 isFileExist = False
 If Dir(fname) <> "" Then isFileExist = True
End Function

Here's example how to use the function above:

Sub FunctionTest()
 If isFileExist("D:\SomeFile.txt") = False Then
     MsgBox "File not exists."
 Else
     MsgBox "File already exist."
 End If
End Sub

The next function do exactly as previous function, but this function uses FileSystemObject to check whether a file exist:

Function isFileExist2(ByVal fname As String) As Boolean
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    isFileExist2 = fs.FileExists(fname)
End Function
FIN.

Related posts:

---
If you like posts in this blog, you can to support me :)

Thursday, March 5, 2009

Excel VBA: Cell Alignment

In Excel VBA macro we can control the alignment of cell, both horizontally and vertically. To change alignment, we can use the following properties of the Range object:
  • HorizontalAlignment. Set to xlLeft, xlCenter, xlRight, xlDistributed, or xlJustify.
  • VerticalAlignment. Set to xlTop, xlCenter, xlBottom, xlDistributed, or xlJustify.
Following example sets horizontal alignment to justify:

Worksheets("Sheet1").Range("A1:D5").HorizontalAlignment = xlJustify


Related Post:
---
If you like posts in this blog, you can to support me :)

Tuesday, March 3, 2009

Excel VBA: Font Formatting

When we working in Excel, if we want to changes font properties we may use Font tab in the Format Cells dialog box. In Excel VBA, we control the font's appearance by the Font object. The Font object have several properties that correspond to various aspects of the font's appearance. Here are several list of font's properties:

Property Description
Name
The name of the font.
Bold True if the font is bold. Read/write Variant.
Italic True if the font style is italic. Read/write Boolean.
Underline Returns or sets the type of underline applied to the font.
Can be set to xlUnderlineStyleNone, xlUnderlineStyleSingle,
xlUnderlineStyleDouble, xlUnderlineStyleSingleAccounting,
xlUnderlineStyleDoubleAccounting. Read/write Variant.
Size Returns or sets the size of the font. Read/write Variant.
Subscript True if the font is formatted as subscript. False by default. Read/write Variant.
Superscript True if the font is formatted as superscript; False by default. Read/write Variant.
Strikethrough True if the font is struck through with a horizontal line. Read/write Boolean.

This example sets font name of range A1:B4 to Tahoma in ActiveSheet in ActiveWorkbook:

ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Name = "Tahoma"

The Color property uses an RGB value, which identifies a color in terms of its red, green,
and blue components. To set this property, use the RGB function:

RGB(r, g, b)

The next example sets font color of range B4 to Blue in ActiveSheet in ActiveWorkbook:

ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Color = RGB(0, 0, 255)

We can also use predefined constants to sets font color, they are vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, and vbWhite.

This example do exactly as previous example:

ActiveWorkbook.ActiveSheet.Range("A1:B4").Font.Color = vbBlue



Related Post:
---
If you like posts in this blog, you can to support me :)

Monday, March 2, 2009

Excel VBA: Changing Row and Column Size

Sometimes, in Microsoft Excel we need to change width of columns or height of rows in a worksheet to accomodate data they contain. In Excel Visual Basic for Application (VBA macro), to change columns width we can use ColumnWidth property. Following excel macro code sets width of column C in "Sheet1" worksheet to 24:

Sheets("Sheet1").Columns("C").ColumnWidth = 24

To change columns width to fit data in columns, we can use AutoFit method. The following example uses AutoFit method to change the size of C:F in the "Sheet1" worksheet:

Sheets("Sheet1").Columns("C:F").AutoFit

RowHeight property is used to change rows height of a range. For example:

Sheets("Sheet1").Rows(2).RowHeight = 56


Related Post:

---
If you like posts in this blog, you can to support me :)

Sunday, March 1, 2009

Excel VBA: Number Formatting in Excel VBA Macro

Number formatting controls how numbers on cells are displayed, it has no effect on cells that contain text. In Microsoft Excel, if we want to apply number formatting we can use Format Cells dialog box. To format numbers in VBA macro we can use the NumberFormat property. Following are some number formatting codes to format numbers.

Number Formatting Codes
Character Meaning Code example Format example
# Significant digit ##.# 10.78 displays as 10.9
0 Nonsignificant 0 #.00 5.4 displays as 5.40
. Decimal point ##.## 14.55 displays as 14.55
$ Currency symbol $#.## 56.78 displays as $56.78
% Percent symbol #.#% 0.075 displays as 7.5%
, Thousands separator #,### 123000 displays as 123,000


Here is the example VBA code to display numbers with no commas or special characters, and no decimal places:

Sub NumFormat()
 Range("A1").NumberFormat = "0"
End Sub

We can display positive and negative numbers differently. Following number format code will display negative numbers in red color.

#.##;[Red]#.##

To specify a display color, include the color name enclosed in square brackets at the start the format code. The available color names are:
  • Black
  • Blue
  • Cyan
  • Green
  • Magenta
  • Red
  • White
  • Yellow

---
If you like posts in this blog, you can to support me :)