Excel VBA functions
Excel Books on Amazon.com
Get Current Directory in Excel VBA
This function retrieves the directory of the current workbook.
Function GetCurDir()
Dim str As String
Dim pos As Integer
str = ActiveWorkbook.FullName
pos = InStrRev(str, "\")
str = Mid(str, 1, pos)
GetCurDir = str
End Function
Export all worksheets in a workbook in the CSV format
This VBA procedure will save each worksheet in the workbook in the same directory as the workbook.
The csv files will have the worksheet name and a .csv extension.
Sub SaveAllWorkSheetsAsCSV()
Dim ws As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim i As Integer
i = 0
Dim Dir As String
Dim Name As String
Dir = GetCurDir
For Each ws In wb.Worksheets
Name = Dir & ws.Name + ".csv"
ws.SaveAs Name, xlCSV
i = i + 1
Next
MsgBox "Saved " & i & " worksheets"
End Sub
Optionally, you can delete the first row in each worksheet before exporting it:
For Each ws In wb.Worksheets
ws.Rows(1).Delete
Name = Dir & ws.Name + ".csv"
ws.SaveAs Name, xlCSV
i = i + 1
Next
Change the background color of the data range
The following example highlights the range containing data in green.
Dim ws As Worksheet
Dim rng As Range
Set ws = ActiveSheet
Set rng = ws.UsedRange
rng.Interior.ColorIndex = 4
Alternatively, you can use a short version:
ActiveSheet.UsedRange.Interior.ColorIndex = 4
Get the number of rows in the Worksheet
ActiveSheet.UsedRange.Rows.Count
Function to Truncate a String
Function Truncate(str As String, intEndPos As Integer)
Truncate = Left(str, intEndPos)
End Function
The function accepts two arguments: a sting to be truncated, and the maximum number of characters the string should have.
It returns the truncated string.
Get the number of sheets in the active workbook
Function getNumberSheets()
getNumberSheets = ActiveWorkbook.Sheets.Count
End Function
More Excel VBA Tips
