Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff. 		Lista Forumurilor Pe Tematici
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login

POZE MRRRR'S FORUM (VIEW ONLY)

Nu sunteti logat. 		Nou pe simpatie:
georgyana92
Femeie
29 ani
Galati
cauta Barbat
29 - 62 ani
Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides / [EXCEL] VBA Cheat Sheets – Commands & Syntax Lists Moderat de TRaP, TonyTzu
Autor
Mesaj Pagini: 1
Mrrrr
AdMiN

Inregistrat: acum 17 ani
Postari: 2237
Source:

The "cheats" below are copy / pasted from the website above - all credit goes to automateexcel.com

Better check the site above, the quotes below are messed up.

Sheets



Activate by Tab Name                Sheets(“Input”).Activate
Activate by VBA Code Name            Sheet1.Activate
Activate by Index Position            Sheets(1).Activate
Next Sheet                    ActiveSheet.Next.Activate
Get ActiveSheet                    MsgBox ActiveSheet.Name
Select Sheet                    Sheets(“Input”).Select
Set to Variable                    Dim ws as Worksheet
                        Set ws = ActiveSheet
Name / Rename                    ActiveSheet.Name = “NewName”
Add Sheet                    Sheets.Add
Add Sheet and Name                Sheets.Add.Name = “NewSheet”
Add Sheet to Variable                Dim ws As Worksheet
                        Set ws = Sheets.Add
Copy Sheet                    Sheets(“Sheet1”).Copy Before:=Sheets(“Sheet2”)
Hide Sheet                    Sheets(“Sheet1”).visible = False
                    or     Sheets(“Sheet1”).visible = xlSheetHidden
Unhide Sheet                    Sheets(“Sheet1”).Visible = True
                    or    Sheets(“Sheet1”).Visible = xlSheetVisible
Very Hide Sheet                    Sheets(“Sheet1”).Visible = xlSheetVeryHidden
Delete Sheet                    Sheets(“Sheet1”).Delete
Clear Sheet                    Sheets(“Sheet1”).Cells.Clear
Unprotect (No Password)                Sheets(“Sheet1”).Unprotect
Unprotect (Password)                Sheets(“Sheet1”).Unprotect “Password”
Protect (No Password)                Sheets(“Sheet1”).Protect
Protect (Password)                Sheets(“Sheet1”).Protect “Password”
Protect but Allow VBA Access            Sheets(“Sheet1”).Protect UserInterfaceOnly:=True


Cells & Ranges



Activate Cell                Range(“B3”).Activate
                    Cells(3,2).Activate
Select Range                Range(“a1:a3”).Select
                    Range(Range(“a1”), Range(“a3”)).Select
                    Range(Cells(1, 1), Cells(3, 1)).Select
Resize                    Range(“B3”).Resize(2, 2).Select
Offset                    Range(“B3”).Offset(2, 2).Select
Copy                    Range(“A1:B3”).Copy Range(“D1”)
Cut                    Range(“A1:B3”).Cut Range(“D1”)
Delete                    Range(“A1:B3”).Delete
                    Range(“A1:B3”).Delete shift:=xlShiftToLeft
Clear                    Range(“A1:A3”).Clear
                    Range(“A1:A3”).ClearContents
                    Range(“A1:A3”).ClearFormat
Count                    Range(“A1:A3”).Count
Set to Variable                Dim rng as Range
                    Set rng = Range(“A1”)
Merge/UnMerge                Range(“A1:A3”).Merge
                    Range(“A1:A3”).UnMerge
Loop Through Cells            Dim cell As Range
                    ForEach cell In Range(“A1:C3”)
                    MsgBox cell.Value
                    Next cell


Rows



Activate            Rows(1).Activate
                Rows(“1:1”).Activate
                Range(“a1”).EntireRow.Activate
Height / Width            Range(“A1”).EntireRow.RowHeight = 30
Delete                Range(“A1”).EntireRow.Delete
Count                Range(“A1”).Rows.Count
Insert                Range(“A1”).EntireRow.Insert
Last                dim lRow as long
                lRow = Cells(Rows.Count, 1).End(xlUp).Row
Copy                Range(“1:1”).Copy Range(“5:5”)
Insert                Range(“1:1”).Copy
                Range(“5:5”).Insert


Columns



Activate            Columns(1).Activate
                Columns(“a:a”).Activate
                Range(“a1”).EntireColumn.Activate
Height / Width            Range(“A1”).EntireColumn.ColumnWidth = 30
Delete                Range(“A1”).EntireColumn.Delete
Count                Range(“A1”).Columns.Count
Insert                Range(“A1”).EntireColumn.Insert
Last                dim lCol as long
                lCol = Cells(1, Columns.Count).End(xlToLeft).Column
Copy                Range(“A:A”).Copy Range(“E:E”)
Insert                Range(“A:A”).Copy
                Range(“E:E”).Insert


Workbooks



Activate                Workbooks(“Book1”).Activate
Activate First Opened            Workbooks(1).Activate
Activate Last Opened            Workbooks(Workbooks.Count).Activate
Get ActivateWorkbook            MsgBox ActiveWorkbook.Name
Get ThisWorkbook (containing VBA Code)    MsgBox ThisWorkbook.Name

Add                    Workbooks.Add
Add to Variable                Dim wb As Workbook
                    Set wb = Workbooks.Add
Open                    Workbooks.Open(“C:\example.xlsm”)
Open to Variable            Dim wb As Workbook
                    Set wb = Workbooks.Open(“C:\example.xlsm”)
Close                    Workbooks(“Book1”).Close SaveChanges:=False
                    Workbooks(“Book1”).Close SaveChanges:=True
Save                    Workbooks(“Book1”).Save
Save As                    Workbooks(“Book1”).SaveAs strFileName
Protect/Unprotect            Workbooks(1).Protect “password”
                    Workbooks(1).Unprotect “password”
Set to Variable                Dim wb as Workbook
                    Set wb = Workbooks(“Book1”)
Loop Through All Workbook in Workbooks    Dim wb As Workbook
                    ForEach wb In Workbooks
                    MsgBox wb.Name
                    Next wb
Check Exists                If Dir(“C:\Book1.xlsx”) = “” Then
                    MsgBox “File does not exist.”
                    EndIf
Copy Closed                FileCopy “C:\file1.xlsx”,”C:\file2.xlsx”


Settings



Screen Updating            Application.ScreenUpdating = False
                Application.ScreenUpdating = True
Display Alerts            Application.DisplayAlerts = False
                Application.DisplayAlerts = True
Events                Application.EnableEvents = False
                Application.EnableEvents = True
Enable Cancel Key        Application.EnableCancelKey = xlDisabled
                Application.EnableCancelKey = xlInterrupt
Text Compare – Ignore Case    Option Compare Text
Require Variable Declaration    Option Explicit
Automatic Calculations        Application.Calculation = xlManual
                Application.Calculation = xlAutomatic
Background Error Checking    Application.ErrorCheckingOptions.BackgroundChecking = False
                Application.ErrorCheckingOptions.BackgroundChecking = True
Display Formula Bar        Application.DisplayFormulaBar = False
                Application.DisplayFormulaBar = True
Freeze Panes            ActiveWindow.FreezePanes = False
                ActiveWindow.FreezePanes = True
Full Screen View        Application.DisplayFullScreen = False
                Application.DisplayFullScreen = True
PageBreak Preview        ActiveWindow.View = xlPageBreakPreview
                ActiveWindow.View = xlNormalView
Display Scroll Bars        With ActiveWindow
                    .DisplayHorizontalScrollBar = False
                    .DisplayVerticalScrollBar = False
                End With
                With ActiveWindow
                    .DisplayHorizontalScrollBar = True
                    .DisplayVerticalScrollBar = True
                End With
Display Status Bar        Application.DisplayStatusBar = False
                Application.DisplayStatusBar = True
Status Bar Contents        Application.StatusBar = “I’m working Now!!!”
                Application.StatusBar = False
Display Workbook Tabs        ActiveWindow.DisplayWorkbookTabs = False
                ActiveWindow.DisplayWorkbookTabs = True
UserName            Application.UserName = “AutomateExcel.com”
App Caption            Application.Caption = “AutomateExcel Model”
Zoom                ActiveWindow.Zoom = 80


Errors



On Error – Stop code and display error        On Error Goto 0
On Error – Skip error and continue running    On Error Resume Next
On Error – Go to a line of code [Label]        On Error Goto [Label]
Clears (Resets) Error                On Error GoTo –1
Show Error number                MsgBox Err.Number
Show Description of error            MsgBox Err.Description
Function to generate own error            Err.Raise


Files



Copy File                 FileCopy “C:\test\test_old.xlsx”, “C:\test\test_new.xlsx”
Delete File                Kill “C:\test\example.xlsx”
Make Folder                MkDir “C:\test\”
Delete All Files From Folder        Kill “C:\test\” & “*.*”
Delete Folder                Kill “C:\test\” & “*.*”
                    RmDir “C:\test\”
Current Directory            strPath = CurDir()
ThisWorkbook Path            strPath = ThisWorkbook.Path
Loop Through All Files in Folder    strFile = Dir(“C:\test” & “\*”)
                    Sub Len(strFile) > 0
                   
                    Sub strFile
                    strFile = Dir
                    Sub


Arrays



Create            Dim arr(1 To 3) As Variant
            arr(1) = “one”
            arr(2) = “two”
            arr(3) = “three”
Create From Excel    Dim arr(1 To 3) As Variant
            Dim cell As Range, i As Integer

            i = LBound(arr)

            ForEach cell In Range(“A1:A3”)
            i = i + 1
            arr(i) = cell.value
            Next cell
Read All Items        Dim i as Long
            Fori = LBound(arr) To UBound(arr)
            MsgBox arr(i)
            Next i
Erase            Erase arr
Array to String        Dim sName As String
            sName = Join(arr, “:”)
Increase Size        ReDim Preserve arr(0 To 100)
Set Value        arr(1) = 22


Collections



Create            Dim coll As New Collection
            coll.Add “one”
            coll.Add “two”
Create From Excel    Dim coll As New Collection
            Dim cell As Range
            ForEach cell In Range(“A1:A2”)
            coll.Add cell.value
            Next cell
Add Item        coll.Add “Value”
Add Item Before        coll.Add “Value”, Before:=1
Add Item After        coll.Add “Value”, After:=1
Read Item        MsgBox coll (1)
Count Items        coll.Count
Read All Items        Dim item As Variant
            ForEach item In coll
            MsgBox item
            Next item
Remove Item        coll.Remove (1)
Remove All Items    Set coll = New Collection


Dictionaries



Required Reference        Tools > References > Microsoft Scripting Runtime
Create                Dim dict As New Scripting.Dictionary
                dict.Add “”
                dict.Add “”
Create From Excel        Dim dict As New Scripting.Dictionary
                Dim cell As Range
                Dim key As Integer
                ForEach cell In Range(“A1:A10”)
                key = key + 1
                dict.Add key, cell.value
                Next cell
Add Item            dict.Add “Key”, “Value”
Change Value            dict(“Key”) = “Value”
Get Value            MsgBox dict(“Key”)
Check For Value            If dict.Exists(“Key”) Then
                MsgBox “Exists”
                End If
Remove Item            dict.Remove (“Key”)
Remove All Items        dict.RemoveAll
Loop Through Items        Dim key As Variant
                ForEach key In dict.Keys
                MsgBox key, dict(key)
                Next key
Count Items            dict.Count
Make Key Case Sensitive        dict.CompareMode = vbBinaryCompare
Make Key Case Insensitive    dict.CompareMode = vbTextCompare


_______________________________________
pus acum 5 ani
   
Pagini: 1  

Mergi la