Top 13 # Xem Nhiều Nhất Xóa Object Trong Excel Mới Nhất 6/2023 # Top Like | Beiqthatgioi.com

26. Các Đối Tượng Trong Vba Excel (Excel Objects)

Khi dùng VBA trong excel bạn cần nhớ 4 đối tượng này:

Application Objects

Workbook Objects

Worksheet Objects

Range Objects

Đối tượng application trong Excel VBA là đối tượng được sử dụng thường xuyên nhất khi thực hiện bất kỳ tác vụ nào với VBA.

Các thuộc tính cơ bản trong VBA: Các phương thức (Events) cơ bản trong VBA:

– ScreenUpdating

Điều khiển cập nhật màn hình: Khi điều khiển các đối tượng (sheets, range, cells), việc vô hiệu hóa cập nhật màn hình (tránh nhấp nháy) giúp tốc độ code nhanh hơn.

– Calculation

Mặc định thiết lập bảng tính tự động tính toán (CalculationAutomatic), khi điều khiển các đối tượng (sheets, range, cells), ví dụ gán kết quả xuống bảng tính, các công thức thực hiện tính toán với giá trị vừa được gán, máy tính phải xử lý thêm tác vụ, làm giảm tốc độ code thực hiện lệnh. Vậy, để tăng tốc độ cho code ta sẽ chuyển thiết lập về dạng thủ công (CalculationManual).Xem 2 ví dụ để so sánh:

Ví dụ 1:

Sub ScreenAndCal_ON() 'Vô hiêu hóa câp nhât màn hình Application.ScreenUpdating = False 'Thiêt lâp tinh toan vê dang thu công Application.Calculation = xlCalculationManual Dim i As Long, T As Double 'Lây gôc thoi gian chay code: T = Timer 'Vòng lap gan sô thu tu: 1 - 100 000 For i = 1 To 100000 Sheet1.Range("A1").Offset(i, 0).Value = i Next i 'Câp nhât màn hình Application.ScreenUpdating = True 'Thiêt lâp tinh toan vê dang tu dông Application.Calculation = xlCalculationAutomatic 'Thoi gian hoàn thành: MsgBox Round(Timer - T, 2) & " giây" 'T=2.62 giây End Sub

Ví dụ 2:

Sub ScreenAndCal_OFF() Dim i As Long, T As Double 'Lây gôc thoi gian chay code: T = Timer 'Vòng lap gan sô thu tu: 1 - 100 000 For i = 1 To 100000 Sheet1.Range("A1").Offset(i, 0).Value = i Next i 'Thoi gian hoàn thành: MsgBox Round(Timer - T, 2) & " giây" 'T=3.12 giây End Sub

– DisplayAlerts

Điều khiển hộp thoại thông báo khi thực thi code.

– Khi đóng file, xuất hiện hộp thoại:

Code:

Sub Alert_Close() Application.DisplayAlerts = False ActiveWorkbook.Close Application.DisplayAlerts = True End Sub

khi đó, Excel đóng mà không lưu bảng tính, tương ứng với chọn “Don’t Save”

– WorksheetFunction

Sử dụng thuộc tính WorksheetFunction để gọi các hàm trong bảng tính.

Cú pháp:

Application.WorksheetFunction.Formula

Với Formula là một hàm trong bảng tính (sum, countA, Match…)

Ví dụ:

Sub Worksheet_Function() Dim WF As WorksheetFunction Set WF = Application.WorksheetFunction Dim aCount As Long MsgBox aCount 'Hoac viet gôp: Dim maxValue As Long maxValue = Application.WorksheetFunction.Max(Sheet1.Range("A2:A100")) MsgBox maxValue End Sub

– GetOpenFilename

Cú pháp:

Application.GetOpenFilename([FileFilter],[FilterIndex],[Title],[ButtonText],MultiSelect])

FileFilter: Chuỗi đưa ra điều kiện lọc loại tập tin trong cửa sổ chọn.

FilterIndex: Chỉ định chỉ số của loại tập tin mặc định được lọc.

Title: Tiêu đề của hộp thoại chọn tập tin, mặc định là “Open”.

ButtonText: Với MAC OS.

MultiSelect: True cho phép chọn nhiều tập tin, False (mặc định) chỉ cho chọn một.

Ví dụ:

Sub GetFileName_Any() Dim FilePath As String FilePath = Application.GetOpenFilename() MsgBox FilePath End Sub Sub GetFileName_Excel() Dim FilePath As String FilePath = Application.GetOpenFilename("Excel file (*.xlsx), *.xlsx") MsgBox FilePath 'Workbooks.Open (OpenFile) End Sub

2.Workbook Objects

Đối tượng Workbook trong Excel VBA là một trong những đối tượng được sử dụng thường xuyên nhất trong khi tự động hóa bất kỳ tác vụ nào với VBA. Bài này cung cấp các phương thức hay sử dụng của đối tượng Workbook.

Mỗi đối tượng Workbook tương ứng với một file excel.

Các phương thức của Workbook giúp chúng ta thực hiện các hành động khác nhau với Excel Workbooks. Ví dụ, chúng ta có thể Kích hoạt một Workbook và Xóa một Workbook hoặc Move Workbook. Và chúng ta cũng có thể Protect và UnProtect Workbooks.

Các phương thức (Events) cơ bản trong VBA :

Ví dụ cách khai báo:

'Ví dụ 1 : Đóng workbooks Workbooks.Close 'Ví dụ 2 : Thêm workbook mới Workbooks.Add 'Ví dụ 3 : Mở workbook Workbooks.Open FileName:="Test.xls", ReadOnly:=True 'Ví dụ 4 : Activate workbook Workbooks("Test.xls").Worksheets("Sheet1").Activate 'Ví dụ 5 : Save workbook Workbooks("Workbook Name").Save 'Ví dụ 6 : Save as workbook Dim wb As Workbook Set wb = Workbooks.Add wb.SaveAs Filename:="D:testSample.xlsx" 'Ví dụ 7 : SaveCopyAs Workbook Workbooks("Workbook Name").Save 'Ví dụ 8 : SaveCopyAs Workbook ThisWorkbook.SaveCopyAs chúng tôi & "" & "ver1_" & ThisWorkbook.Name

3.Worksheet Objects

Đối tượng Worksheet trong Excel VBA là một trong những đối tượng được sử dụng thường xuyên nhất trong khi tự động hoá các tác vụ với VBA.

Đối tượng Worksheet đại diện cho các sheet trong Workbook, tức là mỗi Workbook chứa một hoặc nhiều Worksheet.

Các phương thức (Events) cơ bản trong VBA: Ví dụ cách khai báo trong VBA:

vd 1 : ẩn worksheet Worksheets(1).Visible = False 'vd 2 : Đặt mật khẩu cho WorkSheet Worksheets("Sheet1").Protect password:=strPassword, scenarios:=True 'vd 3: Active sheet Worksheets("data").Activate 'Or Sheets("data").Activate 'vd 4: Copy Worksheet trong VBA với Before Worksheets("Sheet3").Copy Before:=Worksheets(1) 'vd 5: Copy Worksheet trong VBA với After Worksheets("Sheet3").Copy After:=Worksheets(Worksheets.Count) 'vd 6: Xóa Worksheet Sheets("Sheet2").Delete ' or Sheet2.Delete 'vd 7: Xóa Worksheet trong VBA - không hiển thị alert Application.DisplayAlerts = False Sheets("Sheet2").Delete Application.DisplayAlerts = True

4.Range Objects

Đối tượng Range là đại diện của một cell (hoặc cells) trên Worksheet, là đối tượng quan trọng nhất của Excel VBA.

Ví dụ:

'vd 1 : Đưa dữ liệu vào ô A5 Worksheets("Sheet1").Range("A5").Value = "5235" 'vd 2 : Đưa dữ liệu vào range A1:A4 Worksheets("Sheet1").Range("A1:A4").Value = 5 'vd 3 : Khai báo một range và set giá trị bằng 8 Dim rangeObj As Range Set rangeObj = Range("A1:C4") rangeObj .Value = 8

Các phương thức (Events) cơ bản trong VBA:

Delete Or Hide Objects/Controls On A Worksheet

Delete or Hide Objects/Controls on a worksheet

Shapes collection

Members of the Shapes collection are:

1. ActiveX controls (Control Toolbox) or a linked or embedded OLE objects2. Controls from the Forms toolbar3. Controls from the Drawing toolbar4. Pictures, charts, ………………

You see that all objects/controls are a member of the Shapes collection.

Below you find examples to delete or hide the members of this collection.

Tip: if you only want to hide all shapes for a moment then you can use the toggle shortcut Ctrl 6 (This is for the whole workbook)

Manual delete shapes

Note: for Activex(control toolbox) controls you must be in “Design Mode” in Excel 97-2003. Use the first button on the Control toolbox toolbar to toggle this mode.

With VBA code

Delete all shapes

Use this macro to delete all shapes on the worksheet, working in all Excel versions(also in 2007).

Sub Shapes1() 'Delete all Objects except Comments On Error Resume Next ActiveSheet.DrawingObjects.Visible = True ActiveSheet.DrawingObjects.Delete On Error GoTo 0 End Sub Sub Comments() ActiveSheet.Cells.ClearComments End Sub Sub NotUseThisMacro() 'Delete every shape in the Shapes collection Dim myshape As Shape For Each myshape In ActiveSheet.Shapes myshape.Delete Next myshape End Sub

Delete only specific shapes

What if you only want to delete control toolbox controls, Pictures or forms controls.You can loop through the collection and check the Type of the control.

12 = ActiveX control (control toolbox) or a linked or embedded OLE object.13 = Picture 8 = Forms controls

For Type 8 we use another macro to avoid the problem of losing AutoFilter and Data Validation dropdowns on your chúng tôi the example in this section “Delete only Forms controls”

Sub Shapes2() 'Loop through the Shapes collection and use the Type number of the control Dim myshape As Shape For Each myshape In ActiveSheet.Shapes ' ActiveX control (control toolbox) or a linked or embedded OLE object If chúng tôi = 12 Then myshape.Delete ' You can also use myshape.Visible = False Next myshape End Sub

If you want to know all the Type numbers of all controls on your worksheet you can run this macro to add a new worksheet with the names and Type numbers of all objects on your worksheet.You can find the number then that you must use in the code to delete the objects you want.

Sub ListAllObjectsActiveSheet() Dim NewSheet As Worksheet Dim MySheet As Worksheet Dim myshape As Shape Dim I As Long Set MySheet = ActiveSheet Set NewSheet = Worksheets.Add With NewSheet .Range("A1").Value = "Name" .Range("B1").Value = "Visible(-1) or Not Visible(0)" .Range("C1").Value = "Shape type" I = 2 For Each myshape In MySheet.Shapes .Cells(I, 1).Value = myshape.Name .Cells(I, 2).Value = myshape.Visible .Cells(I, 3).Value = myshape.Type I = I + 1 Next myshape .Range("A1:C1").Font.Bold = True .Columns.AutoFit .Range("A1:C" & Rows.Count).Sort Key1:=Range("C1"), _ Order1:=xlAscending, Header:=xlYes End With End Sub

Delete only Forms controls

This example avoid the problem of losing AutoFilter and Data Validation dropdowns on your worksheet when you use Type 8.

Sub Shapes4() 'Dave Peterson and Bob Phillips 'Example only for the Forms controls Dim shp As Shape Dim testStr As String For Each shp In ActiveSheet.Shapes If chúng tôi = 8 Then If shp.FormControlType = 2 Then testStr = "" On Error Resume Next testStr = shp.TopLeftCell.Address On Error GoTo 0 Else shp.Delete End If End If Next shp End Sub

In the workaround macro above we use FormControlType = 2 in the loop (xlDropDown). AutoFilter and Data Validation dropdowns do not have TopLeftCell.Address and the macro will not delete this DropDowns.

Other FormControl constants are:(only for the Forms controls)

xlButtonControl = 0xlCheckBox = 1xlDropDown = 2xlEditBox = 3 xlGroupBox = 4xlLabel = 5xlListBox = 6xlOptionButton = 7 xlScrollBar = 8xlSpinner = 9

Delete or Hide one shapeBecause all objects/controls are a member of the shapes collection we can use this to delete or hide one button, picture or ?

Sub Delete_One_Shape() ActiveSheet.Shapes("YourShapeName").Delete End Sub Sub Hide_One_Shape() ActiveSheet.Shapes("YourShapeName").Visible = False End Sub

Specific examples for Activex(control toolbox) or Forms controls

For most things the macros in the first section of this page are Ok but if you only want to delete Forms buttons or ActiveX buttons then look here for a few examples.

ActiveX controls (Control Toolbox) or linked or embedded OLE objects

Sub OLEObjects1() 'Hide all ActiveX controls(Control Toolbox)or linked or embedded OLE objects On Error Resume Next ActiveSheet.OLEObjects.Visible = False On Error GoTo 0 End Sub Sub OLEObjects2() 'Delete all ActiveX controls(Control Toolbox)or linked or embedded OLE objects On Error Resume Next ActiveSheet.OLEObjects.Visible = True ActiveSheet.OLEObjects.Delete On Error GoTo 0 End Sub Sub OLEObjects3() 'Delete/hide only all CommandButtons from the Control Toolbox Dim obj As OLEObject For Each obj In ActiveSheet.OLEObjects If TypeOf obj.Object Is MSForms.CommandButton Then obj.Delete ' or obj.Visible = False if you want to hide them End If Next End Sub

Others are :

MSForms.CheckBox MSForms.TextBox MSForms.OptionButtonMSForms.ListBoxMSForms.ComboBox MSForms.ToggleButtonMSForms.SpinButton MSForms.ScrollBar MSForms.LabelMSForms.Image

Sub OLEObjects4() 'Hide one ActiveX control(Control Toolbox)or a linked or embedded OLE object ActiveSheet.OLEObjects("CommandButton1").Visible = False End Sub Sub OLEObjects5() 'Delete one ActiveX control(Control Toolbox)or a linked or embedded OLE object ActiveSheet.OLEObjects("CommandButton1").Delete End Sub

Because Control Toolbox controls are also a member of the Shapes collection you can also use this :

Sub OLEObjects6() 'Hide one Control Toolbox button or Control ActiveSheet.Shapes("CommandButton1").Visible = False End Sub Sub OLEObjects7() 'Delete one Control Toolbox button or Control ActiveSheet.Shapes("CommandButton1").Delete End Sub

To clear textboxes or uncheck checkboxes you can use code like this :

Sub TestMe() Dim obj As OLEObject For Each obj In ActiveSheet.OLEObjects If TypeOf obj.Object Is MSForms.TextBox Then chúng tôi = "" End If If TypeOf obj.Object Is MSForms.CheckBox Then obj.Object.Value = False End If Next End Sub

Forms controls

Sub Forms1() 'Delete All Forms buttons ActiveSheet.Buttons.Delete End Sub Sub Forms2() 'Hide All Forms buttons ActiveSheet.Buttons.Visible = False End Sub Sub Forms3() 'Delete one Forms button ActiveSheet.Buttons("Button 1").Delete End Sub Sub Forms4() 'Hide one Forms button ActiveSheet.Buttons("Button 1").Visible = False End Sub

Instead of Buttons you can also use

OptionButtonsCheckBoxes DropDowns

Because Forms controls are also a member of the Shapes collection you can also use this

Sub Forms5() 'One Forms button or Control ActiveSheet.Shapes("Button 1").Delete End Sub Sub Forms6() 'One Forms button or Control ActiveSheet.Shapes("Button 1").Visible = False End Sub

Cách Xóa Dòng Kẻ Trong Excel

Ẩn, hiện đường lưới Gridlines bằng lệnh View

Excel có công cụ lựa chọn dể bạn ẩn hiện đường lưới Grinlines cho toàn bộ worksheet của Excel. Bạn cài đặt theo các cách sau.

Chỉnh cài đặt Options của Excel để xóa đường lưới, cài đặt mầu đường lưới

Để chỉnh mầu cho đường lưới của Excel, bạn làm theo các bước sau:

Bước 1: Vào File, chọn  Options của Excel.

Bước 2: Chọn mục Advanced (1). Sử dụng thanh trượt tìm mục Display options for this worksheet (2). Chọn Worksheet mà bạn muốn cài đặt (3). Chọn dấu tích ở mục Show Gridlines (4) để hiện đường lưới và bỏ chọn nếu muốn ẩn đường lưới. Để chọn mầu cho đường lưới thì bạn phải nhớ chọn mục tích Show Gridlines, rồi chọn mầu ở mục Gridlines color (5). Sau khi chọn xong mầu mà bạn thích, bạn nhấn nút OK (6) để cài đặt và quay trở lại Worksheet.

Hình ảnh dưới là Thủ Thuật 123 đã đổi đường lưới của Sheet Doanh Thu sang mầu đỏ.

Sử dụng công cụ Fill để ẩn hiện đường lưới các ô đã chọn

Thay vì muốn bỏ chọn đường lưới cho toàn bộ bảng tính, bạn chỉ muốn ẩn đường lưới cho một số ô mà bạn cần. Vậy bạn có thể sử dụng công cụ Fill (bôi mầu) các ô mầu trắng. Ví dụ bạn có bảng tính sau và cần ẩn đường lưới cho các ô từ A3:F12.

Để hiện lại dòng kẻ lưới, bạn làm theo các bước ở trên và thay vì bôi mầu nền trắng bạn chọn No Fill. Bảng tính sẽ trở về như mặc định ban đầu.

Cách Xóa Pivottable Trong Excel

Khi PivotTable trở nên không cần thiết, đơn giản bạn hãy xóa đi. Nhưng một số trường hợp gặp khó khăn khi thao tác, chẳng hạn như “không bấm được vào vùng PivotTable để xóa” hoặc “PivotTable trùng vùng”. Hãy tìm hiểu cách loại bỏ PivotTable bằng chuột

Xóa PivotTable khi làm việc offline

Để xóa vùng PivotTable ta chỉ cần chọn toàn bộ Pivot Table rồi nhấn phím Delete.

Trong trường hợp bạn muốn loại bỏ dữ liệu Pivot Table nhưng giữ lại vùng trống, có thể loại bỏ PivotTable như sau:

Chọn một ô bất kỳ trong PivotTable

Xóa PivotTable khi làm việc online

Để loại bỏ PivotTable khi làm việc trên Excel Online, bạn làm tương tự bằng cách chọn toàn bộ PivotTable sau đó ấn Delete.

Lỗi thường gặp

Khi loại bỏ PivotTable, ta có thể bắt gặp trường hợp sau:

Đây là lỗi xảy ra khi bạn không chọn hết vùng cần loại bỏ. Khi đó, Excel sẽ tự động hiểu bạn chọn vùng đằng sau Pivot chứ không phải Pivot. Để khắc phục, chọn lại toàn bộ vùng sau đó ấn Delete

Lập báo cáo theo nhiều điều kiện bằng PivotTable

Tự động cập nhật PivotTable bằng VBA

Đánh giá bài viết này