VBA Resources For MS Excel

Christopher Rath

2012-11-17

MS Excel

These are MS Excel-specific VBA tips:

Sub Auto_Open()
'
' AutOpen Macro
' Actions to perform each time a workbook is opened.
'
    Application.CommandBars("Reviewing").Visible = False        ' Close the Reviewing toolbar.
End Sub
Sub DeleteHiddenColumns()
'
' DeleteHiddenColumns() Macro
' Within the selection, delete any columns that are marked as hidden.
'
' The following code will *not* work because we are removing objects
' from the collection and VBA's OO model is not robust enough to cope
' with this situation:
'        Dim myCol As Range
'        For Each myCol In Selection.Columns
'            If True = myCol.EntireColumn.Hidden Then
'                myCol.EntireColumn.Hidden = False
'                myCol.Delete Shift:=xlToLeft
'            End If
'        Next myCol
'
    If 1 <> Selection.Areas.Count Then
        MsgBox "Error: the selection must be contiguous."
    Else
        Dim i As Integer
        Dim firstCol As Integer
        Dim lastCol As Integer
        
        firstCol = Selection.Item(1).Column
        lastCol = firstCol + Selection.Columns.Count - 1
        
        For i = lastCol To firstCol Step -1
            With Selection.Worksheet.Columns(i)
                If True = .EntireColumn.Hidden Then
                    .EntireColumn.Hidden = False
                    .Delete Shift:=xlToLeft
                End If
            End With
        Next i
    End If
End Sub
Sub FindNextcomment()
'
' FindNextcomment() Macro
' Find the next comment.
'
    Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    If ActiveSheet.Comments.Count > 0 Then
        ActiveSheet.Comments(1).Visible = True
        ActiveSheet.Comments(1).Parent.Select
    End If
End Sub
Sub SetVerticalAlignmentToTop()
'
' SetVerticalAlignmentToTop() - Change the vertical alignment to Top for all selected cells.
'
    With Selection
        .VerticalAlignment = xlTop
    End With
End Sub
Sub RC_A1_Toggle()
'
' RC_A1_Toggle Macro
' Toggle between RC and A1 addressing modes.
'
    If Application.ReferenceStyle = xlR1C1 Then
        Application.ReferenceStyle = xlA1
    Else
        Application.ReferenceStyle = xlR1C1
    End If
End Sub
=RIGHT(CELL("filename",RC),LEN(CELL("filename",RC))-FIND("]",CELL("filename",RC),1))
			

Other Resources

See the page that linked to this one for other VBA resources I have found helpful.


©Copyright 2005–2014, Christopher Rath
Address: 1371 Major Rd., Ottawa, ON, Canada K1E 1H3
Last updated: 2016/05/15 @ 17:58:04 ( )