Đố vui về VBA!

[anhtuan1066]

Nhằm cũng cố kiến thức về VBA cho các bạn mới bắt đầu và cả những bạn đang ứng dụng mà chưa hiểu nhiều về nó, tôi mở topic này với mong mõi qua những câu hỏi vui, các bạn sẽ nhận định lại sự hiểu biết cũa mình... (Kễ cã chính tôi cũng đang tập tành nên có rất nhiều cái chưa biết)
Mong rằng topic sẽ mang đến cho các bạn những khám phá thú vị với những cái tưỡng chừng như đã biết
Mong nhận dc bài viết về câu đố cũa các cao thủ! Còn các bạn mới thì đừng ngại khi đưa ra ý kiến cũa mình.. Có sai có sữa sẽ hoàn thiện!
Tôi xin mỡ màn trước bằng 1 câu hỏi đơn giãn
ANH TUẤN

CÂU HỎI 1: Tại sao biến K ko hoạt động?
Tôi muốn khi nhấn vào 1 button thì cell A1 sẽ tăng lên 1 đơn vị... Tôi đã làm như sau:
-Tạo 1 Command Button (nút nhấn thuộc thanh Control Toolbox), click phải chuột lên nút nhấn, chọn View code, rồi gõ vào đoạn code sau:

Private Sub CommandButton1_Click()
   K = K + 1
   Range("A1").Value = K
End Sub

Ban đầu K chưa có gì, xem như =0, nhấn nút lần thứ nhất thì K dc tăng thêm 1, vậy K hiện tại sẽ bằng 1, và gán K vào cell A1 thì đương nhiên A1 sẽ =1... Nhấn nút lần 2, K lại dc tăng thêm 1 nên hiện tại K sẽ =2 và cell A1 cũng sẽ =2... vân vân.. từ đó diễn tiến tiếp...
Hi.. hi.. Điều này nghe qua có vẽ rất hợp lý, ấy thế mà khi nhấn nút nó chỉ hoạt động dc duy nhất 1 lần (A1 = 1) rồi thôi ko nhút nhít nữa...
Các bạn có thể giãi thích tại sao lại như thế ko? Tại sao những lần nhấn nút sau đó K lại ko tăng thêm tí nào (vì thực tế A1 vẫn cứ = 1 hoài) ?
ANH TUẤN

 

[ndu96081631]

Vâng, Đúng như anh nói.

Đây là một giải pháp đường vòng.

Chúng ta thao tác với một tập tin text nhưng đuôi của file text không phải là txt mà là xls.

Khi kết nối với tập tin này (xls), chúng ta sẽ kết nối bình thường giống như tôi đã làm ở trên.
Như vậy chúng ta có thể lưu dữ liệu trên tập tin xls với số lượng dòng lớn hơn. Nhưng chú ý rằng nếu bạn mở tập tin trong Excel 2003 trở về trước thì coi như các dòng vượt trên 65,536 sẽ mất đi.

Lê Văn Duyệt

Duyệt ơi, mình xin hỏi thêm ngoài lề 1 chút: Mình có thể lấy giá trị tại cell A70000 được không? (code lấy dữ liệu nằm ở 1 file khác) ---> Nếu lấy được thì có cần thủ tục gì đặc biệt hay không?

 

[levanduyet]

Anh Tuấn,

Duyệt ơi, mình xin hỏi thêm ngoài lề 1 chút: Mình có thể lấy giá trị tại cell A70000 được không? (code lấy dữ liệu nằm ở 1 file khác)

(Em chưa hiểu câu màu xanh của Anh.)

Dĩ nhiên là được anh à.
Các bước như sau:

'Khai báo biến
Dim oXL    As Object
Dim oXLWb  As Object
Dim oXLWs  As Object

'Khởi tạo đối tượng
Set oXL = CreateObject("Excel.Application")
'sFilePath là đường dẫn đầy đủ của tập tin
Set oXLWb = oXL.Workbooks.Open(sFilePath)
Set oXLWs = oXLWb.Worksheets(1)

'Sau đó anh tham chiếu đến đối tượng bình thường
'Giả sử anh cần lấy dữ liệu ở A70000
debug.Print oXLWs.Range("A70000")

'Giải phóng bộ nhớ
Set oXLWs = Nothing
oXLWb.Close SaveChanges:=False
Set oXLWb = Nothing
oXL.Quit
Set oXL = Nothing

Như vậy chúng ta có thể tạo một tập tin lưu giữ dữ liệu có số hàng lớn hơn số hàng có trong tập tin xls theo các bước sau:

1. Bạn hãy tạo một tập tin text mới và lưu lại với định dạng xls như hình sau.
   
   
   
   
   
   
   
   
   
   
   
   
2. Thực hiện các bước như ở trên, nhưng sau đó bạn lưu lại

   oXLWb.Close SaveChanges:=True, FileName="Tên tập tin.xls"

   
   
   Chú ý rằng cách tham chiếu đến một Range áp dụng bình thường, không có gì đặc biệt cả.

Lê Văn Duyệt

 

[ndu96081631]

Anh Tuấn,


(Em chưa hiểu câu màu xanh của Anh.)

Dĩ nhiên là được anh à.
Các bước như sau:

'Khai báo biến
Dim oXL    As Object
Dim oXLWb  As Object
Dim oXLWs  As Object

'Khởi tạo đối tượng
Set oXL = CreateObject("Excel.Application")
'sFilePath là đường dẫn đầy đủ của tập tin
Set oXLWb = oXL.Workbooks.Open(sFilePath)
Set oXLWs = oXLWb.Worksheets(1)

'Sau đó anh tham chiếu đến đối tượng bình thường
'Giả sử anh cần lấy dữ liệu ở A70000
debug.Print oXLWs.Range("A70000")

'Giải phóng bộ nhớ
Set oXLWs = Nothing
oXLWb.Close SaveChanges:=False
Set oXLWb = Nothing
oXL.Quit
Set oXL = Nothing

Lê Văn Duyệt

Không được Duyệt ơi
Tôi đặt file Test.xls ấy vào ổ D:\
Chạy code sau:

Sub Test()
  Dim oXL As Object, oXLWb As Object, oXLWs As Object
  Set oXL = CreateObject("Excel.Application")
  Set oXLWb = oXL.Workbooks.Open("D:\Test.xls")
  Set oXLWs = oXLWb.Worksheets(1)
  Debug.Print oXLWs.Range("A70000")
  Set oXLWs = Nothing
  oXLWb.Close SaveChanges:=False
  Set oXLWb = Nothing
  oXL.Quit
  Set oXL = Nothing
End Sub

Nó báo lỗi:

Run-time error '1004'
Application-defined or object-defined error

Lỗi được đánh dấu tại dòng này:
Debug.Print oXLWs.Range("A70000")
Sửa thành:
Debug.Print oXLWs.Range("A60000")
Là hết lỗi

 

[levanduyet]

Không được Duyệt ơi
Tôi đặt file Test.xls ấy vào ổ D:\
Chạy code sau:

Sub Test()
  Dim oXL As Object, oXLWb As Object, oXLWs As Object
  Set oXL = CreateObject("Excel.Application")
  Set oXLWb = oXL.Workbooks.Open("D:\Test.xls")
  Set oXLWs = oXLWb.Worksheets(1)
  Debug.Print oXLWs.Range("A70000")
  Set oXLWs = Nothing
  oXLWb.Close SaveChanges:=False
  Set oXLWb = Nothing
  oXL.Quit
  Set oXL = Nothing
End Sub

Nó báo lỗi:

Lỗi được đánh dấu tại dòng này:
Debug.Print oXLWs.Range("A70000")
Sửa thành:
Debug.Print oXLWs.Range("A60000")
Là hết lỗi

Anh ơi anh nhớ kiểm tra hàng cuối cùng của tập tin của anh.

lRows = FindLastRow(oXLWs)

Hàm tìm hàng cuối:

Function FindLastRow(wsObj As Worksheet) As Long
    On Error Resume Next
    FindLastRow = wsObj.Cells.Find(What:="*", _
                                   After:=wsObj.Range("A1"), _
                                   LookAt:=xlPart, _
                                   LookIn:=xlFormulas, _
                                   SearchOrder:=xlByRows, _
                                   SearchDirection:=xlPrevious, _
                                   MatchCase:=False).Row
    On Error GoTo 0
End Function

Lê Văn Duyệt

 

[ndu96081631]

Ah... xin nói thêm:
Code ở bài 323, nếu chạy trên Excel 2007 thì càng tệ ---> Báo lỗi tùm lum và thậm chí treo máy luôn (phải Task manager mới xử được)
Duyệt có thể cho code hoàn hảo vào 1 file và đưa lên đây không? (code để lấy dữ liệu tại cell A70000 ấy)

 

[levanduyet]

Ah... xin nói thêm:
Code ở bài 323, nếu chạy trên Excel 2007 thì càng tệ ---> Báo lỗi tùm lum và thậm chí treo máy luôn (phải Task manager mới xử được)
Duyệt có thể cho code hoàn hảo vào 1 file và đưa lên đây không? (code để lấy dữ liệu tại cell A70000 ấy)

Anh ơi,
Coi chừng Excel 2007 của anh có vấn đề.
Em đã dùng mấy cái code này trên Excel 2007 lâu lắm rồi. Không có lỗi gì cả anh ơi.
Anh gởi file của anh lên đây đi.


Lê Văn Duyệt

 

[ndu96081631]

Anh ơi,
Coi chừng Excel 2007 của anh có vấn đề.
Em đã dùng mấy cái code này trên Excel 2007 lâu lắm rồi. Không có lỗi gì cả anh ơi.
Anh gởi file của anh lên đây đi.


Lê Văn Duyệt

Thì code là của Duyệt mà, tôi copy và paste vào thôi (chỉ sửa đường dẩn đến file)
--------------------------------
Ah... chắc Office có vấn đề thật, vì thử trên Excel 2010 thì OK
--------------------------------
Một vấn đề đặt ra là: Chúng ta lưu dữ liệu có số dòng > 65536 dòng để làm gì nếu như ta không có bộ Office 2007 hoặc Office 2010 để chạy code? Mà nếu đã có bộ Office đời mới này rồi thì ta đâu cần phải đi đường vòng như thế?
(Vì code này đâu có chạy được trên Excel 2003)
Duyệt có thể nói rõ hơn về mục đích của việc làm này không?

 

[levanduyet]

Một vấn đề đặt ra là: Chúng ta lưu dữ liệu có số dòng > 65536 dòng để làm gì nếu như ta không có bộ Office 2007 hoặc Office 2010 để chạy code?
Duyệt có thể nói rõ hơn về mục đích của việc làm này không?

Chắc có lẻ anh chưa bao giờ làm việc với những tập tin có trên 100,000 dòng.
Thông thường các dữ liệu này là từ phần mềm xuất ra.

Từ đó em mới có ý nghĩ là dùng một tập tin *.xls (mà thực chất là *.txt) để em lưu và thao tác đối với những dữ liệu lớn mà Excel 2003 không đáp ứng số dòng.
Tập tin *.xls chỉ để lưu dữ liệu, chúng ta không được mở ra với Excel 2003 để chỉnh sửa.

Vì code này đâu có chạy được trên Excel 2003

Anh ơi, em vẫn chạy trên Excel 2003 mà?

Lê Văn Duyệt

 

[ndu96081631]

Anh ơi, em vẫn chạy trên Excel 2003 mà?
Lê Văn Duyệt

Chắc trên máy của Duyệt có cài Office 2007 nên chạy code ấy không có vấn đề, chứ tôi thử mấy máy rồi, những máy ấy đều không cài Office 2007 và không có máy nào thí nghiệm thành công cả ---> Toàn báo lỗi
Có bạn nào đang dùng Office 2003 test giúp tôi với

 

[ptm0412]

Báo lỗi: "Application defined or Object Defined Error". Lỗi tại dòng lệnh Debug Print.

(Test trên máy chỉ có 1 bộ 2003)

 

[ndu96081631]

Báo lỗi: "Application defined or Object Defined Error". Lỗi tại dòng lệnh Debug Print.

(Test trên máy chỉ có 1 bộ 2003)

Điều đó là HIỂN NHIÊN và em nghĩ không thể nào khác hơn được, trừ phi trên máy ta có cài Office 2007
Nhưng đã cài Office 2007 rồi thì em tự hỏi có cần thiết phải đi đường vòng thế không? (lưu file thành .txt rồi lại lưu thành .xls) ---> Tại sao không lưu thành .xlsx cho nó đở mất công
???
Tóm lại: em cảm thấy công việc này thiếu thực tế quá...

 

[ndu96081631]

Anh Tuấn,

Tại sao lại thiếu thực tế? Vấn đề là có thực, cụ thể là ở công ty em.

Từ SAP xuất ra file *.xls, tất cả các máy đều dùng Office 2003. Vậy thì làm sao thao tác và lấy dữ liệu với tập tin này.
Chẳng lẻ đây không phải là vấn đề thực tế?

Như em đã nói, anh phải kiểm tra hàng cuối cùng. Chắc chắn là anh không kiểm tra.
Xin nói lại tập tin này em đã sử dụng trên máy Office 2003 của công ty mấy năm rồi.

Lê Văn Duyệt

Duyệt à! Tôi không bàn về tập tin xls có số dòng > 65536 ---> Chỉ nói về code lấy dữ liệu tại cell A70000 thôi!
Nó không thể nào chạy được nếu máy tính của Duyệt chưa cài Office 2007
Duyệt cứ mang code ấy đến máy nào chỉ có Office 2003 mà thử sẽ biết liền (tôi thử trên 5 máy rồi đấy... Sư phụ ptm0412 cũng vừa kiểm tra xong, kết quả là Error)
Điều này quá hiển nhiên, lý nào Duyệt lại không biết
Kiểm tra LastRow, đương nhiên nó chỉ = 65536 thôi (lấy đâu ra 70000)
---------------------------------
Cái sự KHÔNG THỰC TẾ ở đây là: Muốn chạy được code thì phải cài Office 2007 ---> Có phải là quá vô lý hay không?

 

[levanduyet]

Cái sự KHÔNG THỰC TẾ ở đây là: Muốn chạy được code thì phải cài Office 2007 ---> Có phải là quá vô lý hay không?

Chào anh,

Em kiểm tra lại máy đang dùng code này cài 2 Office. Còn mấy máy khác thì khi thực hiện không liên quan đến code này.

Ngoài ra code này em còn dùng trên VB và em cài đặt cho tất cả các máy trong mạng LAN xài. Để em kiểm tra lại việc dùng code này trong VB ở các máy khác xem sao.

Lê Văn Duyệt

 

[ndu96081631]

Chào anh,

Em kiểm tra lại máy đang dùng code này cài 2 Office. Còn mấy máy khác thì khi thực hiện không liên quan đến code này.

Ngoài ra code này em còn dùng trên VB và em cài đặt cho tất cả các máy trong mạng LAN xài. Để em kiểm tra lại việc dùng code này trong VB ở các máy khác xem sao.

Lê Văn Duyệt

Nói thật, tôi đang rất hứng thú nếu có cách nào đó có thể lấy được dữ liệu tại cell A70000 mà không cần phải cài Office 2007
Duyệt nghiên cứu giúp xem, dùng VB6 cũng được (miễn chạy được trên máy chỉ có Office 2003)
Nếu thành công thì mới thật sự là HỮU ÍCH đây!

 

[levanduyet]

Chắc các bạn đều biết công cụ Consolidate, nó có khả năng tổng hợp dữ liệu từ nhiều sheet, thậm chí từ nhiều file
Việc viết code có thể dựa trên cơ sơ Record macro rồi chỉnh lại
Vấn đề ở đây là tôi có 4 file nằm trong 1 thư mục, tôi muốn dùng Consolidate để tổng hợp nó ra 1 file khác mà không cần đến 1 vòng lập nào
Các bạn hãy thử xem
Có file đính kèm, ra kết quả giống như trong file ConsolMutiFiles.xls là xem như thành công! (các file con nằm trong thư mục Source)
Lưu ý:
- Ta chỉ biết các file con nằm trong 1 thư mục chỉ định trước chứ không biết có tổng cộng bao nhiêu file con và tên file con là gì đâu nha
- Biết trước: các file con nằm trong thư mục Source (là thư mục con của thư mục chứa file ConsolMutiFiles.xls)
- Biết trước: Dữ liệu cần tổng hợp trong các file con nằm ở "Sheet1", vùng "A2:B30"

Anh ơi,

Tiếp cái vụ này đi.

Lê Văn Duyệt

 

[anhtuan1066]

Anh ơi,

Tiếp cái vụ này đi.

Lê Văn Duyệt

Cũng đơn giản thôi! Thuật toán dựa vào hàm FILES của macro4, nó có khả năng lấy list file trong 1 thư mục mà không cần vòng lập:

Sub ConsolMutiFiles(Folder As String, ShName As String, SrcRng As String, Target As Range)
  Dim Temp As String
  Temp = ShName & "'!" & Range(SrcRng).Address(, , 2)
  If Right(Folder, 1) <> "\" Then Folder = Folder & "\"
  ActiveWorkbook.Names.Add "Arr", "=""'" & Folder & "[""&Files(""" & Folder & "*.*"")&""]" & Temp & """"
  Target.Consolidate Evaluate("Arr"), 9, 0, 1
  ActiveWorkbook.Names("Arr").Delete
End Sub

Sub Test()
  Dim Folder As String, ShName As String, SrcRng As String
  Range("A2:B1000").ClearContents
  With CreateObject("Shell.Application")
    On Error Resume Next
    Folder = .BrowseForFolder(0, "", 1).Self.Path
  End With
  ShName = "Sheet1": SrcRng = "A2:B30"
  ConsolMutiFiles Folder, ShName, SrcRng, Range("A2")
End Sub

Vụ bẩy lỗi gì đó các bạn tự làm nha!

 

[anhtuan1066]

Hàm người dùng nằm trong nhóm "Hàm của tôi!"

Tôi xây dựng 1 hàm nào đó, code nằm trong 1 module, ví dụ thế này

Function MyFunction() As String
  MyFunction = "Test My Function"
End Function

Đương nhiên khi tôi vào menu Insert\Function thì tôi sẽ nhìn thấy hàm mà tôi xây dựng nằm trong nhóm "User Defined"



Vậy có khi nào ta sửa được chữ "User Defined" thành 1 tên nào khác không nhỉ? Chẳng hạn là "Hàm của tôi!"



Hi... hi...

 

[rollover79]

Tôi xây dựng 1 hàm nào đó, code nằm trong 1 module, ví dụ thế này

Function MyFunction() As String
  MyFunction = "Test My Function"
End Function

Đương nhiên khi tôi vào menu Insert\Function thì tôi sẽ nhìn thấy hàm mà tôi xây dựng nằm trong nhóm "User Defined"

View attachment 51077

Vậy có khi nào ta sửa được chữ "User Defined" thành 1 tên nào khác không nhỉ? Chẳng hạn là "Hàm của tôi!"

View attachment 51078

Hi... hi...

Giả sử hàm tên là test, sau khi thực hiện câu lệnh này sẽ thấy hiệu quả của nó

Application.MacroOptions Macro:="test", Description:="Thu test thoi ma", Category:="Ham cua toi"

Chú ý giá trị tham số Category, có thể thay bằng 1 tên có sẵn như "Text", hay "Logical"

 

[anhtuan1066]

Giả sử hàm tên là test, sau khi thực hiện câu lệnh này sẽ thấy hiệu quả của nó

Application.MacroOptions Macro:="test", Description:="Thu test thoi ma", Category:="Ham cua toi"

Chú ý giá trị tham số Category, có thể thay bằng 1 tên có sẵn như "Text", hay "Logical"

Đại ca làm "lộ chiêu" hết trơn rồi! Định "để dành" hỏi tiếp câu nữa: "Làm sao cho MyFunction và nhóm Date & Time"
Giờ thì coi như.... XONG! Hi... hi...

 

[tungson_mrcc]

Chuyện gì xảy ra với vòng lặp For...Next

Tôi có đoạn code như sau:

For i = 1 to erowNX
    if true then erowNX=erowNX + 1
next
msgbox "Gia tri hien tai cua i la " & i & chr(13) & "Gia tri hien tai cua erownx la " & erownx

Vấn đề là sau khi biến erowNX tăng, vòng lặp vẫn chạy theo giá trị ban đầu của erowNX. Dễ dàng kiểm tra điều này qua câu lệnh msgbox...

Nhờ các bạn giải thich lý do vì sao và xin hỏi có cách nào giải quyết tình huống này không?

Thân.