working on it ...

Filters

Explore Public Snippets

Sort by

Found 869 snippets matching: vba

    public by fabio.nosenzo  264119  8  6  0

    Refresh all pivot table excel vba macro

    With this snippet you can refresh all the pivot tables in excel workbook within a macro
    ThisWorkbook.RefreshAll

    public by fabio.nosenzo  3288  0  6  1

    Replace Function MS Excel VBA

    Replace a string with another within a string
    ' Create a file path with date
    sPath = "C:\myfile - " & Date
    ' / char is not admitted
    ' replace / with -
    sPath = Replace(sPath, "/", "-")

    public by fabio.nosenzo  2805  0  5  0

    Use MS Excel formulas in VBA

    General description of vba excel formula insertion
    ' general description
    ' Application.formulaname(formula parameters)
    
    ' example
    ' count cells with value = x
    
    Dim iCounter As Integer
    iCounter = Application.CountIf(Range("A1:D5"), "x")
    

    public by fabio.nosenzo  6524  1  7  1

    Get Current Date In VBA

    How to use date function for getting date in VBA Excel. In VBA you can't use today() function but you can use date ()
    ' In VBA you can't use today() function
    ' instead you have to use date ()
    
    'get today date:
    Cells (1,1) = Date
    
    'get yesterday date:
    Cells (1,1) = Date - 1

    public by fabio.nosenzo  6024  3  7  0

    Screen update disable in Excel VBA

    How to disable and enable screen update in Excel VBA, speeding up macro execution
    ' disable screen updating
    Application.ScreenUpdating = False
    
    ' enable screen updating
    Application.ScreenUpdating = True
    

    public by fabio.nosenzo  3180  3  6  2

    Get column letter Excel VBA

    Given the column number, this function returns the letter(s) corrisponding to the excel column. Thanks to brettdj
    Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
    End Function
    
    'test the code
    
    Sub Test()
    MsgBox Col_Letter(100)
    End Sub            

    public by fabio.nosenzo  3584  1  6  0

    Manage errors excel VBA

    Command to be used as Iferror() formula
    ' example with vlookup formula
    
            sVlookup = Application.VLookup(Cells(i, j), Range("A1:C101"), 2, False)
            If IsError(sVlookup) Then sVlookup = "not found"
                            

    public by fabio.nosenzo  3301  14  6  1

    Excel VBA Merge cells with same value

    Merge cells in different columns with the same value in an ordered list
    Sub merge_cells()
    Application.DisplayAlerts = False
        
        'row to be avaluated
        iRow = 1
        'number of columns to be evaluated
        iNumberOfColumns = 12
        'first Column to be evaluated
        iFirstColumn = 4
        For i = iFirstColumn + 1 To iNumberOfColumns + iFirstColumn + 1
            If Cells(iRow, i) <> Cells(iRow, i - 1) Then
            iLastRow = i - 1
            'get first column letter
            vArr = Split(Cells(1, iFirstColumn).Address(True, False), "$")
            sFirstColumn = vArr(0)
            'get last column letter
            vArr = Split(Cells(1, i - 1).Address(True, False), "$")
            sLastColumn = vArr(0)
            sRange = sFirstColumn & iRow & ":" & sLastColumn & iRow
            Range(sRange).Select
                With Selection
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                    .WrapText = True
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = False
                End With
            Selection.Merge
            iFirstColumn = i
            End If
        Next i
          
    Application.DisplayAlerts = True
    End Sub
    

    public by fabio.nosenzo  3223  0  6  0

    Manage Workbooks Excel VBA

    Useful code to manage workbooks in MS Excel VBA
    ' how to open a workbook
    
    'how to activate a workbook 
    Workbooks(ThisWorkbook.Name).Activate
    
    'how to activate the workbook with the macro that is running
    Workbooks(ThisWorkbook.Name).Activate

    public by fabio.nosenzo  2803  0  5  0

    Excel VBA String Extraction

    Extract a string in Excel VBA from another string
    ' originary string
    Dim sString As String
    ' extracted string
    Dim sNewString As String
    ' starting index for extraction
    Dim lIndex As Long
    ' number of char to be extracted
    Dim iCharNum As Integer
    
    sNewString = Mid(sString, iIndex, iCharNum)
    
    
    • Public Snippets
    • Channels Snippets