Bạn đang xem bài viết 20.Hàm Xử Lý Date() Và Time() Trong Vba – Blog Chia Sẽ Kiến Thức Học Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Các hàm xử lý Date và Time trong VBA giúp các nhà phát triển chuyển đổi ngày và thời gian từ một định dạng này sang định dạng khác hoặc để lấy ra giá trị ngày hoặc giờ theo định dạng phù hợp.
Các hàm xử lý Date
No.Hàm & Mô tả1Datetrả về ngày hệ thống hiện tại.2CDatechuyển đổi một chuỗi đầu vào cho trước thành kiểu Date.3DateAddtrả về một ngày mà một khoảng thời gian xác định đã được thêm vào.4DateDifftrả về sự khác biệt giữa hai khoảng thời gian.5DateParttrả về một phần quy định của giá trị date đã cho.6DateSerialtrả về một ngày hợp lệ cho một năm, tháng và ngày cho trước.7FormatDateTimeđịnh dạng ngày dựa trên các thông số được cung cấp.8IsDatetrả về một giá trị Boolean tham số được cung cấp có phải là một date hay không.9Daytrả về một số nguyên giữa 1 và 31 đại diện cho ngày của date đã cho.10Monthtrả về một số nguyên giữa 1 và 12 đại diện cho tháng của date đã cho.11Yeartrả về một số nguyên đại diện cho năm của date đã cho.12MonthNametrả về tên của tháng cụ thể cho date đã cho.13WeekDaytrả lại một số nguyên (1 đến 7) đại diện cho ngày trong tuần cho date đã cho.14WeekDayNametrả về tên ngày trong tuần cho date đã cho.
Các hàm xử lý Time
No.Hàm & Mô tả1Nowtrả về ngày và giờ hệ thống hiện tại.2Hourtrả về một số nguyên giữa 0 và 23 đại diện cho phần giờ của time đã cho.3Minutetrả về một số nguyên từ 0 đến 59 đại diện cho phần phút của time đã cho.4Secondtrả về một số nguyên giữa 0 và 59 đại diện cho phần giây của time đã cho.5Timetrả về thời gian hệ thống hiện tại.6Timertrả lại số giây kể từ 12:00 AM.7TimeSerialđịnh dạng thời gian dựa trên các thông số được cung cấp.8TimeValuechuyển đổi chuỗi đầu vào sang định dạng thời gian.
26. Các Đối Tượng Trong Vba Excel (Excel Objects) – Blog Chia Sẽ Kiến Thức Học Excel
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:
PropertiesMô tảDisplayAlertsĐể đại diện cho hiển thị cảnh báo.PathĐể có được đường dẫn tuyệt đối của ứng dụng.ScreenUpdatingĐể bật/tắt màn hình.
Các phương thức (Events) cơ bản trong VBA:
Phương thứcMô tảFindFileĐể mở hộp thoại để mở một file.GotoĐể chọn bất kỳ range nào.RunĐể chạy một thủ tục hoặc một hàmWaitĐể tạm ngưng chương trình macro.
– 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 SubVí 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 Subkhi đó, 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.FormulaVớ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 :
Phương thứcMô tảActivateĐể kích hoạt một Workbook.CalculateĐể làm mới tất cả tính toán trong một Workbook.CloseĐể close một Workbook.SaveĐể save một Workbook.SaveAsĐể SaveAs một Workbook.SaveCopyAsĐể SaveCopyAs một Workbook.
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:
Phương thứcMô tảActivateĐể kích hoạt một Worksheet.CalculateĐể làm mới tất cả tính toán trong một Worksheet.CopyĐể copy một Worksheet.DeleteĐể xóa một Worksheet.MoveĐể di chuyển một Worksheet.SelectĐể chọn một Worksheet.
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 = 8Các phương thức (Events) cơ bản trong VBA:
Bài 15 – Sắp Xếp Và Lọc Dữ Liệu – Blog Chia Sẽ Kiến Thức Học Excel
1. Sắp xếp dữ liệu
Để sắp xếp dữ liệu bạn thực hiện theo các bước sau đây:
Yêu cầu cần sắp xếp theo Họ tên bạn nhấp chọn vào ô bất kỳ trong cột này.
Bước 2: Từ thanh công cụ Ribbon nhấp chọn Tab Data sau đó tìm tới nhóm Sort & Filter.
Trong nhóm này bạn có thể chọn biểu tượng Sort A to Z để sắp xếp dữ liệu giảm dần, hoặc chọn biểu tượng Sort Z to A để sắp xếp dữ liệu tăng dần. Ngoài ra bạn cũng có thể chọn nút Custum Sort để có nhiều tùy chọn sắp xếp hơn.
Để bổ xung các điều kiện sắp xếp bạn nhấp chọn nút Add Level sau đó chọn trường cần lọc trong ô Sort By tiếp theo là kiểu sắp xếp trong ô Order.
2. Lọc dữ liệu
Lọc là cách dễ dàng và nhanh chóng để tìm và làm việc với dữ liệu trong vùng dữ liệu được chọn. Vùng dữ liệu được lọc hiển thị các mẫu tin đáp ứng tiêu chuẩn mà bạn chỉ định cho một trường. Excel cung cấp hai lệnh: AutoFilter dùng để lọc tự động với tiêu chuẩn đơn giản và Advanced Filter dùng để lọc cho tiêu chuẩn phức tạp hơn.
Việc lọc không giống việc sắp xếp, lọc mẫu tin có tác dụng che tạm thời các mẫu tin mà bạn không muốn hiển thị và nó không sắp xếp trật tự các mẫu tin đó. Khi đã lọc các mẫu tin, bạn có thể chỉnh sửa, định dạng các mẫu tin này trong cơ sở dữ liệu mà không ảnh hưởng đến các mẫu tin khác.
Lọc tự động bằng AutoFilter
Lọc tự động là cách lọc đơn giản và nhanh nhất. Trong đó bạn có thể chọn những tiêu chuẩn do Excel phát hiện thấy có trong trường dữ liệu hoặc bạn cũng có thể sử dụng các câu lệnh đi theo nó. Các bước thực hiện như sau:
– Chọn tới vùng dữ liệu cần lọc – Từ thanh công cụ Ribbon nhấp chọn Tab Data tìm tới nhóm lệnh Sort & Filter. Khi đó trên vùng dữ liệu ở dòng đầu tiên mỗi ô sẽ xuất hiện một biểu tượng hình tam giác nhỏ phía góc phải.
– Bây giờ muốn lọc theo cột nào bạn nhấp chọn biểu tượng hình tam giác
+ Sort A to Z: Sắp xếp dữ liệu tăng dần
+ Sort Z to A: Sắp xếp dữ liệu giảm dần
+ Sort by Color: Sắp xếp dữ liệu theo mầu nền của Cell.
Khi bạn di chuyển chuột đến mục này một menu nhanh xuất hiện bên trái liệt kê các mầu nền của Cell có trong cột hiện thời bạn có thể chọn một mầu bất kỳ nào đó để sắp xếp dữ liệu theo mầu đó.
+ Filter by Color: Lọc dữ liệu theo mầu nền của Cell
Khi bạn di chuyển chuột đến mục này một menu nhanh xuất hiện bên trái liệt kê các mầu nền của Cell có trong cột hiện thời bạn có thể chọn một mầu bất kỳ nào đó để lọc dữ liệu theo mầu đó.
Lưu ý:
+ Trong trường hợp bạn muốn lọc ra những Cell không có mầu nền bạn chọn mục No Fill.
+ Nếu muốn hủy giá trị lọc bạn nhấp chọn lại mục đã được đánh dấu lọc trước đó.
– Text Filters: Lọc dữ liệu dạng Text
Bạn nhập giá trị cần tìm ở hộp thoại đầu tiên bên tay trái, giả sử chúng ta muốn tìm nhân viên có họ tên là “Hoàng Thiên Mệnh”.
Lưu ý: Ngoài việc ngõ trực tiếp giá trị cần lọc bạn cũng có thể nhấp vào ô hình tam giác cuối ô nhập này để chọn tính xác giá trị cần lọc.
Vẫn với danh sách trước chúng ta muốn lọc ra những nhân viên có họ tên bắt đầu là “Nguyễn” bạn thiết lập thông số như sau:
+ Begins With…: Lọc dữ liệu bắt đầu với… Sau khi nhấp chọn mục này hộp
Vẫn với danh sách trước chúng ta muốn lọc ra những nhân viên có họ tên khác “Lê Viết Bằng” bạn thiết lập thông số như sau:
+ End With…: Lọc dữ liệu kết thúc với… Sau khi nhấp chọn mục này hộp
Vẫn với danh sách trước chúng ta muốn lọc ra những nhân viên có họ tên kết thúc bằng “Tâm” bạn thiết lập thông số như sau:
Giả sử cần lọc ra những nhân viên có tên không chứa từ “Văn” bạn thiết lập giá trị cho hộp thoại như sau:
Thực chất tất cả các lựa chọn trên đều nằm trong hai hộp chọn đầu tiền bên tay trái.
Đây còn gọi là các điều kiện lọc, tuy nhiên với những gì đã hướng dẫn mỗi lần lọc chúng ta chỉ có thể lấy dữ liệu ra với một điều kiện (bằng, không bằng, chứa, không chứa,…). Trong phần này chúng ta sẽ học cách lọc dữ liệu với hai điều kiện kết hợp nhau giả sử như tên chứa từ “Thị” và kết thúc bằng “Thanh”, hay tên không chưa từ “Tâm” hoặc chứa từ “Tiến”. Để làm được điều này
Giả sử chúng ta lọc những nhân viên có họ là “Nguyễn” nhưng tên khác “Lâm”.
– Trong phần tiếp theo: Microsoft Excel 2013 cung cấp cho chúng ta các tùy chọn khác
Hoặc có thể nhấp chọn (Select All) để chọn tất cả. Cuối cùng nhấp Ok để hoàn tất.
Lọc nâng cao Advanced Filter
Các bước thực hiện như sau:
Bước 1: Xác định vùng dữ liệu cần lọc, ở đây vùng dữ liệu của chúng ta từ ô B4 đến I17 (B4:I17).
đến kết quả dữ liệu được lọc ra. Với lọc nâng cao chúng ta sẽ có những điều kiện lọc sau đây:
Vùng điều kiện lọc bắt đầu từ ô L4 đến ô L7 (L4:L7), nếu để ý bạn sẽ thấy tên cột trùng với tên cột trong bảng dữ liệu cần lọc, tốt nhất bạn nên Copy từ bảng dữ liệu cần lọc để đảm bảo tính chính xác. Tiếp theo là tên những người cần lọc được viết trên các dòng tiếp theo.
– Hai hay nhiều cột thỏa mãn điều kiện: Giả sử chúng ta cần tìm những nhân viên có tên là “Minh” làm việc “23” ngày công và có phụ cấp lớn hơn 100.000 vnđ.
– Thảo mãn một điều kiện ở một trong các cột: Giả sử chúng ta cần tìm vnđ, hoặc có ngày công =26.
– Hai hay cột đều phải thảo mãn một hoặc nhiều danh sách điều kiện cho trước ứng với từng cột. Giả sử chúng ta cần lọc những nhân viên có Lương cơ bản >1.000.000 và thực lĩnh >1.200.000 hoặc những nhân viên có lương cơ bản <900.000 và thực lĩnh <900.000.
Bước 3: Thực hiện lọc dữ liệu
Từ thanh Ribbon nhấp chọn Tab nhóm Data tìm tới nhóm lệnh Sort & Filter.
– Filter the List, In- Place: Cho phép bạn đặt các mẫu tin được lọc (các mẫu tin thoả mãn điều kiện) ngay trên cơ sở dữ liệu cũ, và tạm thời che các mẫu tin khác không thoả điều kiện trong cơ sở dữ liệu cũ.
– Copy to Another Location: Cho phép đặt các mẫu tin được lọc (các mẫu tin thoả điều kiện) ở một nơi khác và nơi này được chỉ định trong hộp Copy to.
– List Range: Chứa vùng dữ liệu tham gia vào để lọc trong cơ sở dữ liệu.
Bạn có thể điều chỉnh địa chỉ ô trong hộp này để giới hạn các mẫu tin tham gia vào để lọc bằng hai cách sau:
+ Nhấp chuột vào hộp List Range để chèn con nháy và nhập địa chỉ tuyệt đối ô vào khung từ bàn phím.
+ Nhấp chuột vào hộp List Range để chèn con nháy, di chuyển chuột đến góc trên bên trái của mẫu tin đầu tham gia vào để lọc, nhấp chuột trái rê xuống góc dưới bên phải của mẫu tin cuối tham gia vào để lọc trong cơ sở dữ liệu, thả chuột. Vùng được chọn sẽ có viền đen trắng chạy xung quanh.
– Hộp Criteria Range: Chứa địa chỉ tuyệt đối ô của vùng tiêu chuẩn hay còn gọi là vùng chứa các điều kiện. Bạn cần phải điền rõ địa chỉ khối ô nào chứa điều kiện để Excel căn cứ cào các điều kiện đó mà lọc ra những mẫu tin theo đúng yêu cầu.
+ Nhấp chuột vào hộp Criteria Range để chèn con nháy và nhập địa chỉ tuyệt đối ô chứa điều kiện vào khung từ bàn phím.
+ Nhấp chuột vào hộp Criteria Range để chèn con nháy, di chuyển chuột đến góc trên bên trái của đầu khối ô chứa điều kiện, nhấp chuột trái rê xuống góc dưới bên phải của cuối khối ô chứa điều kiện trong cơ sở dữ liệu, thả chuột. Vùng được chọn có viền đen trắng chạy xung quanh.
– Copy to: Nếu chọn chức năng Copy to Anther Location ở phần trên, thì hộp Copy to cho phép bạn chỉ định địa chỉ ô để chứa các mẫu tin được lọc.
– Unique Records Only: Nếu chọn hộp kiểm này thì Excel cho phép hiển thị một mẫu tin duy nhất trong các mẫu tin giống nhau. Ngược lại không chọn thì Excel cho hiển thị hết tất cả các mẫu tin giống nhau.
– Lựa chọn các chức năng trong hộp thoại xong, nhấp OK để thực hiện
Lưu ý: Muốn bỏ điều kiện lọc để hiển thị toàn bộ dữ liệu bạn nhấp chọn nút Clear.
40 Code Macro Vba Excel Thông Dụng Và Dễ Hiểu Có Thể Bạn Chưa Biết – Blog Chia Sẽ Kiến Thức Học Excel
Tổng hợp các 40 code macro vba thông dụng sẽ hữu ích cho những ai đang học tập và làm việc với excel:
1.Thêm dòng vào worksheet
Sub insertRowInWorksheet() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Rows(4).Insert 'chèn nhiều hơn 1 hàng bắt đầu từ một cụ thể 'được chèn bắt đầu từ hàng 3 'hàng thứ 3 hiện tại sẽ được chuyển sang vị trí thứ 6 sh.Rows("3:5").EntireRow.Insert 'chèn hàng bên dưới ô đã chọn ActiveCell.Rows.Insert ' Lệnh chèn End Sub
2.Chèn cột vào worksheet
Sub insertColumnInWorksheet() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) ' chèn vào cột chỉ định sh.Columns(B).Insert 'chèn nhiều hơn 1 cột bắt đầu từ một cụ thể 'được chèn bắt đầu từ cột A sh.Columns("A:C").Insert End Sub
3.Xóa một dòng trong worksheet
Sub deleteRowInWorksheet() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersvmishraDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Rows(2).Delete 'chèn nhiều hơn 1 cột bắt đầu từ một cụ thể 'được chèn bắt đầu từ cột A sh.Rows("3:5").Delete End Sub
4.Xoa cột trong worksheet
Sub deleteColumnInWorksheet() End Sub Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Columns(B).Delete 'chèn nhiều hơn 1 cột bắt đầu từ một cụ thể 'được chèn bắt đầu từ cột A sh.Columns("A:C").Delete End Sub
5.Ẩn dòng trong worksheet
Sub hideRowInWorksheet() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Rows(2).Hidden = True 'chèn nhiều hơn 1 cột bắt đầu từ một cụ thể 'được chèn bắt đầu từ cột A sh.Rows("3:5").Hidden = True End Sub
6.Ẩn cột trong worksheet
Sub hideColumnInWorksheet() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Columns("C").Hidden = True sh.Columns("A:C").Hidden = True End Sub
7.Hiện dòng trong worksheet bị ẩn
Sub unhideRowInWorksheet() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersvmishraDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Rows(2).Hidden = False 'chèn nhiều hơn 1 cột bắt đầu từ một cụ thể 'được chèn bắt đầu từ cột A sh.Rows("3:5").Hidden = False End Sub
8.Hiện cột ẩn trong worksheet
Sub unhideColumnInWorksheet() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Columns("C").Hidden = False 'chèn nhiều hơn 1 cột bắt đầu từ một cụ thể 'được chèn bắt đầu từ cột A sh.Columns("A:C").Hidden = False End Sub
9.Copy và dáng trước hàng chỉ định
Sub CopyAndInsertCopiedRow() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) ' copy dong 2 va inset dong 10 sh.Rows(2).EntireRow.Copy sh.Rows(10).Insert 'sao chép nhiều hơn một hàng và chèn tất cả 'tại một hàng cụ thể 'Sao chép các hàng từ 2 đến 5 và dán chúng 'trên hàng thứ 10. Excel sẽ tự động mặc định 'dịch chuyển nhiều hàng xuống sh.Rows("2:5").EntireRow.Copy sh.Rows(10).Insert End Sub
10.Copy và dáng trước cột chỉ định
Sub CopyAndInsertCopiedColumn() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) ' Copy A và dáng ở cột D sh.Columns("A").EntireColumn.Copy sh.Columns("D").Insert 'sao chép nhiều hơn một cột và chèn tất cả 'tại một cột cụ thể 'Ví dụ: Sao chép các cột từ A đến D và dán chúng 'trên cột F. Excel sẽ tự động mặc định 'thay đổi nhiều cột đúng sh.Columns("A:D").EntireColumn.Copy sh.Columns("F").Insert End Sub
11.Bảo vệ worksheet mà không cần password
Sub protectSheetWithoutPassword() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Protect End Sub
12.Đặt mật khẩu cho worksheet
Sub protectSheetWithPassword() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Protect Password:="password123" End Sub
13.Bỏ bảo vệ worksheet không đặt password
Sub unprotectSheetWithoutPassword() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Unprotect End Sub
14.Bỏ bảo vệ worksheet khi có password
Sub unprotectSheetWithPassword() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Unprotect Password:="password123" End Sub
15.Đặt mật khẩu cho workbook:
Sub protectWorkbookWithPassword() Dim wb As Workbook Dim fPath As String Dim newFileName As String fPath = "C:UsersadminDesktopmyfile.xlsx" newFileName = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) wb.SaveAs Filename:=newFileName, Password:="password123" End Sub
16.Mở một workbook đã đặt mật khẩu:
Sub OpenProtectedWorkbookWithPassword() Dim wb As Workbook Dim fPath As String fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath, Password:="password123") End Sub
17.xóa nội dung range vẫn giữ format
Sub ClearContentOfRangeWithoutClearingFormatting() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Range("A1:X5").ClearContents End Sub
18.Xóa content và format của vùng dữ liệu
Sub ClearContentAndFormatting() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Range("A1:P27").Clear End Sub
19.Xóa nội dung của worksheet
Sub ClearContentOfWorksheet() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.UsedRange.Clear sh.UsedRange.ClearContents End Sub
20.Xóa tất cả commnet
Sub ClearAllComments() Dim wb As Workbook Dim fPath As String Dim sh As Worksheet fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Range("A1:P27").ClearComments End Sub
21.Tạo mới workbook
Sub CreateNewExcelWorkbook() Dim wb As Workbook Set wb = Workbooks.Add 'nhập vào đường dẫn file muốn tạo wb.SaveAs "C:abctemp.xlsx" End Sub
22.Mở workbook đã tồn tại
Sub openExcelWorkbook() Dim wb As Workbook Dim fPath As String fPath = "C:....myfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) wb.Close End Sub
23.Đóng workbook
Sub closeWorkbook() Dim wb As Workbook Dim fPath As String fPath = "C:....myfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) 'đóng và lưu wb.Close SaveChanges:=True 'đóng và ko lưu wb.Close SaveChanges:=False End Sub
24.Lưu file workbook
Sub saveWorkbook() Dim wb As Workbook Dim fPath As String Dim newPath As String fPath = "C:....myfile.xlsx" ' old path newPath = "D:....myfile1.xlsx" ' new path Set wb = Workbooks.Open(Filename:=fPath) 'lưu ở vị trí cũ wb.Save 'lưu ở ví trí mới wb.SaveAs Filename:=newPath End Sub
25.Xóa file
Sub deleteFile() Dim wb As Workbook Dim fPath As String fPath = "C:....myfile.xlsx" Kill PathName:=fPath End Sub
26.Tạo một sheet trong workbook
Sub addNewSheetInAWorkbook() Dim wb As Workbook Dim fPath As String fPath = "C:....myfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) wb.Worksheets.Add End Sub
27.Tạo một sheet ở một ví trí chỉ định trong workbook
Sub addNewSheetInAWorkbookAtPosition() Dim wb As Workbook Dim fPath As String fPath = "C:....myfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) 'thêm o vi tri dau tien chúng tôi Before:=1 ' them sau sheet thu 2 chúng tôi After:=1 ' thêm ở sheet cuoi cung chúng tôi After:=Worksheets.count End Sub
28.Đổi tên worksheet
Sub renameWorksheet() Dim wb As Workbook Dim sh As Worksheet Dim newSheetName As String newSheetName = "March" Dim fPath As String fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) chúng tôi = newSheetName End Sub
29.Xóa worksheet
Sub deleteWorksheet() Dim wb As Workbook Dim sh As Worksheet Dim newSheetName As String newSheetName = "March" Dim fPath As String fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) Application.DisplayAlerts = False ' hien thong bao sh.Delete Application.DisplayAlerts = True ' khong muon hien thong bao End Sub
30.Thay đổi tab màu sắc
Sub ChangeTabColor() Dim wb As Workbook Dim sh As Worksheet Dim newSheetName As String newSheetName = "March" Dim fPath As String fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Tab.ColorIndex = 1 sh.Tab.Color = RGB(255, 0, 300) End Sub
31.Copy sheet
Sub CopySheet() Dim wb As Workbook Dim sh As Worksheet Dim newSheetName As String newSheetName = "March" Dim fPath As String fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Copy End Sub
32.Copy sheet như workbook
Sub CopySheetAsWorkbook() Dim wb As Workbook Dim sh As Worksheet Dim newSheetName As String newSheetName = "March" Dim fPath As String fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) wb.Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy End Sub
33.Copy sheet với tên cho trước
Sub CopySheetWithProvidedName() Dim wb As Workbook Dim sh As Worksheet Dim newSheetName As String newSheetName = "March" Dim fPath As String fPath = "C:UsersvmishraDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) chúng tôi Before:=Sheets(1) chúng tôi = "tenmoi" End Sub
34.Ẩn worksheet
Sub HideWorksheet() Dim wb As Workbook Dim sh As Worksheet Dim newSheetName As String newSheetName = "March" Dim fPath As String fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) Set sh = wb.Worksheets(1) sh.Visible = xlSheetHidden End Sub
35.Ẩn tất cả worksheet
Sub HideAllWorksheets() Dim wb As Workbook Dim fPath As String fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) For Each Sheet In wb.Worksheets Sheet.Visible = False End If Next End Sub
36.Hiện tất cả worksheet bị ẩn
Sub UnhideAllWorksheets() Dim wb As Workbook Dim fPath As String fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) For Each Sheet In wb.Worksheets Sheet.Visible = True End If Next End Sub
37.Kiểm tra sheet có tồn tại
Sub CheckIfSheetExists() Dim wb As Workbook Dim fPath As String Dim sheetExists As Boolean sheetExists = False fPath = "C:UsersadminDesktopmyfile.xlsx" Set wb = Workbooks.Open(Filename:=fPath) For Each Sheet In wb.Worksheets If chúng tôi = "SheetName To Search" Then sheetExists = True Exit For End If Next If sheetExists Then MsgBox "Yes, SheetName To Search exists in the workbook" End If End Sub
38.Sắp xếp sheet theo thứ tự A-Z
Sub SortSheetNames() ' sap xep sheet theo thu tu A-Z Dim i As Integer Dim j As Integer Dim totalSheets As Integer totalSheets = Sheets.count For i = 1 To totalSheets - 1 For j = i + 1 To totalSheets If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move Before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub
39.Đọc dữ liệu từ một file text (mỗi dòng cách nhau khoảng trắng)
Sub ReadDataFromFile() Dim my_file As Integer Dim text_line As String Dim file_name As String Dim i As Integer Dim name_array() As String file_name = "C:text_file2.txt" my_file = FreeFile() Open file_name For Input As my_file i = 1 While Not EOF(my_file) Line Input #my_file, text_line If i = 1 Then name_array = Split(text_line, " ") Cells(i, "A").Value = name_array(0) Cells(i + 1, "A").Value = name_array(1) ElseIf i = 2 Then pos = InStr(text_line, "Age: ") Cells(i + 1, "A").Value = Replace(text_line, "Age: ", "") End If ElseIf i = 3 Then pos = InStr(text_line, "Location: ") Cells(i + 1, "A").Value = Replace(text_line, "Location: ", "") End If End If i = i + 1 Wend End Sub
40.Đọc dữ liệu file text (line by line)
Sub ReadFileLineByLine() Dim my_file As Integer Dim text_line As String Dim file_name As String Dim i As Integer file_name = "C:text_file.txt" my_file = FreeFile() Open file_name For Input As my_file i = 1 While Not EOF(my_file) Line Input #my_file, text_line Cells(i, "A").Value = text_line i = i + 1 Wend End Sub
Cập nhật thông tin chi tiết về 20.Hàm Xử Lý Date() Và Time() Trong Vba – Blog Chia Sẽ Kiến Thức Học Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!