Sub SplitByComma() Dim arr As Variant Dim row As Integer Dim col As Integer row = ActiveCell.row col = ActiveCell.Column arr = VBA.split(ActiveCell.Value, ",") For i = 0 To UBound(arr) Cells(row + 1 + i, col).Value = Trim(arr(i)) Next i End Sub
Excel VBA Revisited
Excel VBA tips and functions
Thursday, February 19, 2015
Split String By Comma
The following routine splits the comma-separated contents of an active cell into strings and prints each result below the active cell. It will overwrite the cells below the active cell.
Sunday, October 26, 2014
Friday, February 15, 2013
EndsWith and StartsWith For VBA
Here are listings for 2 functions that are missing in VBA: string starts with and string ends with. The functions are case-insensitive. They use a simple equality check which will probably make them unsuitable for non-ASCII text.
Public Function EndsWith(str As String, ending As String) As Boolean Dim endingLen As Integer endingLen = Len(ending) EndsWith = (Right(Trim(UCase(str)), endingLen) = UCase(ending)) End Function Public Function StartsWith(str As String, start As String) As Boolean Dim startLen As Integer startLen = Len(start) StartsWith = (Left(Trim(UCase(str)), startLen) = UCase(start)) End Function
Friday, June 15, 2012
Add Hyperlinks
The following procedure converts a url to a hyperlink in range:
Sub AddHyperlinks(rng As Range)
Dim c As Range
For Each c In rng
If StartsWith(c.Value, "http") Then
c.Hyperlinks.Add Anchor:=c, Address:=c.Value
End If
Next
End Sub
It uses the StartsWith function from the previous post.
Sub AddHyperlinks(rng As Range)
Dim c As Range
For Each c In rng
If StartsWith(c.Value, "http") Then
c.Hyperlinks.Add Anchor:=c, Address:=c.Value
End If
Next
End Sub
It uses the StartsWith function from the previous post.
Thursday, June 7, 2012
Some Excel Range Functions
Here I will list code for some useful VBA functions that deal with an Excel range. I think the function descriptions are self-explanatory.
Determine Last Row in a Range
Function GetLastRow(rng As Range) GetLastRow = rng.Rows.Count + rng.Row - 1 End Function
Return an Array of Row Numbers from a Range
Function RowsFromRange(rng As Range) Dim c As Range Dim i As Integer Dim arr() As Integer ReDim arr(rng.Rows.Count - 1) For Each c In rng.Rows arr(i) = c.Row i = i + 1 Next RowsFromRange = arr End Function
Initialize VBA Array
Declare an array as Variant:
Dim arr as Variant
Use the VBA Array function to initialize the array:
arr = Array(154, 155, 156, 157)
Dim arr as Variant
Use the VBA Array function to initialize the array:
arr = Array(154, 155, 156, 157)
Wednesday, June 6, 2012
Check if Worksheet Exists
This is a VBA function that checks if a Worksheet exists. It accepts a worksheet name as a parameter. It loops through worksheet names and stops if it finds the one you are looking for.
Otherwise, it goes through all of them and returns false. Also, it is case-insensitive.
Otherwise, it goes through all of them and returns false. Also, it is case-insensitive.
Private Function WorksheetExists(wsName As String) As Boolean Dim ws As Worksheet Dim ret As Boolean ret = False wsName = UCase(wsName) For Each ws In ThisWorkbook.Sheets If UCase(ws.Name) = wsName Then ret = True Exit For End If Next WorksheetExists = ret End Function
Subscribe to:
Posts (Atom)