Chuyên đề giải đáp những thắc mắc về code VBA (2 người xem)

[maytinhvp01]

Mình muốn nhờ giải thich câu lệnh " If Ran.Cells(d, c) > max Then max = Ran.Cells(d, c) "
trong ví du:
Public Function LonNhat(Ran As Range)
Dim max As Double, v As Integer, d As Integer, c As Integer
max = Ran.Cells(1, 1)
For d = 1 To Ran.Rows.Count
For c = 1 To Ran.Columns.Count
If Ran.Cells(d, c) > max Then max = Ran.Cells(d, c)
Next c
Next d
v = Tim(max, Ran)
LonNhat = max
End Function
-------------------------------------------------------
[INFO1]Thông báo:
Vì topic này:
http://www.giaiphapexcel.com/forum/...ải-thích-các-code-đề-nghị-các-bạn-gửi-vào-đây
đã quá dài nên BQT đóng lại.
Nay tôi mở topic mới với cùng chủ đề: GIẢI THÍCH NHỮNG THẮC MẮC VỀ CODE
Các bạn nếu có nhu cầu giải thích code, vui lòng post tại đây nhé
NDU96081631
[/INFO1]

 

[ngoclam1088]

Xin nhờ cao thủ giúp em vấn đề này với.
Trong WORD em có đoạn văn bản như sau:
< bR>
Chào các bạn
< bR>
Hôm nay trời đẹp
< bR>
Kết thúc

Em muốn viết đoạn code để tự động tìm và thay thế chữ "<bR>" theo quy tắc
Câu 1
Chào các bạn
Câu 2
Hôm nay trời đẹp
Câu 3
Kết thúc

Tức là vừa tìm chữ <bR> vừa đếm xem nó là chữ xuất hiện lần thứ i và thay bằng chữ "Câu i".
Em cần gấp lắm mong được sự cứu giúp của anh em trong diễn đàn lắm ạ.
Em xin chân thành cảm ơn.

 

[lovefree123]

Mình tìm đc đoan code này ở trên mạng:
Sub run_sql_sub(sql)
On Error Resume Next
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


With cn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
ThisWorkbook.FullName _
& ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
.Open
End With
rs.Open sql, cn


Application.ScreenUpdating = False
ActiveSheet.Range("A2:XFD1048576").ClearContents


For intColIndex = 0 To rs.Fields.Count - 1
Range("A2").Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next


Range("A3").CopyFromRecordset rs
Application.ScreenUpdating = True
rs.Close: cn.Close: Set rs = Nothing: Set cn = Nothing
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range


Set KeyCells = ActiveSheet.Range("A1")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then


If InStr(KeyCells.Value2, "mi_sql") > 0 Then
sql = Right(KeyCells.Value2, Len(KeyCells.Value2) - Len("mi_sql "))
run_sql_sub sql
End If

End If

End Sub

Nhưng mỗi lần cần chạy câu lệnh SQL trong ô A1 lại phải đưa con trỏ lên ô A1 và ấn Enter. Bây giời mình muốn sửa lại để môi lần thay đổi là câu lệnh SQL tự động thực hiện. Cảm ơn

 

[minhtungph]

Nhưng mỗi lần cần chạy câu lệnh SQL trong ô A1 lại phải đưa con trỏ lên ô A1 và ấn Enter. Bây giời mình muốn sửa lại để môi lần thay đổi là câu lệnh SQL tự động thực hiện. Cảm ơn

Bạn nói rõ hơn, mỗi lần thay đổi là thay đổi thế nào không vậy, tức là thay đổi ở các ô khác hay sao?
- Nếu bạn muốn thay đổi 1 vùng từ A1 đến B10 đi chẳng hạn thì code thực hiện: bạn chỉ cần thay dòng: Set KeyCells = ActiveSheet.Range("A1") ====> Set KeyCells = ActiveSheet.Range("A1:B10"). Nói chung bạn chỉ cần thay đổi trong đó vùng bạn tác động

 

[Let'GâuGâu]

Mình tìm đc đoan code này ở trên mạng:
Sub run_sql_sub(sql)
On Error Resume Next
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


With cn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
ThisWorkbook.FullName _
& ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
.Open
End With
rs.Open sql, cn


Application.ScreenUpdating = False
ActiveSheet.Range("A2:XFD1048576").ClearContents


For intColIndex = 0 To rs.Fields.Count - 1
Range("A2").Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next


Range("A3").CopyFromRecordset rs
Application.ScreenUpdating = True
rs.Close: cn.Close: Set rs = Nothing: Set cn = Nothing
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range


Set KeyCells = ActiveSheet.Range("A1")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then


If InStr(KeyCells.Value2, "mi_sql") > 0 Then
sql = Right(KeyCells.Value2, Len(KeyCells.Value2) - Len("mi_sql "))
run_sql_sub sql
End If

End If

End Sub

Nhưng mỗi lần cần chạy câu lệnh SQL trong ô A1 lại phải đưa con trỏ lên ô A1 và ấn Enter. Bây giời mình muốn sửa lại để môi lần thay đổi là câu lệnh SQL tự động thực hiện. Cảm ơn

gõ HCM,DANANG,HANOI vào ô A1

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$A$1" Then
            sql = "select * from [vpp] where region=" & "'" & [A1] & "'"
            run_sql_sub sql
    End If
    
End Sub

 

[keke355992]

Các bác cho em hỏi 1 chút ạ. Trong Macro setting của em, sao cái dấu tích trust access to the vba project object model - Nó bị mờ đi ạ, k tích được, cũng k bỏ tích được. Như vậy liệu có ảnh hưởng đến các code VBA của mình ko ạ

 

[ndu96081631]

Các bác cho em hỏi 1 chút ạ. Trong Macro setting của em, sao cái dấu tích trust access to the vba project object model - Nó bị mờ đi ạ, k tích được, cũng k bỏ tích được. Như vậy liệu có ảnh hưởng đến các code VBA của mình ko ạ

Cũng chẳng sao cả, nhưng nếu bạn muốn nó "bình thường" trở lại thì làm như sau:
- Đóng toàn bộ Excel
- Bấm tổ hợp phím Windows + R (lá cờ windows và phím R)
- Gõ REGEDIT vào khung Open rồi Enter
- Duyệt tới đường dẫn:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\[COLOR=#ff0000]16.0[/COLOR]\Excel\Security

(Con số 16.0 màu đỏ ở trên là tùy theo phiên bản Office nha)
- Nhìn khung bên phải, nếu thấy mục có tên AccessVBOM thì xóa phéng nó đi
- Khởi động Excel và kiểm tra lại mục "Trust access.... "

 

[keke355992]

Cũng chẳng sao cả, nhưng nếu bạn muốn nó "bình thường" trở lại thì làm như sau:
- Đóng toàn bộ Excel
- Bấm tổ hợp phím Windows + R (lá cờ windows và phím R)
- Gõ REGEDIT vào khung Open rồi Enter
- Duyệt tới đường dẫn:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\[COLOR=#ff0000]16.0[/COLOR]\Excel\Security

(Con số 16.0 màu đỏ ở trên là tùy theo phiên bản Office nha)
- Nhìn khung bên phải, nếu thấy mục có tên AccessVBOM thì xóa phéng nó đi
- Khởi động Excel và kiểm tra lại mục "Trust access.... "

Theo đường dẫn của bác NDU thì nó hem ra ạ, mà e find trong regitry thì cái AccessVBOM nó nằm ở đường dẫn này ạ: HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Office\15.0\Excel\Security
Thêm cái mục lớn là Policies nữa ạ. e Đã xóa rùi, mà trong Excel - Marco seting - Cái trust access to the vba project object model nó vẫn bị mờ ạ.
Nhưng bác NDU đã nói k vấn đề gì thì ok ,kệ bố nó . Thank bác nhìu. Chúc bác ngày cuối tuần vui vẻ ạ

 

[keke355992]

Thuộc tính End trong VBA: [C65536].End(3)(1, 2). Các bác cho em hỏi, cái phần e bôi đậm nghĩa là sao vậy ạ !

 

[ndu96081631]

Thuộc tính End trong VBA: [C65536].End(3)(1, 2). Các bác cho em hỏi, cái phần e bôi đậm nghĩa là sao vậy ạ !

Truy xuất na ná như hàm INDEX ấy
Giả sử [C65536].End(3) ra được kết quả là cell C15 đi nha. Vậy cái tô đậm kia sẽ index 1 dòng 2 cột, ra kết quả là cell D15
Tổng quát: Tham chiếu(m, n) sẽ cho kết quả cell có dòng thứ m, cột thứ n tính từ cell tham chiếu
Lưu ý:
- Tại vị trí cell tham chiếu được tính là dòng 1, cột 1
- m và n có thể có giá trị âm

 

[keke355992]

Truy xuất na ná như hàm INDEX ấy
Giả sử [C65536].End(3) ra được kết quả là cell C15 đi nha. Vậy cái tô đậm kia sẽ index 1 dòng 2 cột, ra kết quả là cell D15
Tổng quát: Tham chiếu(m, n) sẽ cho kết quả cell có dòng thứ m, cột thứ n tính từ cell tham chiếu
Lưu ý:
- Tại vị trí cell tham chiếu được tính là dòng 1, cột 1
- m và n có thể có giá trị âm

Vậy mình có thể dùng offset ko ạ. [C65536].end(3).offset(m,n). 2 cách này có khác nhau ko ạ

 

[Ba Tê]

Vậy mình có thể dùng offset ko ạ. [C65536].end(3).offset(m,n). 2 cách này có khác nhau ko ạ

Từ [C15].Offset(1, 2) sẽ là [E16].

 

[keke355992]

Từ [C15].Offset(1, 2) sẽ là [E16].

Vâng. E hỉu rùi. Nó sẽ khác nhau vị trí tham chiếu. Cảm ơn 2 bác nhìu ạ

 

[keke355992]

Trong Sub này

Sub TinhTien()
On Error Resume Next
For Each cls In Range([c3], [c65536].End(3))
If cls > 0 Then
cls(1, 2) = Sheets("Don Gia").Cells.Find(cls(1, 0), , , 2)(1, 2)
cls(1, 3) = cls * cls(1, 2)
End If
Next
End Sub

Cho e hỏi với ạ: Tại vị trí em bôi đậm, e chưa hiểu điều kiện cần tìm này là gì ạ !

 

[minhtungph]

Trong Sub này

Sub TinhTien()
On Error Resume Next
For Each cls In Range([c3], [c65536].End(3))
If cls > 0 Then
cls(1, 2) = Sheets("Don Gia").Cells.Find(cls(1, 0), , , 2)(1, 2)
cls(1, 3) = cls * cls(1, 2)
End If
Next
End Sub

Cho e hỏi với ạ: Tại vị trí em bôi đậm, e chưa hiểu điều kiện cần tìm này là gì ạ !

Giả sử cls là ô C7 thì cls(1,0) là ô B7.

 

[keke355992]

Giả sử cls là ô C7 thì cls(1,0) là ô B7.

Hôm qua đến h còn mông lung các loại tham chiếu kiểu này, h ngộ ra rùi, thank a nha

 

[minhtungph]

Truy xuất na ná như hàm INDEX ấy
Giả sử [C65536].End(3) ra được kết quả là cell C15 đi nha. Vậy cái tô đậm kia sẽ index 1 dòng 2 cột, ra kết quả là cell D15
Tổng quát: Tham chiếu(m, n) sẽ cho kết quả cell có dòng thứ m, cột thứ n tính từ cell tham chiếu
Lưu ý:
- Tại vị trí cell tham chiếu được tính là dòng 1, cột 1
- m và n có thể có giá trị âm

Xin thầy chia sẽ thêm là: tham chiếu kiểu này và offset cơ bản có cùng bản chất, vậy sử dụng cái nào sẽ tối ưu hơn ạ, và thầy thường sử dụng phương pháp nào, em cám ơn

 

[dinhquang042000]

Dear all,

Em có viết 1 file báo cáo công ty bằng VBA khi chạy trên máy ở nhà thì code chạy ổn định, Nhưng khi sang máy khác bị lỗi ở modul 6, bước cuối cùng tổng hợp báo cáo. Mong mọi người xem xét chỉ giúp em lỗi của file này RUN TIME ERRO 7 OUT OF MEMORY. (Do file này nặng em tải nên drive, các anh chị tải về xem giúp em)
Rát mong nhận được sự giúp đỡ của mọi người. Em xin chân thành cảm ơn.

https://drive.google.com/file/d/0B7_P2xWKkzz7dWhoU3JHck9JZzA/view

 

[ndu96081631]

Xin thầy chia sẽ thêm là: tham chiếu kiểu này và offset cơ bản có cùng bản chất, vậy sử dụng cái nào sẽ tối ưu hơn ạ, và thầy thường sử dụng phương pháp nào, em cám ơn

Nhìn thì có vẻ giống! Tức nếu viết Range("C7")(2, 2) thì các bạn nghĩ nó tương đương với Range("C7").Offset(1,1) đúng không?
Nếu vậy thì người ta "đẻ" thêm cái kiểu (2,2) kia làm gì cho nhức đầu
???!!!
Ta hãy làm cuộc thí nghiệm thế này:
- Quét chọn C7:G15 rồi thực hiện lệnh Merge chúng lại với nhau
- Vào cửa sổ VBA, bấm Ctrl + G (mở cửa sổ Immediate)
- Gõ vào khung Immediate dòng lệnh ?Range("C7")(2,2).Address rồi Enter phát, ta nhận được kết quả $D$8 (Ổn đúng không?)
- Tiếp theo gõ vào khung Immediate lệnh ?Range("C7").Offset(1,1).Address rồi Enter phát, ta nhận được kết quả $H$16 (bất ngờ quá đúng không?)
--------------------------
Vậy ta có thể tạm kết luận rằng:
- Kiểu viết Range("...").Offset(m-1,n-1) sẽ giống với kiểu Range("...")(m,n) cho trường hợp cell thường chưa Merge
- Kiểu viết Range("...").Offset(....) tương đương với thao tác chọn 1 cell rồi dùng các phím mũi tên để di chuyển. Chẳng hạn tình huống trên, nếu chọn C7 (đã Merge) rồi bấm phím mũi tên xuống và mũi tên phải cùng lúc, activeCell cũng sẽ được di chuyển đến H16 ---> Đó chính là Range("C7").Offset(1,1) đấy
- Ngoài cells bị merge ra, Offset còn gặp rắc rối tương tự khi có dòng ẩn, cột ẩn hoặc bảng tính có fillter (nó sẽ "nhảy" linh tinh khiến ta ngơ ngác không hiểu mình sai ở đâu)
- Kiểu viết Range(m,n) luôn luôn cho kết quả đúng như những gì mình nghĩ trong đầu, bất kể cell có bị merge, bị ẩn hay filter
--------------------------
Hiểu được sự khác nhau giữa 2 cách viết rồi, các bạn quyết định xài cái nào là.. tùy tình huống. Riêng tôi, ít khi thích xài Offset

 

[minhtungph]

Nhìn thì có vẻ giống! Tức nếu viết Range("C7")(2, 2) thì các bạn nghĩ nó tương đương với Range("C7").Offset(1,1) đúng không?
Nếu vậy thì người ta "đẻ" thêm cái kiểu (2,2) kia làm gì cho nhức đầu
???!!!
Ta hãy làm cuộc thí nghiệm thế này:
- Quét chọn C7:G15 rồi thực hiện lệnh Merge chúng lại với nhau
- Vào cửa sổ VBA, bấm Ctrl + G (mở cửa sổ Immediate)
- Gõ vào khung Immediate dòng lệnh ?Range("C7")(2,2).Address rồi Enter phát, ta nhận được kết quả $D$8 (Ổn đúng không?)
- Tiếp theo gõ vào khung Immediate lệnh ?Range("C7").Offset(1,1).Address rồi Enter phát, ta nhận được kết quả $H$16 (bất ngờ quá đúng không?)
--------------------------
Vậy ta có thể tạm kết luận rằng:
- Kiểu viết Range("...").Offset(m-1,n-1) sẽ giống với kiểu Range("...")(m,n) cho trường hợp cell thường chưa Merge
- Kiểu viết Range("...").Offset(....) tương đương với thao tác chọn 1 cell rồi dùng các phím mũi tên để di chuyển. Chẳng hạn tình huống trên, nếu chọn C7 (đã Merge) rồi bấm phím mũi tên xuống và mũi tên phải cùng lúc, activeCell cũng sẽ được di chuyển đến H16 ---> Đó chính là Range("C7").Offset(1,1) đấy
- Ngoài cells bị merge ra, Offset còn gặp rắc rối tương tự khi có dòng ẩn, cột ẩn hoặc bảng tính có fillter (nó sẽ "nhảy" linh tinh khiến ta ngơ ngác không hiểu mình sai ở đâu)
- Kiểu viết Range(m,n) luôn luôn cho kết quả đúng như những gì mình nghĩ trong đầu, bất kể cell có bị merge, bị ẩn hay filter
--------------------------
Hiểu được sự khác nhau giữa 2 cách viết rồi, các bạn quyết định xài cái nào là.. tùy tình huống. Riêng tôi, ít khi thích xài Offset

Dạ em cám ơn thầy rất nhiều ạ, đúng là chỉ có trải nghiệm nhiều mới biết được những cái này.

 

[ndu96081631]

Dạ em cám ơn thầy rất nhiều ạ, đúng là chỉ có trải nghiệm nhiều mới biết được những cái này.

Ở trên chỉ mới nêu vài sự khác nhau cơ bản nhất. Vẫn còn nhiều cái khác nhau nữa, bạn hãy tự khám phá nhé