Chuyên đề giải đáp những thắc mắc về code VBA

maytinhvp01 · 31/7/13

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]

Quang_Hải · 12/4/21

huongmuine đã viết:
Mình sử dụng code của bạn trên diễn đàn để tô màu vùng dữ liệu K4:O55. Tuy nhiên nó tô màu không đúng vùng dữ liệu mình muốn (K4:O6548).
Mong các bạn chỉ lỗi sai giúp.
Cảm ơn rất nhiều.

Thay chỗ K4:O55 sẽ quyết định vùng cần hightlight khi thay đổi ô hiện hành

Mã:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect([K4:O55], Target) Is Nothing Then
    ToMau [K4:O55], 44, 3 '<--- doi so nay tu 1 den 4 de cho ra 4 kieu HighLight
  End If
End Sub

huhumalu · 12/4/21

Thanh Bình PV đã viết:
Cho em hỏi là em có 1 Form và 1 Button. Em mở Form sau đó nhấn Button sẽ ẩn Form đi và hiện InputBox để chọn ô..
Nhưng khi ẩn Form và hiện InputBox thì em phải click vào hộp thoại InputBox mới chọn ô được. Bình thường thì khi em gọi InputBox lên và chọn ô là được.
Bác nào giúp em với ạ. Em cảm ơn.

Ủa xài vẫn bình thường.

huongmuine · 12/4/21

Quang_Hải đã viết:
Thay chỗ K4:O55 sẽ quyết định vùng cần hightlight khi thay đổi ô hiện hành

Mã:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect([K4:O55], Target) Is Nothing Then ToMau [K4:O55], 44, 3 '<--- doi so nay tu 1 den 4 de cho ra 4 kieu HighLight End If End Sub

Cảm ơn Bạn đã giúp. Code Bạn trích dẫn là code dùng tô màu dòng và cột khi di chuyển chuột.
Nhờ Bạn xem giúp mình code thứ 2 với ( dùng để tô màu theo điều kiện mong muốn ).

Thanh Bình PV · 12/4/21

huhumalu đã viết:
Ủa xài vẫn bình thường.

Lỗi của em là sau khi nhấn vào Button (Get) thì ẩn form và hiện cái InputBox lên.. Nhưng em phải click vào hộp thoại InputBox mới chọn ô được.. Bình thường thì khi hộp thoại InputBox hiện lên thì mình chỉ chọn ô thôi..
Không biết máy bác có bị vậy không chớ máy e là bị như vậy.

huhumalu · 12/4/21

Thanh Bình PV đã viết:
Lỗi của em là sau khi nhấn vào Button (Get) thì ẩn form và hiện cái InputBox lên.. Nhưng em phải click vào hộp thoại InputBox mới chọn ô được.. Bình thường thì khi hộp thoại InputBox hiện lên thì mình chỉ chọn ô thôi..
Không biết máy bác có bị vậy không chớ máy e là bị như vậy.

Do GPE kg chạy .gif
Mình kg làm gì cả, chỉ bật lên và dùng, kg phải click cái hộp thoại inputbox làm gì cả.

Thanh Bình PV · 12/4/21

huhumalu đã viết:
Do GPE kg chạy .gif
Mình kg làm gì cả, chỉ bật lên và dùng, kg phải click cái hộp thoại inputbox làm gì cả.

À.. Em thử bên máy ảo thì lại chạy tốt.. Mà máy tính em thì cứ nhấn button thì chớp 1 phát là phải click vào hộp thoại InputBox

((.. Không biết tại sao.

nghiank09 · 12/4/21

Chào mọi người, xin mọi người hỗ trợ đoạn code để cuộn chuột (cuộn chầm chậm - nhằm để tăng hoặc giảm âm lượng của ứng dụng không đột ngột). Giả sử ứng dụng đó là VLC Media Player, đang ở cửa sổ thứ nhất (hot key Windows + 1).Mình xin cám ơn ạ.

huongmuine · 15/4/21

Nhờ các bạn giúp gộp code thứ 2 và thứ 3 thành code sự kiện Worksheet_Change.
Cảm ơn.

SA_DQ · 15/4/21

Bạn thử cái ni xem có đúng ý bạn không nha:

PHP:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim MyColor As Long
  If Not Intersect([A3:V55], Target) Is Nothing Then
    MyColor = 34 + Rnd() * 9 \ 1
    ToMau [A3:V55],  MyColor, 3 '<--- doi so nay tu 1 den 4 de cho ra 4 kieu HighLight
  End If 
End Sub

huongmuine · 15/4/21

SA_DQ đã viết:
Bạn thử cái ni xem có đúng ý bạn không nha:

PHP:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyColor As Long If Not Intersect([A3:V55], Target) Is Nothing Then MyColor = 34 + Rnd() * 9 \ 1 ToMau [A3:V55], MyColor, 3 '<--- doi so nay tu 1 den 4 de cho ra 4 kieu HighLight End If End Sub

Cảm ơn Bác SA_DQ nhiều; Kiểu highlight rất hay.

huongmuine · 15/4/21

SA_DQ đã viết:
Bạn thử cái ni xem có đúng ý bạn không nha:

PHP:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim MyColor As Long If Not Intersect([A3:V55], Target) Is Nothing Then MyColor = 34 + Rnd() * 9 \ 1 ToMau [A3:V55], MyColor, 3 '<--- doi so nay tu 1 den 4 de cho ra 4 kieu HighLight End If End Sub

Nhờ bác giúp ghép lại 2 code này cùng hoạt động theo sự kiện:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cll As Range, Rng As Range
With Sheets("BC")
Set Rng = .Range("K3:O" & .Cells(Rows.Count, "B").End(xlUp).Row)
Rng.Interior.Color = xlNone
For Each Cll In Rng
    Select Case Cll.Value
        Case "": Cll.Interior.Color = RGB(255, 255, 204)
        Case Is < 5: Cll.Interior.Color = RGB(153, 204, 255)
        Case "Y": Cll.Interior.Color = RGB(255, 102, 0)
        Case "K": Cll.Interior.Color = RGB(199, 192, 192)
        Case "G": Cll.Interior.Color = RGB(153, 204, 0)
    End Select
Next
End With
End Sub



Public Sub CongDiem()
Dim sArr(), dArr(), i As Long, R As Long
sArr = Range("B4", Range("B4").End(xlDown)).Resize(, 15).Value
R = UBound(sArr): ReDim dArr(1 To R, 1 To 2)
For i = 1 To R
    If sArr(i, 2) <> Empty Then
   
        dArr(i, 1) = IIf((sArr(i, 6) + sArr(i, 7) + sArr(i, 8) + sArr(i, 5)) / 2 < 5, (5 - ((sArr(i, 6) + sArr(i, 7) + sArr(i, 8) + sArr(i, 5))) / 2) * 2, "")
        dArr(i, 2) = Application.WorksheetFunction.Round((sArr(i, 6) + sArr(i, 7) + sArr(i, 8) + sArr(i, 5)) / 2, 0)
   End If
Next i
Range("J4:K100").Interior.Color = xlNone
Range("J4:K100").ClearContents
Range("J4").Resize(R, 2) = dArr
End Sub

Cảm ơn Bác.

SA_DQ · 16/4/21

Theo mình thấy thì 2 macro này hoàn toàn có 2 nhiệm vụ khác nhau mà; Thêm nữa, hình như macro dưới nên làm trước khi chạy macro đầu.
Vậy thì kết hợp hay không có cần thiết lắm không; mà chỉ thêm rầy rà hay phiền phức chực chờ(?)

huongmuine · 16/4/21

SA_DQ đã viết:
Theo mình thấy thì 2 macro này hoàn toàn có 2 nhiệm vụ khác nhau mà; Thêm nữa, hình như macro dưới nên làm trước khi chạy macro đầu.
Vậy thì kết hợp hay không có cần thiết lắm không; mà chỉ thêm rầy rà hay phiền phức chực chờ(?)

Cảm ơn Bác SA_DQ đã góp ý.

TrungLeNB · 5/5/21

Chào mn,

Mình cũng biết mò qua chút VBA để làm việc cho tiện.
Mn cho hỏi khi mình sử dụng mảng giá trị lớn Vd:Arr(1000000,5) , thì khi chạy code lần thứ 2 mà ko đống file sẽ bị đầy bộ nhớ. Mình google để khắc phục thì thấy nên dùng thêm đoạn dưới để giải phóng bộ nhớ: Application.CutCopyMode = False
Mình ko hiểu lắm chức năng đoạn này, và có cần để lại nó là True khi đóng code k.

Code của mình:

Sub CONSOL_FILES()
Dim WbX As Workbook, WbY As Workbook
Dim ShX1 As Worksheet, ShX2 As Worksheet
Dim ShY1 As Worksheet, ShY2 As Worksheet, ShY3 As Worksheet
Dim Files As Variant
Dim i&, j&, TOTAL&, Lr&, x&, y&, RowData&
Dim ARR(999998, 12), FileName As String

Set WbX = ThisWorkbook
Set ShX1 = WbX.Sheets("Data")
Set ShX2 = WbX.Sheets("Ref")

Files = Application.GetOpenFilename(, , , , True)

If VarType(Files) = vbBoolean Then
Exit Sub 'Neu chon nut "Cancel
End If

TOTAL = UBound(Files) - LBound(Files) + 1

Application.ScreenUpdating = False
Application.EnableEvents = False

On Error GoTo LB01

RowData = 0

For i = LBound(Files) To UBound(Files)
Application.StatusBar = "Consoling..." & Round((j + 1) / TOTAL * 100, 0) & "%"
FileName = GetFileNameDB(Files(i))
If HasWorkbook(FileName) Then
Workbooks(FileName).Close True
End If

Set WbY = Workbooks.Open(Files(i), False)
Set ShY1 = WbY.Worksheets("Export Worksheet")

'CONSOL RAW DATA
For x = 2 To GetEndRow(ShY1, "A")
For y = 1 To 12

ARR(RowData, y - 1) = ShY1.Cells(x, y).Value

Next y
ARR(RowData, 12) = Left(FileName, 14)
RowData = RowData + 1
Next x

WbY.Close False

LB02:
j = j + 1

Next i

With ShX1.Range("A2:M999999")
.ClearContents
.Value = ARR
End With

LB01:

ConvertToText ShX1, "M"

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.StatusBar = ""
Application.CutCopyMode = False

MsgBox "DONE!"

End Sub

Maika8008 · 5/5/21

TrungLeNB đã viết:
Chào mn,

Mình cũng biết mò qua chút VBA để làm việc cho tiện.
Mn cho hỏi khi mình sử dụng mảng giá trị lớn Vd:Arr(1000000,5) , thì khi chạy code lần thứ 2 mà ko đống file sẽ bị đầy bộ nhớ. Mình google để khắc phục thì thấy nên dùng thêm đoạn dưới để giải phóng bộ nhớ: Application.CutCopyMode = False
Mình ko hiểu lắm chức năng đoạn này, và có cần để lại nó là True khi đóng code k.

Code của mình:

Mấy dòng này chứ gì?
Application.ScreenUpdating = True => Để cập nhật lại màn hình sau khi chạy macro, tuy nhiên không có dòng này tôi thấy cũng không sao vì hết macro thì Excel cũng tự cập nhật kết quả lên màn hình máy tính.
Application.EnableEvents = True => Bật lại các sự kiện bảng tính. Nếu ở đầu có dòng Application.EnableEvents = False thì cuối buộc phải có dòng này.
Application.StatusBar = "" => Ở trên đã đặt StatusBar = gì gì đó rồi thì ở cuối phải set cho nó về = "" để khỏi thấy mãi cái thông tin kia (dù đã chạy xong macro rồi)
Application.CutCopyMode = False => Nếu trong lệnh có dòng Range(XX:YY).Copy thì cuối code cần có dòng này để hủy trạng thái vùng được copy có cái khung chạy chạy xung quanh. Không có cũng không sao nhưng hết macro thấy chạy chạy chướng mắt.

Vậy thôi, cả 4 dòng đều không có tác dụng trong việc giải phóng bộ nhớ.

TrungLeNB · 5/5/21

Maika8008 đã viết:
Mấy dòng này chứ gì?
Application.ScreenUpdating = True => Để cập nhật lại màn hình sau khi chạy macro, tuy nhiên không có dòng này tôi thấy cũng không sao vì hết macro thì Excel cũng tự cập nhật kết quả lên màn hình máy tính.
Application.EnableEvents = True => Bật lại các sự kiện bảng tính. Nếu ở đầu có dòng Application.EnableEvents = False thì cuối buộc phải có dòng này.
Application.StatusBar = "" => Ở trên đã đặt StatusBar = gì gì đó rồi thì ở cuối phải set cho nó về = "" để khỏi thấy mãi cái thông tin kia (dù đã chạy xong macro rồi)
Application.CutCopyMode = False => Nếu trong lệnh có dòng Range(XX:YY).Copy thì cuối code cần có dòng này để hủy trạng thái vùng được copy có cái khung chạy chạy xung quanh. Không có cũng không sao nhưng hết macro thấy chạy chạy chướng mắt.

Vậy thôi, cả 4 dòng đều không có tác dụng trong việc giải phóng bộ nhớ.

Thank bác. Mấy cái trên thì mình biết nhưng dòng Application.CutCopyMode = False giờ mới gặp.
Cho mình hỏi vậy làm như nào để giải phóng bộ nhớ khi mình chạy code có mảng dữ liệu lớn 2 lần.

Maika8008 · 5/5/21

TrungLeNB đã viết:
Thank bác. Mấy cái trên thì mình biết nhưng dòng Application.CutCopyMode = False giờ mới gặp.
Cho mình hỏi vậy làm như nào để giải phóng bộ nhớ khi mình chạy code có mảng dữ liệu lớn 2 lần.

Mảng hay sheet, book đều tự hủy và giải phóng bộ nhớ sau khi xong macro nên không cần lệnh gì cả. Các đối tượng mà người ta thường hay set như Scripting.Dictionary hoặc FileSystemObject thì sẽ chiếm bộ nhớ nếu không giải phóng chúng bằng lệnh Set XXX = Nothing. Tuy nhiên bạn cũng nên Set XXX = Nothing cho tất cả các biến XXX nếu trước đó đã Set XXX = Gì đó.

truongvu317 · 5/5/21

TrungLeNB đã viết:
Vd:Arr(1000000,5) ,

Dùng mảng to đùng như này mà không chủ động giải phỏng thì có lúc sẽ ăn Ram.
Dùng erase arr để giải phóng, mà khi khai báo là dim arr(10000000000,5) thì việc giải phóng có khi còn phản tác dụng

dim arr()
redim arr(1000000,5)

'code ahihihi ở đây

erase arr'giải phóng.

Ps:
Trong trường hợp này có khi dùng mảng động thì hợp lý hơn, không biết bác Vẹt với bác Rơi nghĩ sao vì cháu cũng chả hiểu rõ mấy cái cơ chế này.

Bài đã được tự động gộp: 5/5/21

Maika8008 đã viết:
Mảng hay sheet, book đều tự hủy và giải phóng bộ nhớ sau khi xong macro nên không cần lệnh gì cả

Trường hợp mảng nhỏ thì thằng VBa nó yêu thương chủ động giải phóng thì phải, mảng to quá nó không siêng như trước kia nữa thì phải,ahihi

batman1 · 5/5/21

truongvu317 đã viết:
Ps:
Trong trường hợp này có khi dùng mảng động thì hợp lý hơn, không biết bác Vẹt với bác Rơi nghĩ sao vì cháu cũng chả hiểu rõ mấy cái cơ chế này.

Theo tôi ...

Với khai báo

Mã:

Dim ARR(999998, 12)

thì trong không gian địa chỉ của process sẽ có một vùng ~ 208 MB được "đặt" cho nhu cầu của mảng Arr. Giá trị của vùng này có thể thay đổi trong suốt quá trình thực hiện process, nhưng độ lớn của nó luôn không đổi cho tới khi kết thúc process.
Với

Mã:

Dim ARR
...
Redim ARR(1 To 999999, 1 To 13)

Thì biến ARR thực ra chỉ chứa địa chỉ của mảng ARR, tức đúng 4 bai trong system 32 bit. Khi chạy Sub thì mới có "đặt" một vùng trong memory ~ 208 MB, và địa chỉ của vùng đó được "ghi nhớ" trong biến ARR. Trước khi ra khỏi sub thì vùng được đặt sẽ được giải phóng.

Có thể tự "ép" giải phóng bộ nhớ - ERASE, nhưng:
- với Dim ARR(999998, 12) thì vùng ~ 208 MB trong memory được "xóa", tức nội dung của vùng đó, nhưng bản thân vùng ~ 208 MB vẫn luôn tồn tại trong memory.
- Với Dim ARR thì vùng được đặt cho ARR sẽ được giải phóng, 4 bai dành cho ARR để ghi nhớ vùng trong memory dành cho ARR sẽ được điền bằng những bai 0. Chỉ có vùng 4 bai là luôn luôn tồn tại suốt quá trình thực hiện process.

Cũng chỉ có lợi khi biến ARR là toàn cục - khi biến là toàn cục mà không dùng ERASE thì vùng chiếm bởi ARR sẽ tồn tại cho tới khi đóng tập tin. Vì thế nếu không dùng nữa thì trước khi ra khỏi sub nào đấy thì dùng ERASE để "ép" giải phóng bộ nhớ. Khi ARR là biến cục bộ thì trước khi ra khỏi Sub VBA sẽ tự giải phóng bộ nhớ.

VetMini · 5/5/21

Cũng như chuỗi, mảng có hai dạng là dạng tĩnh và dạng động.

Dim a(1 To 100) as Integer là dạng khai báo tĩnh.
Khi đọc đến dòng này, trình dịch coi a là một mảng một chiều với 100 phần tử Integer. Và dạng này chết cứng luôn cho đến khi a không còn hiện hữu. Không thay đổi gì nữa cả. Trình dịch giành đủ chỗ chứa bấy nhiêu dữ liệu về mảng (mảng 1 chiều, chỉ số bắt đầu từ 1 đến 100, phần tử đầu tiên ở địa chỉ ***) và một dãy 200 bai để chứa các phần tử mảng (mỗi phần từ chiếm 2 bai). Lưu ý là bộ nhớ mà mảng dùng để chứa các phần tử phải liên tục, không được gián đoạn.
Nếu bạn dùng lệnh Erase a để xoá thì VBA chỉ xoá hết các trị của phần tử trong a thôi chứ a vẫn mãi mãi là một mảng Integer 100 phần tử, chiếm hữu bấy nhiêu trong bộ nhớ.
Chỉ khi a không còn hiện hữu, điển hình, exit/end Sub/Function khai báo nó thì trình dịch mới giải phóng bộ nhớ.

Dim a() as Integer là dạng khai báo động. Động ở đây nói về số phần tử và số chiều. Lưu ý rằng đoạn as integer xác định là mảng này chỉ chứa integers.
Khi đọc đến dòng này, trình dịch chỉ biết là bạn muốn một mảng ten a để chứa Integers. Nó chỉ giành riêng cho a của bạn cỡ một vài chục bai để chứa thông tin này.
Để có thẻ sử dụng a, bạn phải dùng một lệnh khác báo cho trình dịch cách giành bộ nhớ:
Redim a(1 To 10, 1 To 10) là lệnh xác định a.
Đọc đến dòng này, trình dịch sẽ giành thêm ra 200 bai để chứa 100 phần tử, và ghi vào chi tiết dữ liệu về a rằng: đây là một mảng 2 chiều, chiều 1 chỉ số 1 đến 10, chiều 2 chỉ số 1 đến 10, phần tử đầu tiên ở địa chỉ ***. Và vì đây là mảng, dù tĩnh hay động, trình dịch vẫn phải dùng một khoảng liên tục trong bộ nhớ cho dãy phần tử của a. Đó là lý do chính tại sao mảng lớn tốn nhiều bộ nhớ.
Mỗi lần bạn Redim (kể cả Redim Preserve) thì trình dịch lại tìm cho bạn một dãy bộ nhớ mới, và thảy bộ nhớ cũ trở vào cho ụ bộ nhớ (tức là giải phóng chỗ cũ này)
Khi bạn Erase a thì trình dịch chỉ phải giải phóng bộ nhớ cũ và không cần phải lấy mới cho a. Mảng a sẽ trở lại trạng thái giống như mới vừa được khai báo.

Như vậy, để làm nhẹ bộ nhớ, bạn có những lệnh căn bản sau:
- gán "" cho các biến string (nếu chúng dài qúa, ngắn thì chả bỏ công)
- Erase mảng động. Chỉ mảng động thôi, mảng tĩnh thì phải đợi ra khỏi tầm vực của biến.
- Set Nothing các Objects mà bạn dựng ra bằng lệnh CreateObject, hoặc lệnh New. Những đối tượng có sẵn của Excel luôn nằm đó, bạn chỉ ngưng con trỏ biến của mình vào chúng thôi. Việc set nothing này không chỉ quan trọng để giải phóng bộ nhớ mà còn giải phóng các tài nguyên khác tránh trường hợp bị khoá, bị rò bộ nhớ (ngày xưa ADO hay bị rò bộ nhớ nếu truy vấn trong cùng workbook cho nên người ta có thói quen set nothing ngay khi không còn dùng nữa, không đợi đến lúc end sub, biến ra khỏi tầm vực).

Chuyên đề giải đáp những thắc mắc về code VBA

Thành viên thường trực

Thành viên gạo cội

Thành viên tích cực

File đính kèm

Thành viên GPE

Thành viên hoạt động

Thành viên tích cực

Thành viên hoạt động

Thành viên hoạt động

Thành viên GPE

File đính kèm

/(hông là gì!

Thành viên GPE

Thành viên GPE

/(hông là gì!

File đính kèm

Thành viên GPE

Thành viên mới

Thành viên gạo cội

Thành viên mới

Thành viên gạo cội

Thành viên tích cực

Thành viên gạo cội

Ăn cùng góc phố

Đếm ngược thời gian