Excel VBA
This category will hold articles regarding developement in Excel VBA. It will serve as a wiki and an Excel VBA Framework for myself.
Some development tasks reoccur for every customer. Since I am a lazy bum it will be nice to have a central source where I can reuse source code from.
Determining the last row actually containing information in an Excel worksheet can be an annoying task, if you are relying on the Excel API to do the trick for you. Especially when you are not exatcly in control over the handed files you are dealing with and people are allowed to enter information anywhere it is not as easy as it should be.
Source Code automating Excel search function
Below function is determining the last row containing data in provided worksheet using the Excel search function.
This approach works only, if no cells in given sheet are merged.
Therefore merged cells are removed from a handed workbook before determining its last row containing data.
- Hits: 6135
This function copies used range of handed worksheet and replaces it with values only.
Source Code
- Hits: 5741
Actually you can list all worksheet names in a workbook using the object path Workbook.Worksheets(index).Name.
I needed to have the names as strings in a VBA.Collection, so I developed a little function collecting the names.
' @Author - Alexander Bolte
' @Change Date - 26.12.2013
' @Description - Creates a list of all sheet names in given workbook.
' @Param wrk - Excel workbook the sheet name collection should be created from.
' @Returns - VBA.Collection holding all sheet names from given Microsoft Excel workbook.
Public Function getSheetNamesFromWorkbook(ByRef wrk As Workbook) As VBA.Collection
Dim i As Integer
Dim sheetNames As New VBA.Collection
On Error GoTo errHandle:
For i = 1 To wrk.Worksheets.Count
sheetNames.Add wrk.Worksheets(i).Name
Next i
errHandle:
If Err.Number <> 0 Then
Err.Clear
End If
Set getSheetNamesFromWorkbook = sheetNames
End Function
- Hits: 6017
Page 3 of 10