GetFileSize function

Function GetFileSize(folderspec As String) As Variant

'   Returns an array of file size that match FileSpec

    Dim fs, f, f1, fc, s

    Dim FileArray() As Variant

    Dim FileCount As Integer

    Dim filesize As String

    Set fs = CreateObject("Scripting.FileSystemObject")

    Set f = fs.GetFolder(folderspec)

    Set fc = f.Files

    FileCount = 0

    For Each f1 In fc

        s = f1.Size

        filesize = s

        FileCount = FileCount + 1

        ReDim Preserve FileArray(1 To FileCount)

        FileArray(FileCount) = filesize

        GetFileSize = FileArray

    Next

End Function

GetFileList function

Function GetFileList(filespec As String) As Variant
'***Function to get list of files in specific folder***'
' Returns an array of file names that match FileSpec

' If no matching files are found, it returns False

Dim FileArray() As Variant

Dim FileCount As Integer

Dim FileName As String

On Error GoTo NoFilesFound

FileCount = 0

FileName = Dir(filespec)

If FileName = "" Then GoTo NoFilesFound

' Loop until no more matching files are found

Do While FileName <> ""

   FileCount = FileCount + 1

   ReDim Preserve FileArray(1 To FileCount)

   FileArray(FileCount) = FileName

   FileName = Dir()

Loop

GetFileList = FileArray

Exit Function

' Error handler

NoFilesFound:

GetFileList = False

End Function

Excel VBA command (4)

Calculation

Command	Description
Application.Calculation = xlManual	Set calculation option to Manual (by F9)
Application.Calculation = xlAutomatic	Set calculation option to Automatic
Activesheet.Calculate	Calculate on activesheet only

CutCopyMode

Command	Description
Application.CutCopyMode=False	Clear data in clip board

Quit

Command	Description
Application.Quit	Quit Excel

ScreenUpdating

Command	Description
Application.ScreenUpdating = False	Result of commands between both will not display on screen
Application.ScreenUpdating = True

StatusBar

Command	Description
Application.DisplayStatusBar = False	Not display status bar
Application.DisplayStatusBar = True	Display status bar
Application.StatusBar = "Test"	Set to show "Test" on status bar

DisplayAlert

Command	Description
Application.DisplayAlerts = False	Not display any alert such as dialog box
Application.DisplayAlerts = True	Display alert

Excel VBA command (3)

Workbook

Command	Description
1. Workbooks.Open "Book1.xls"	Open "Book1.xls"
2. Workbooks.Open Sheets("Sheet1").Range("A1").Value	Open file by referring to file name in cell "A1" of "Sheet1"
3. Workbooks.Open Filename:= "C:\Book1.xls"	Open "C:\Book1.xls"
4. ThisWorkbook.Close	Close workbook which is coded
5. Workbooks("Book1.xls").Close	Close file "Book1.xls"
6. Workbooks(Range("A1").Value).Close	Close file by referring to file name in cell "A1"
7. ActiveWorkbook.Save	Save active workbook
8. Workbooks("Book1.xls").Save	Save "Book1.xls"
9. Workbooks(Range("A1").Value).Save	Save file by referring to file name in cell "A1"
10. ActiveWorkbook.SaveAs "C:\Book1.xls"	SaveAs active workbook to "C:\Book1.xls"
11. Workbooks("Book1.xls").SaveAs "C:\Book1.xls"	SaveAs "Book1.xls" to "C:\Book1.xls"
12. Workbooks(1).Close SaveChanges:=False	Close workbook index 1 without save
13. Workbooks(1).Close SaveChanges:=True	Close workbook index 1 with save
14. Kill "C:\Book1.xls"	Kill file "C:\Book1.xls"
15. Workbooks.Add	Add new workbook
16. ActiveWorkbook.Name	Return name of active workbook
17. Difference between ThisWorkBook and ActiveWorkBook	ThisWorkBook object refers to the workbook that the code is contained in ActiveWorkBook object refers to the workbook that is currently on top (Active) in Excel

Excel VBA command (2)

Worksheet

Command	Description
1. Sheets(“Sheet1”).Select	Select “Sheet1”
2. Sheets(1).Select	Select sheet index no. 1 (count from left hand side)
3. Sheets(Array("Sheet1","Sheet2")).Select	Select “Sheet1” and “Sheet2” together
4. Sheets("Sheet1").Visible=xlVeryHidden	Hide “Sheet1”, not found in menu Format > Sheet
5. ActiveWindow.SelectedSheets.Visible= False	Hide selected sheet together
6. Sheets(“Sheet1”).Delete	Delete “Sheet1”
7. Sheets.Count	Return number of sheets in this workbook
8. Sheets.Add	Insert 1 sheet
9. Sheets.Add Before:=Sheets("Sheet1")	Insert 1 sheet before “Sheet1”
10. Sheets.Add After:=Sheets(“Sheet1”)	Insert 1 sheet after “Sheet1”
11. Sheets.Add After:=Sheets(Sheets.Count)	Insert 1 sheet at last sheet
12. Sheets("Sheet1").Copy Before:=Sheets(2)	Copy “Sheet1” and insert before sheet index no. 2
13. Sheets("Sheet1").Copy After:=Sheets(2)	Copy “Sheet1” and insert after sheet index no. 2
14. Sheets(“Sheet1”).Name = “Test”	Rename sheet name from “Sheet1” to “Test”
15. Sheets(1).Name = “Test”	Rename sheet index no. 1 to “Test”
16. ActiveSheet.UsedRange.Columns.count	Return number of columns used in active sheet
17. ActiveSheet.UsedRange.Rows.count	Return number of rows used in active sheet
18. ActiveSheet.Protect	Protect active sheet from any changes in active sheet
19. ActiveSheet.Unprotect	Unprotect active sheet from any changes in active sheet

Excel VBA command (1)

Cell and Range

1. Cells.Select -> select all cells in worksheet
2. Range("A1").Select, Cells(1,1).Select -> select cell A1
3. Range("A1").Value, Cells(1,1).Value -> return value of cell A1
4. Rows(1).EntireRow.Select -> select entire row number 1
5. Columns("A:B").EntireColumn.Select -> select entire column A and B
6. Selection.CurrentRegion.Select -> select current cell region
7. ActiveCell.Row, ActiveCell.Column -> return row or column number of active cell
8. Selection.Rows.Count, Selection.Columns.Count -> return quantity of row or column of selected area
9. Selection.CurrentRegion.Rows.Count, Selection.CurrentRegion.Columns.Count -> return quantity of row or column of current cell region
10. Range("A1").Formula = "=B1+B2" -> create a formula =B1+B2 in cell A1
11. Range("A1").FormulaR1C1 = "=RC[1]+R[1]C[1]" -> create a formula = B1+B2 in cell A1

Random Number

RAND()

- return real number that >=0 and < 1

RANDBETWEEN(bottom,top)

- return integer in specified range

Note: every Excel calculations, random number will change, to fix the value generated

-> enter =rand() in formula bar and press F9

For example

1. =rand() => ex. 0.135
2. =rand()*10 => return real number that >=0 and < 10
3. =int(rand()*10) => return integer that >=0 and < 10
4. =randbetween(1,10) => return integer that >=1 and <=10 (ex. 7)

Random in VBA

RND()

- return single value that >=0 and < 1

Int(Rnd * (bottom- top+ 1)+ bottom)

- return integer in specific range

For example

--------------

Sub test()

Randomize

MsgBox Rnd()

End Sub

=> return single number ex. 0.741725

--------------

Sub test2()

'to find random integer in range between 1 and 10

Dim RandNo As Integer

Randomize

RandNo = Int(Rnd() * 10 + 1) '10 = 10-1+1

MsgBox RandNo

End Sub

=> return integer that >=1 and <=10 ex. 6

--------------

Note: single value is ranging in value from -3.402823E38 to -1.401298E-45 for negative values and from 1.401298E-45 to 3.402823E38 for positive values