Home > Excel VBA Tips

Excel VBA Tips

Some info about VBA in Excel, collected here for my convenience.

Number of rows in Excel:
65536

Number of columns in Excel:
255

Add a worksheet:
Workbooks("filename.xls").Worksheets.Add.Name = sheetnam

Suppress Alerts (useful when saving files automatically)
Application.DisplayAlerts = False

Find the active sheet:
MsgBox ActiveSheet.Name

Find the directory that the active workbook is located in:
Dir = ActiveWorkbook.Path
where Dir will equal something like "C:\MyDirectory\"

Function returnValueFromFunction()

'to return a value from a function: on the last line of your function, assign the return value
'to a variable of the same name as your function

returnValueFromFunction = 5

End Function

Sub UseStDevFunction()

Dim startOfData
Dim endOfData
Dim i

startOfData = 3
endOfData = 5
i = 1

'Set the range of data to be analyzed (in this case, part of a column)
Set RangeOfData = Range(Cells(startOfData, i), Cells(endOfData, i))

'Calculate the Standard Deviation
Cells(1, 2).Value = Application.WorksheetFunction.StDev(RangeOfData)

End Sub