ExcelEasy1.blogspot.com: Hướng dẫn cập nhật thời gian nhập liệu tự động bằng VBA

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Text <> "" Then

Target.Offset(0, 1) = Format(Now, "dd")

Target.Offset(0, 2) = Format(Now, "mm")

Target.Offset(0, 3) = Format(Now, "yyyy")

End If

End Sub

Download File: Driver

Công cụ Macro VBA xử lý tệp tuyệt vời trong Excel

Công cụ xử lý tệp Excel

Nhiều công ty đã sử dụng bảng tính trong nhiều thập kỷ. Đôi khi các mô hình bảng tính có thể quay trở lại sau 20 năm, khi các bước đầu tiên được thực hiện để xây dựng chúng, đôi khi sử dụng Lotus 123 hoặc các ứng dụng bảng tính khác.

Thường xuyên hơn không, các mô hình (thường là kinh doanh quan trọng) này đã được mở rộng đáng kể qua nhiều năm:

• Các trang tính đã được chèn hoặc sao chép từ các sổ làm việc khác
• Định dạng đã được thay đổi hàng trăm lần thành hàng ngàn ô
• Tên được xác định (phạm vi được đặt tên) đã được chèn. Cố ý hoặc vô tình (ví dụ: bằng cách sao chép bảng tính)
• Tên được xác định bị hỏng vẫn còn từ hợp nhất từ ​​các định dạng tệp bảng tính khác sang Excel
• Hàm do người dùng xác định trong VBA
• .........

Khách hàng của tôi (người đã liên hệ với bộ phận hỗ trợ cao cấp của Microsoft về các vấn đề này mà không nhận được giải pháp) đã hỏi tôi liệu có thể xây dựng một công cụ giống như thuật sĩ để cứu vãn loại tệp này không. Thậm chí tốt hơn: để loại bỏ hoàn toàn các lỗi có thể xảy ra, mà không làm hỏng tính toàn vẹn của mô hình bảng tính, mà không xóa định dạng và cũng đảm bảo bất kỳ mã VBA nào được giữ lại.

Link tải chương trình: Tại đây

Giao diện người dùng

Menu của công cụ được hiển thị dưới đây.

Những ảnh chụp màn hình này cho thấy các bước của trình hướng dẫn.

Bước đầu tiên: lựa chọn tập tin.

 Bước tiếp theo: những hành động phải được thực hiện:

Bước cuối cùng: Những số liệu thống kê phải được thu thập:

Vì việc khắc phục có thể là một quá trình dài, các công cụ cho thấy sự tiến bộ của nó. Lưu ý rằng chỉ báo tiến trình có nút dừng, trong đó quá trình có thể được thoát.

Khách hàng của tôi đã nhiều lần xác nhận công cụ này đã được chứng minh là một trình tiết kiệm thời gian vô giá, cho phép họ cứu vãn các tệp quan trọng trong kinh doanh, nếu không họ phải coi là bị mất.

Link tải chương trình: Tại đây

HƯỚNG DẪN CÁCH XUẤT DỮ LIỆU HÀNG LOẠT TỪ EXCEL RA PDF BẰNG VBA

Một yêu cầu có thể gặp phải là chúng ta cần lưu trữ các phiếu đó, dưới dạng từng phiếu một theo các hình thức:

• In ra giấy (chức năng in hàng loạt)
• Xuất ra bản mềm (file PDF, file Excel…)

Chúng ta cùng tìm hiểu cách xây dựng lệnh Trích xuất dữ liệu hàng loạt ra file PDF theo mỗi đối tượng, cụ thể ở đây là mỗi Phiếu Xuất kho.

XÁC ĐỊNH TRÌNH TỰ THỰC HIỆN

Đây là việc mô tả lại những việc cần phải làm, từng bước thực hiện và tương tác với Excel ra sao. Việc diễn đạt tốt trình tự thực hiện sẽ giúp chúng ta dễ dàng viết lệnh trong VBA và tránh các lỗi có thể xảy ra.

Bước 1: Xác định các đối tượng

Ở đây chúng ta có 2 đối tượng: Dòng cuối của bảng kê và vùng dữ liệu cần trích xuất

• Dòng cuối bảng kê:  dòng cuối được xác định bằng câu lệnh tìm dòng cuối trong VBA
• Vùng dữ liệu cần trích xuất: trong mục này chúng ta giới hạn phạm vi dữ liệu cần trích xuất (nội dung này thiết lập trong phần Page Setup > Thẻ Sheet > Mục Print area)

Bước 2: Xác định đường dẫn tới nơi lưu kết quả trích xuất

Trong bước này chúng ta cần biện luận các trường hợp:

• Chọn chính xác đường dẫn tới nơi lưu kết quả => Chọn tới đường dẫn đó
• Chưa chọn chính xác đường dẫn => Thông báo lỗi và yêu cầu chọn lại hoặc hủy bỏ câu lệnh.

Bước 3: Viết tiếp câu lệnh trong trường hợp chọn đúng đường dẫn

Bước này cũng xảy ra một số trường hợp như sau:

• Nếu kết quả cần trích xuất đã có hoặc trùng tên => Thông báo cho người sử dụng biết để xử lý: Có ghi đè lên file đó hay không
• Trường hợp nếu ghi đè không được vì file đó không cho phép ghi đè => Thông báo cho người sử dụng biết để xử lý.
• Trường hợp kết quả cần trích xuất chưa có sẵn => Thực hiện việc trích xuất.

Việc này sẽ được thực hiện khi kiểm tra từng kết quả cần trích xuất => Do đó phải thực hiện theo 1 vòng lặp cho từng đối tượng số phiếu.

CÂU LỆNH VBA TRÍCH XUẤT DỮ LIỆU

Trong đoạn code trên, mình đã ghi chú rất cụ thể nội dung từng đoạn code để các bạn có thể dựa vào đó đọc, hiểu nội dung từng đoạn.

Câu lệnh viết trong VBA có thể dài và hơi khó đọc, nhưng khi chúng ta đã xây dựng được trình tự từ trước đó thì việc kiểm soát lại câu lệnh VBA sẽ không khó nữa.

Khi ứng dụng vào việc khác thì cần lưu ý tọa độ các vị trí để điều chỉnh cho khớp với đặc điểm dữ liệu trong việc đó.

Cùng xem kết quả nào:

Tải về file mẫu : Tại đây

VBA để thêm bản đồ Google tĩnh trong bảng tính Excel

Trong bài viết này sẽ cung cấp cho bạn một mẹo nhanh - làm thế nào bạn có thể thêm bản đồ google vào sổ làm việc excel của bạn mà không cần mở bản đồ google trong bất kỳ trình duyệt nào. Thông tin chi tiết về điều này sẽ theo sau ... 

Để làm điều đó tôi đang sử dụng Google Map Map API . Tôi sẽ không giải thích thêm về API này ở đây vì bạn đã có thể nhận được rất nhiều thông tin về API này ở đây trên trang này… Nhấn vào đây… Ở cuối bài viết này bạn sẽ tìm thấy một sổ làm việc có thể tải xuống như dưới đây. Nơi bạn sẽ có thể tạo ra các bản đồ thành phố khác nhau hoặc loại khác nhau.

Trình tạo bản đồ của Google

Tải xuống trình tạo bản đồ mẫu Excel

TẠI ĐÂY

API bản đồ tĩnh của Google sử dụng URL phải ở định dạng này: (được sao chép từ trang tài liệu google ở ​​trên)

https://maps.googleapis.com/maps/api/staticmap? thông số

Thông số

Có rất nhiều khả năng của các thông số có thể được cung cấp để tạo bản đồ của bạn .. một lần nữa chi tiết hơn bạn có thể tìm thấy nó ở trên trang

Ví dụ tôi sẽ lấy một bản đồ rất cơ bản của bất kỳ thành phố nào trên thế giới và chỉ cho bạn cách sử dụng API này trong excel.

Tôi hứa với bạn, đó là mã đơn giản nhất mà bạn từng thấy để sử dụng nó trong excel cho một điều tuyệt vời như vậy.

Bước 1. Trình giữ chỗ để giữ Bản đồ đã tạo của bạn

Thêm một hình dạng hình chữ nhật (ofcourse bạn có thể chọn bất cứ hình dạng bạn thích) trong bảng của bạn bất cứ nơi nào bạn muốn hiển thị bản đồ và thay đổi kích thước nó theo nhu cầu của bạn.

Thêm hình dạng cho bản đồ

Lưu ý: Bạn có thể cung cấp tên cho hình dạng đó để bạn có thể tham chiếu tên đó bằng tên có liên quan. Đây không phải là điều bắt buộc phải làm.

Làm thế nào để cung cấp một tên cho hình dạng trong Excel - bạn đã thêm ở trên?

Làm theo hướng dẫn dưới đây để làm như vậy…

Cách đặt tên cho hình dạng trong Excel

Bước 2. Tạo URL cho API bản đồ tĩnh của Google

Tạo URL của bạn dựa trên các thông số khác nhau mà bạn muốn. Ví dụ: để tạo Bản đồ thành phố Amsterdam ở kích thước 400X400 với mức thu phóng: 14, sau đây sẽ là URL.

https://maps.googleapis.com/maps/api/staticmap?center=Amsterdam&zoom=14&size=400×400

Lưu ý: Để kiểm tra xem URL của bạn có chính xác hay không, bạn có thể chỉ cần mở URL này giống như trong bất kỳ trình duyệt nào. Nếu bạn thấy một hình ảnh bản đồ tĩnh được tạo, điều đó có nghĩa là URL của bạn là chính xác và bạn có thể tiếp tục và sử dụng nó trong mã VBA Excel của bạn.

Bước 3. Mã VBA để hiển thị Bản đồ tĩnh của Google

Mã VBA để thực thi để hiển thị bản đồ thành phố Amsterdam theo hình chữ nhật

Sub insertMap()
    ' prepare your google static map API
    gAPIMapUrl = "https://maps.googleapis.com/maps/api/staticmap?center=Amsterdam&zoom=12&size=640x640"
    Shapes("your Shape Name").Fill.UserPicture gAPIMapUrl
End Sub

Bây giờ bạn đã biết cách bao gồm một bản đồ trong bảng excel của mình bằng cách sử dụng excel vba và Google Static Map API?

Dựa trên lời giải thích ở trên, tôi đã tạo một bảng tính mẫu, mà bạn có thể tải xuống và chơi với nó.

Tải xuống trình tạo bản đồ mẫu Excel

TẠI ĐÂY

Tải về, sử dụng nó và đừng quên cung cấp cho tôi phản hồi của bạn bằng cách gõ bình luận của bạn ở đây hoặc gửi email hoặc bạn có thể. Bạn cũng có thể chia sẻ nó với bạn bè, đồng nghiệp của bạn hoặc bất cứ ai bạn muốn !!

Chương trình + Add-in Macro VBA tra cứu và đánh dấu từ trong Excel giống Word và PDF.

Nếu bạn dùng Word chăc hẳn sẽ biết chức năng tra cứu từ trong cụm từ Ctrl + F rất tiện dụng, bạn nhập từ hoặc cụm từ cần tra vào và ấn Next, Word sẽ tự động tìm kiếm và đánh dấu vào các cụm từ trùng với từ tra.

Tuy nhiên chức năng tra từ Ctrl+F trên Excel rất hạn chế khi tra cứu. Nó chỉ trỏ chuột vào ô có từ tra mà không biết từ đó ở vị trí nào, không đánh dấu từ đó trong cụm từ như trong Word.

Do vậy, QuanTriExcel.info phát triển “ Chương trình + Add-in Macro VBA tra cứu và đánh dấu từ trong Excel giống Word và PDF. “ với mục đích tra cứu từ trong Excel dễ dàng và tiện dụng như trong Word khắc phục những điểm yếu của Excel.

Hoạt động :

 Bạn chọn nút tra cứu hoặc ấn tổ hợp phím Ctrl+F -> nhập từ cần tra và chọn OK đến khi tra cứu xong.

Chương trình sẽ tự động quét toàn bộ các ô trong sheets hiện hành có dữ liệu và kiểm tra đối chiếu -> nếu trùng sẽ tô đỏ vào cụm từ trung và phủ vàng vào ô tương ứng.

Link tải chương trình ( file xlsm ) : Tại đây


Link tải Add-in Tra cứu từ : Tại đây


Link file mẫu thực hành : Tại đây

Chia sẻ kinh nghiệm để bạn tự học lập trình VBA trong Excel

Sử dụng VBA trong Excel
Excel là công cụ mạnh mẽ mà bạn có thể sử dụng để phân tích, vận hành và dữ liệu hiện tại. Mặc dù trong Excel luôn tập hợp những tính năng phong phú trong giao diện người dùng chuẩn Excel, bạn có thể muốn tìm một cách dễ dàng hơn để thực hiện nhiệm vụ đơn điệu, một thói quen hoặc để thực hiện những nhiệm vụ mà người dùng không thể giải quyết được. VBA trong Excel là ngôn ngữ lập trình sẽ cung cấp cho bạn các thiết bị để lan truyền những ứng dụng.
Nhiều người khi sử dụng VBA đều cho rằng công viêc của họ dễ dàng hơn và mang đến những kỹ năng để làm việc trong văn phòng mà họ nhiều khi nghĩ rằng nó không thể xảy ra. 
Sử dụng VBA trong Excel là hệ thống hóa các nhiệm vụ được lặp lại nhiều lần, có thể tự động hóa các nhiệm vụ để viết lệnh rõ ràng cho Excel.
Bạn có thể sử dụng VBA Excel để thực hiện các công việc lặt vặt có kết hợp với các ứng dụng Excel Office như Microsoft Access, xây dựng bộ thủ tục mới để phân tích dữ liệu của bạn, sau đó sử dụng các khả năng biểu đồ trong Excel để hiển thị các kết quả.

Một số ứng dụng quan trọng của các macro

- Hiệu suất ổn định: Bạn có thể hiển thị hiệu suất công việc và sự xuất sắc báo cáo hàng ngày của bạn với sự giúp đỡ của VBA nếu đến cuối tháng bạn phải làm báo cáo bán hàng, bạn cũng có thể phát triển một chương trình trong excel sau đó.
- Công việc lặp đi lặp lại và lệnh tùy chỉnh: Bạn có thể sửa đổi lệnh của bạn và làm công việc đơn điệu trong một lệnh duy nhất nếu bạn thực hiện cùng một cách hoặc trùng lặp trong excel sau đó sử dụng các macro. Cùng với sự hỗ trợ của các vĩ mô, bạn có thể cung cấp thông các lệnh vào lệnh duy nhất.
- Sử dụng macro và tạo thanh công cụ tùy chỉnh của bạn với các điều khiển của riêng bạn trong excel. Ngoài ra bạn có thể thực hiện các chức năng bảng tính mới.
- Với sự hỗ trợ của các macro, bạn có thể vẽ đồ thị và các đối tượng. Nhưng bạn phải tìm hiểu VBA mã hóa để cùng nhau hành động với bản vẽ và biểu đồ các công cụ của Excel. Bạn có thể thay đổi kích thước, trọng lượng tuyến của một đối tượng và rescale một khu vực.
Những chia sẻ trên đã cung cấp cho những bạn tự học lập trình VBA trong Excel những thông tin cần thiết.

Hướng dẫn lập trình VBA căn bản

Để bắt đầu học Lập trình VBA một cách tốt nhất, các bạn hãy thử theo cách sau của tôi, nếu thấy hay thì có thể vận dụng:
1. Học cửa sổ VBE , menu và các nút trên VBE
2. Học cách Debug một dòng code (Phải biết), Cách Debug đi đôi với cửa sổ Local Window và Watch Window để xem code chạy từng dòng như thế nào ở menu Debug.

+ Viết code với chế độ Debug Mỗi lần viết đoạn Code xong là Ấn "Compile ..." để soát lỗi​

+ Chạy Code (F5) để soát lỗi​

3. Tìm kiếm hàm, các hằng (ví dụ: vbBlack, vbNullString, ...) , các phương thức,... trong: Object Browers ( Ấn F2 ) (Rất quan trọng)
4. Các Cửa sổ VBE: Immediate (Ctrl + g), Locals Window, Watch Window

+ Immediate: Debug trong Immediate rất hay để các bạn có thể viết hàm để chạy thử.​

++ Nếu là Function thì Gõ chuỗi vào cửa sổ Immediate và ấn Enter:​

?TestFunction(Var1,Var2, ...)​

?1 = 2​

?TypeName(Worksheets)​

++ Với Sub thì không cần dấu ?​

+ Locals Window và Watch Window: Đánh dấu Breakpoint chạy debug (Dấu tròn đỏ trước dòng code), thì cửa sổ này sẽ diễn giải code đang chạy từng dòng như thế nào​

5. Cách thêm References
6. Học VBIDE để biết cách tạo module, class module, ... của Excel và VBA bằng chính VBA
7. Học các Hàm, Các phương thức hướng sự kiện. (Vận dụng tìm kiếm trong Object Browers)
Cách bắt sự kiện Application (Thường là một Add-Ins ) , Workbook, Worksheet, Userform
8. Sau khi viết code xong Các file có thể lưu được Code VBA:

+ Với trang tính: xls, xlsm, xlsb (Khuyên dùng)​

+ Add-ins và Style Sheet: xla, xlam (Khuyên dùng), sxl​

------------------------------------------
Module Này chỉ là ý kiến cá nhân của tôi, dùng để xem những gì căn bản nhất, và chạy test các thủ tục.
Các bạn cần Vào cửa sổ VBE - Vào Tools - Vào References - tìm thêm Microsoft Scripting Runtime để module đủ điều kiện chạy các khai báo Object (Đối tượng)

Link tải ở bên dưới từng tài liệu. Các bạn quan tâm có thể tải về nhé. Xin cảm ơn.

https://msexcel2017.blogspot.com/2019/04/50-ebooks-documents-to-lear-excel-vba.html

https://msexcel2017.blogspot.com/2019/04/ebooks-excel-and-vba-free-download.html

https://msexcel2017.blogspot.com/2019/04/150-chuyen-e-tin-hoc-van-phong-hoc.html

VBA với ký tự số trong Excel

Khi bạn nhập những giá trị số trong Excel và nhấn Enter thì Excel sẽ tự động căn chỉnh các giá trị trên sang bên phải của ô. Trong trường hợp bạn nhập những dãy số mà đằng trước có số 0, ví dụ như số điện thoại, … thì Excel sẽ tự động loại bỏ những số 0 này. Khi đó, người dùng sẽ sử dụng thêm dấu trước khi đánh dãy số để Excel không loại bỏ số 0 đằng trước. Những dãy số như vậy thường được gọi là ký tự số. Một trường hợp khác cũng được coi là các ký tự số đó là khi ta nhập các số xen lẫn giữa các ký tự hoặc các số được ngăn cách bởi các ký tự đặc biệt.

Chắc hẳn trong công việc thường ngày, bạn phải gặp một số  trường hợp thao tác, xử lý với các ký tự số nhưng các hàm có sẵn trong Excel không thể giải quyết triệt được vấn đề theo ý muốn.

1. Đếm số chữ số trong chuỗi ký tự số

Ví dụ bạn có 1 chuỗi sau: 17B4HH22TB63
Bạn muốn đếm xem chuỗi trên có bao nhiêu số? Bạn có thể sử dụng hàm VBA dưới đây:

Function CountNumber(str As String)
Dim mlen As Long
Dim i As Long
Dim iCount As Long
‘Neu chuoi =0 thi khong xu ly
If Len(str) = 0 Then Exit Function
‘Xoa bo cac ky tu trang o dau va cuoi
str = Trim(str)
‘Dem so ky tu chuoi
mlen = Len(str)
iCount = 0
For i = 1 To mlen
If IsNumeric(Mid(str, i, 1)) Then
iCount = iCount + 1
End If
Next
CountNumber = iCount
End Function

2. Tìm giá trị lớn nhất trong chuỗi ký tự số

Giả sử bạn có 1 chuỗi sau: 15,67,66,5748,37,59,89,27,99,20

Bạn muốn tìm số lớn nhất trong chuỗi trên? Bạn có thể sử dụng hàm VBA dưới đây:

Function HighestNumber(R As Range)
Dim x As Variant, M As Double, i As Long, ct As Long
Set R = R.CellS(1, 1)
x = Split(R.Value, “,”)
For i = LBound(x) To UBound(x)
If IsNumeric(x(i)) Then
ct = ct + 1
If x(i) > M Then M = x(i)
End If
Next i
If ct = 0 Then
HighestNumber = CVErr(xlErrNA)
Else
HighestNumber = M
End If
End Function

Nếu bạn gặp trường hợp các ký tự số trong chuỗi được ngăn cách bởi dấu cách, ví dụ 15 67 66 5748 37 59 89 27 99 20 thì bạn chỉ cần sửa trong đoạn code trên x = Split(R.Value, “,”) được thay thế bằng x = Split(R.Value, ” “).

Hi vọng những thủ thuật VBA trên giúp ích cho các bạn!