VBA primjeri - Popis najboljih 19 VBA primjera za početnike

Primjeri za Excel za početnike

Makronaredbe su vam najbolji prijatelj kada je riječ o povećanju vaše produktivnosti ili uštedi vremena na vašem radnom mjestu. Od malih do velikih zadataka, možemo automatizirati pomoću jezika kodiranja VBA. Znam da ste često pomislili na neka ograničenja koja Excel ima, ali s VBA kodiranjem možete sve to ukloniti. Ok, ako ste se u ovom članku borili s VBA-om i još uvijek ste početnik, dat ćemo neke korisne primjere VBA makronaredbe u Excelu.

Popis najboljih 19 primjera

  1. Ispis svih naziva listova
  2. Umetnite indeks različitih boja u VBA
  3. Umetnite serijski broj od vrha
  4. Umetnite serijski broj od dna
  5. Umetnite serijski broj od 10 do 1
  6. Umetnite radne listove koliko god želite
  7. Izbrišite sve prazne radne listove iz radne knjige
  8. Umetni prazan red nakon svakog drugog reda
  9. Istaknite pravopisnu pogrešku
  10. Promijeni sve u velika slova
  11. Promijeni sve u mala slova
  12. Označite sve komentirane stanice
  13. Označite Sve prazne stanice
  14. Sakrij sve listove osim jednog lista
  15. Otkrij sve listove
  16. Izbrišite sve datoteke u mapi
  17. Izbriši cijelu mapu
  18. Pronađite zadnji upotrijebljeni red u listu
  19. Pronađite zadnji korišteni stupac u listu

Pogledajmo svaki od ovih primjera detaljno.

# 1 - Ispis svih naziva listova

Kodirati:

Sub Print_Sheet_Names () Dim i kao cjelina za i = 1 na listove. Broj ćelija (i, 1) .Vrijednost = listovi (i) .Name Sljedeće i Završi pod

Ovo će izdvojiti sva imena listova u aktivni list.

# 2 - Umetnite indeks različitih boja u VBA

Kodirati:

Sub Insert_Different_Colours () Dim i As Integer for i = 1 To 56 Cells (i, 1) .Value = i Cells (i, 2) .Interior.ColorIndex = i Next End Sub

To će umetnuti brojeve od 1 do 56 i njihov indeks boja u sljedeći stupac.

# 3 - Umetnite serijski broj od vrha

Kodirati:

Sub Insert_Numbers_From_Top () Dim i As Integer for i = 1 to 10 Cells (i, 1) .Value = i Next i End Sub

Ovo će umetnuti serijske brojeve od 1 do 10 od vrha.

# 4 - Umetnite serijski broj od dna

Kodirati:

Sub Insert_Numbers_From_Bottom () Dim i As Integer for i = 20 to 1 Step -1 Cells (i, 7) .Value = i Next i End Sub

Ovo će umetnuti serijske brojeve od 1 do 20 od dna.

# 5 - Umetnite serijski broj od 10 do 1

Kodirati:

Sub Ten_To_One () Dim i As Integer Dim j As Integer j = 10 For i = 1 to 10 Range ("A" & i) .Value = jj = j - 1 Next i End Sub

Ovo će umetnuti serijske brojeve od 10 do 1 od vrha.

# 6 - Umetnite radne listove koliko god želite

Kodirati:

Sub AddSheets () Dim ShtCount As Integer, i As Integer ShtCount = Application.InputBox ("Koliko listova želite umetnuti?", "Add Sheets",,,,,, 1) If ShtCount = False then Exit Sub Else Za i = 1 na radne listove ShtCount.Dodaj Sljedeće i End If End Sub

Zatražit će se da unesete broj radnih listova koje biste željeli umetnuti. Samo navedite broj u okviru za unos i kliknite na Ok, on će odmah umetnuti toliko listova.

# 7 - Iz radne bilješke izbrišite sve prazne radne listove

Kodirati:

Pod Delete_Blank_Sheets () Dim ws as Worksheet Application.DisplayAlerts = False Application.ScreenUpdating = False za svaki ws u ActiveWorkbook.Worksheets If WorksheetFunction.CountA (ws.UsedRange) = 0 Zatim ws.Delete End If Next ws Application.DisplayAlerts = True. .ScreenUpdating = True End Sub

This will delete all the blank worksheets from the workbook we are working on.

#8 - Insert Blank Row After Every Other Row

Code:

Sub Insert_Row_After_Every_Other_Row() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow.Insert ActiveCell.Offset(2, 0).Select Next i End Sub

For this first, you need to select the range where you would like to insert alternative blank rows.

#9 - Highlight Spelling Mistake

Code:

Sub Chech_Spelling_Mistake() Dim MySelection As Range For Each MySelection In ActiveSheet.UsedRange If Not Application.CheckSpelling(Word:=MySelection.Text) Then MySelection.Interior.Color = vbRed End If Next MySelection End Sub

First, select the data and run the VBA code. It will highlight the cells which have spelling mistakes.

#10 - Change All To Upper Case Characters

Code:

Sub Change_All_To_UPPER_Case() Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

First, select the data and run the code. It will convert all the text values to upper case characters.

#11 - Change All To Lower Case Characters

Code:

Sub Change_All_To_LOWER_Case() Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = LCase(Rng.Value) End If Next Rng End Sub

First, select the data and run the code. It will convert all the text values to lower case characters in excel.

#12 - Highlight All the Commented Cells

Code:

Sub HighlightCellsWithCommentsInActiveWorksheet() ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments).Interior.ColorIndex = 4 End Sub

Result:

#13 - Highlight All the Blank Cells

Code:

Sub Highlight_Blank_Cells() Dim DataSet As Range Set DataSet = Selection DataSet.Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = vbGreen End Sub

First, select the data range and run the code. It will highlight all the blank cells with green color.

#14 - Hide All Sheets Except One Sheet

Code:

Sub Hide_All_Except_One() Dim Ws As Worksheet For Each Ws In ActiveWorkbook.Worksheets If Ws.Name "Main Sheet" Then Ws.Visible = xlSheetVeryHidden Next Ws End Sub

The above code hides all the sheets except the sheet named “Main Sheet.” You can change the worksheet name as per your wish.

#15 - Unhide All Sheets

Code:

Sub UnHide_All() Dim Ws As Worksheet For Each Ws In ActiveWorkbook.Worksheets Ws.Visible = xlSheetVisible Next Ws End Sub

This will unhide all the hidden sheets.

#16 - Delete All Files in the Folder

Code:

Sub Delete_All_Files() 'You can use this to delete all the files in the folder Test '' On Error Resume Next Kill "C:UsersAdmin_2.Dell-PcDesktopDelete Folder*.*" On Error GoTo 0 End Sub

Change the folder path, which is marked in red as per your folder deletion.

#17 - Delete Entire Folder

Code:

Sub Delete_Whole_Folder() 'You can use this to delete entire folder On Error Resume Next Kill "C:UsersAdmin_2.Dell-PcDesktopDelete Folder*.*" 'Firstly it will delete all the files in the folder 'Then below code will delete the entire folder if it is empty RmDir "C:UsersAdmin_2.Dell-PcDesktopDelete Folder " 'Note: RmDir delete only a empty folder On Error GoTo 0 End Sub

Change the folder path, which is marked in red as per your folder deletion.

#18 - Find the Last Used Row in the Sheet

Code:

Sub Last_Row () Dim LR as Long LR = Cells (Rows.Count, 1) .End (xlUp) .Row MsgBox LR End Sub

Ovdje pronalazimo zadnji korišteni red u listu

# 19 - Pronađite zadnji korišteni stupac u listu

Kodirati:

Sub Last_Column () Dim LC as Long LC = Cells (1, Columns.Count) .End (xlToLeft) .Column MsgBox LC End Sub

Ovdje nalazimo zadnji korišteni stupac u listu

Zanimljivi članci...