LÀM VIỆC VỚI MẢNG TRONG VBA - Bugatino's Note

LÀM VIỆC VỚI MẢNG TRONG VBA

Nếu bạn đã làm quen với các ngôn ngữ lập trình khác, tôi tin rằng bạn cũng đã từng nghe qua khái niệm về mảng.

Mảng là một tập hợp của nhiều biến liên quan (related variables) được gọi qua một tên. Ví dụ: Samples. Bạn có thể xác định một thành phần trong mảng thông qua một chỉ mục. Ví dụ: Samples(1): tức là lấy giá trị của phần tử có thứ tự là 1 trong mảng Samples.

KÍCH THƯỚC CỦA MỘT MẢNG

Khai báo Dim (viết tắt của chữ Dimention) được dùng để khai báo kích thước của một mảng. Thông thường phần tử của mảng sẽ được bắt đầu bằng chỉ mục 0. Vì vậy  khi khai báo

Dim Samples(10)

Khi bạn khai báo như trên, có nghĩa mảng Samples của bạn có 11 phần tử.

Nếu muốn mảng được bắt đầu bằng chỉ mục 1, bạn phải khai báo

Option Base 1

ở đầu mỗi module mà bạn muốn.

Nhằm tránh việc nhằm lẫn các bạn nên luôn sử dụng mảng được bắt đầu bằng phần tử có chỉ mục 0.

Trong trường hợp khác bạn cũng có thể thực hiện được điều này bằng việc khai báo tường minh như sau:

Dim Samples(1 to 10)

Sử dụng tên của biến mảng để tham chiếu đến một mảng.

Mảng có thể có nhiều chiều, nhưng thông thường là mảng hai chiều.

Ví dụ khai báo mảng hai chiều như sau:

Dim Samples(500,2)

KHAI BÁO KIỂU BIẾN CỦA MẢNG

Khai báo kiểu biến cho các phần tử rất quan trọng tại sao? Vì với một mảng có nhiều phần tử, nếu bạn khai báo không rỏ ràng thì tài nguyên bộ nhớ sẽ bị chiếm dụng.

Dim TenBien(Lower To Upper) As Type ‘Lower: cận dưới; Upper: cận trên

Khi khai báo nhiều biến bạn có thể dùng một khai báo Dim, nhưng phải cách nhau bằng As Type cho mỗi biến. Vì vậy bạn phải khai báo như sau:

Dim J As Integer, K As Integer

TRẢ VỀ KÍCH THƯỚC CỦA MỘT MẢNG

Chúng ta dùng hai hàm LBound và UBound để xác định kích thước dưới và trên của mảng.

LBound(ArrayName, Dimension)

UBound(ArrayName, Dimension)

ArrayName: tên mảng.

Dimension: chiều của mảng cần kiểm tra.

Ví dụ: khi chúng ta khai báo

Dim Samples(500,2)

LBound(Samples,1) sẽ trả về 500

UBound(Samples, 2) sẽ trả về 2

MẢNG ĐỘNG (DYNAMIC ARRAY)

Nếu bạn không biết kích thước của mảng khi khai báo, bạn phải dùng mảng động. Mảng động cho phép bạn khai báo mảng và khai báo kích thước mảng sau.

Bạn sẽ khai báo mảng bình thường nhưng không cần khai báo kích thước như sau:

Dim Mang1(), Mang2()

Sau đó để khai báo lại bạn dùng từ ReDim

Giả sử sau khi bạn khai báo mảng động như trên, sau đó bạn biết được mảng bạn sử dụng cần bao nhiêu phần tử bạn sẽ dùng ReDim để khai báo lại như sau:

SoPhanTu= Xvalues.Count

ReDim Mang1(SoPhanTu), Mang2(SoPhanTu)

Trong một thủ tục bạn có thể dùng nhiều lần ReDim. Khi bạn dùng ReDim thì các phần tử bên trong của mảng sẽ bị xóa. Ví dụ:

Dim Mang1()

Dim SoPhanTu As Integer, i As Integer

SoPhanTu=100

ReDim Mang1(SoPhanTu)

For i=1 to SoPhanTu

Mang1(i)= i

Next i

SoPhanTu=101

ReDim Mang1(SoPhanTu)

Mang1(SoPhanTu)=SoPhanTu

Trong ví dụ trên, lần đầu tiên bạn khai báo số phần tử là 100, sau đó gán giá trị cho các phần tử của mảng là chính số thứ tự của các phần tử. Sau đó bạn dùng ReDim để khai báo lại số phần tử và gán phần tử mới, tức là phần tử thứ 101 của mảng, với giá trị là 101.

Bạn chú ý cho rằng các phần từ từ 1 đến 100 đã bị xóa giá trị khi bạn dùng khai báo ReDim.

Vậy nếu muốn không xóa giá trị của các phần tử trước đây chúng ta phải dùng từ khóa Preserve để khai báo.

Dim Mang1()

Dim SoPhanTu As Integer, i As Integer

SoPhanTu=100

ReDim Mang1(SoPhanTu)

For i=1 to SoPhanTu

Mang1(i)= i

Next i

SoPhanTu=101

ReDim Preserve Mang1(SoPhanTu)

Mang1(SoPhanTu)=SoPhanTu

Chú ý rằng khai báo Preserve chỉ có tác dụng ở cận trên của chiều (của mảng) cuối cùng mà thôi. Do đó nếu bạn khai báo như sau là hợp lệ:

Dim Mang1(2,100)

.

.

ReDim Preserve Mang1(2,SoPhanTu)

Nhưng nếu bạn khai báo như sau sẽ bị lỗi

Dim Mang1(2,100)

.

.

ReDim Preserve Mang1(SoPhanTu,2)

Nếu bạn sử dụng Preserve, bạn không thể dùng ReDim để thay đổi số phần tử của một mảng.

LÀM VIỆC VỚI MẢNG TRONG MỘT THỦ TỤC:

TRUYỀN GIÁ TRỊ TỪ Worksheet vào VBA Module

Có hai cách để lấy giá trị từ Worksheet vào trong mảng VBA. Bạn có thể dùng vòng lập để lấy giá trị của mỗi ô và lưu giá trị này vào các thành phần của một mảng (đây là cách thường hay sử dụng) hoặc bạn có thể gán mảng VBA cho một Worksheet Range.

Tùy theo cách bạn dùng hai cách thức ở trên, mà tốc độ thực hiện chương trình của bạn nhanh hay chậm. Theo bạn hai cách thức trên cách thức nào nhanh, cách thức nào chậm? Tôi nghĩ bạn có thể dễ dàng so sánh !

MỘT RANGE XÁC ĐỊNH CÓ THỂ DÙNG NHƯ MỘT MẢNG TRONG MỘT THỦ TỤC

Nếu một biến trong VBA gán cho một (đối tượng) Range, thì biến này có thể sử dụng như một mảng.

ThuMang=Range(“A1:A10”)

Hoặc nếu bạn đã đặt tên cho vùng A1:A10 là MangTam thì các bạn có thể dùng như sau:

ThuMang=Range(“MangTam”)

Lúc này mảng chúng ta sẽ có hai chiều, chiều của hàng và chiều của cột. Số hàng trở thành số phần tử của chiều 1, số cột trở thành số phần tử của chiều 2.

Array(Rows, Columns)

Một điều chú ý nữa là cho dù bạn không khai báo Option Base 1 (có nghĩa là thứ tự mảng sẽ được bắt đầu từ 1) thì khi bạn dùng mảng theo kiểu này (tức là từ một Range) thì thứ tự mảng bao giờ cũng bắt đầu từ 1.

Chú ý nữa là, giá trị trong các ô của Range chưa được chuyển vào mảng VBA (internal VBA array); mảng VBA chỉ đơn giản “points” vào mảng trên worksheet. Tuy nhiên các giá trị trong Range có thể được truy cập giống như trong một mảng.

Theo các bạn sự khác nhau giữa

ThuMang=Range(“A1:A10”) ‘ Cách 1

Và

Set ThuMang=Range(“A1:A10”) ‘ Cách 2

Là gì?

Đối với cách 1, bạn có thể dùng các hàm LBound và UBound để xác định cận trên và cận dưới của mảng.

Đối với cách 2, bạn phải dùng

ThuMang.Rows.Count

và

ThuMang.Columns.Count

để xác định số phần tử.

Chuyển các giá trị từ một mảng VBA sang Worksheet

Bạn có ít nhất hai cách để chuyển các giá trị từ một mảng VBA vào Worksheet.

1. Dùng vòng lập.
2. Gán giá trị từ mảng VBA vào các giá trị của WorksheetRange.

Cách thứ hai có thể gây lỗi khi bạn làm việc với mảng một chiều. Xin xem ví dụ sau:

Range(“E1:E10”).Value=TestArray

Dòng lệnh này tạo ra các giá trị giống nhau

Giả sử tôi có các giá trị từ ô A1 đến ô A10 như hình bên. Sau đó các bạn hãy copy đọan mã sau vào một module và thực hiện thủ tục TestArray. Option Explicit Option Base 1 Sub TestArray() Dim Mang1(10) Dim i          As Integer For i = 1 To 10 Mang1(i) = Range(“A” & i).Value Next i Range(“B1:B10”).Value = Mang1 End Sub

Kết quả sẽ như hình sau:

Giá trị của ô A1 (phần tử đầu tiên của Mang1, tức là bằng 1) sẽ được gán cho các ô từ B1 đến B10 như hình bên.

Nhưng nếu

Giả sử tôi có các giá trị từ ô A1 đến ô A10  và B1 đến B10 như hình bên. Sau đó các bạn hãy copy đọan mã sau vào một module và thực hiện thủ tục TestArray. Option Explicit Option Base 1 Sub TestArray() Dim Mang1(10, 10) Dim i          As Integer For i = 1 To 10 Mang1(i, 1) = Range(“A” & i).Value Mang1(i, 2) = Range(“B” & i).Value Next i Range(“C1:D10”).Value = Mang1 End Sub

Bạn sẽ được kết quả như hình sau:

Các giá trị trong mảng sẽ được gán trở lại choRange đúng như các bạn mong đợi.

Với lỗi này chúng ta có ít nhất ba cách để khắc phục.

_ Cách thứ nhất chúng ta sẽ dùng vòng lập. Đây là cách thường dùng.

_ Cách thứ hai chúng ta sẽ khai báo thêm chiều thứ hai của mảng.

Option Explicit Option Base 1 Sub TestArray() Dim Mang1(10, 1) Dim i          As Integer For i = 1 To 10 Mang1(i, 1) = Range(“A” & i).Value Next i Range(“B1:B10”).Value = Mang1 End Sub

Và chúng ta sẽ có kết quả đúng như hình bên.

_ Cách thứ ba chúng ta dùng hàm TRANSPOSE  của Application.WorksheetFunction

Option Explicit

Option Base 1

Sub TestArray()

Dim Mang1(10)

Dim i          As Integer

For i = 1 To 10

Mang1(i) = Range(“A” & i).Value

Next i

Range(“B1:B10”).Value = Application.WorksheetFunction.Transpose(Mang1)

End Sub

HÀM NGƯỜI DÙNG/CUSTOM FUNCTION

Chúng ta nói một ít về hàm người dùng vì phần này có liên quan đến vấn đề mảng chúng ta đang nói ở trên.

Hàm (function) có thể hiểu đơn giản như sau:

Hàm là một chương trình máy tính (Computer Program) để tính tóan và trả về một giá trị cho (các) ô mà bạn nhập vào.

 

Khi viết một hàm người dùng chúng ta nên xác định kiểu biến truyền cho hàm.

Ví dụ:

Function MoIW(Formula As String, Decimals As Integer)

Nếu tham số truyền không đúng kiểu, một thông báo lỗi #Value! sẽ được trả về.

 

Tương tự ở trên chúng ta cũng nên xác định kiểu trả về của hàm người dùng.

Function MoIW(Formula As String, Decimals As Integer) As Double

Để trả về một giá trị lỗi từ một hàm thông thường chúng ta viết như sau:

If(TimThayLoi) Then TenHam=”Thong bao loi”: Exit Function

Nhưng đây không phải là cách tốt nhất để xử lý (handle) lỗi. Chúng ta dùng CVErr(Giá trị lỗi) để trả về giá trị lỗi worksheet của Excel mà Excel có thể xử lý.

If(TimThayLoi) Then TenHam=CVErr(xlErrNA): Exit Function

Đối với một số hàm, một số tham số không bắt buộc truyền vào cho hàm. Đối với trường hợp này chúng ta sẽ dùng từ khóa Optional để khai báo.

Function MoIW(Formula As String, Optional Decimals As Integer) As Double

Để kiểm tra người dùng có nhập vào biến này hay không chúng ta có thể dùng IsMissing để kiểm tra.

MẢNG SỬ DỤNG TRONG HÀM

Bạn có thể tạo một hàm và dùng mảng như một đối số (Argument), hoặc trả về một mảng các kết quả.

 

Range được truyền cho hàm và có thể được sử dụng như một mảng.

Nếu một đối số Range được chuyển cho một hàm , thìRange có thể được xử lý như một mảng trong VBA. Không cần thiết phải khai báo Dim.

Ví dụ: Function MyLinest(known_x, known_y)

 

Truyền số lượng không xác định các đối số cho hàm.

Các bạn để ý, khi chúng ta sử dụng hàm SUM chúng ta thể truyền vào nhiều đối số.

Ví dụ: SUM(“A1:A10”, “B3:C5”)

Đây chính là trường hợp chúng ta muốn đề cập đến.

Gặp trường hợp này chúng ta sẽ dùng từ khóa ParamArray.

Function ArrayMaker(ParamArray rng())

Chú ý, chỉ có một đối số trong một hàm được khai báo ParamArray và đối số này phải là đối số cuối cùng trong dãy các đối số của một hàm.

Đối số được khai báo ParamArray là một mảng các biến, và phải kết thúc bởi ().

Chú ý cận dưới của mảng này bao giờ cũng bắt đầu từ 0, ngay cả khi các bạn có khai báo Option Base 1

Function ArrayMaker(ParamArray rng())

For J = 0 To UBound(rng)

YSize = rng(J).Columns.Count

For K = 1 To YSize

statements

Next K

Next J

Trả về một mảng các giá trị

Hàm sau trả về một mảng các giá trị

Function MyLINEST(known-ys, known-xs)

Dim Results(3)

Results( 1) = MySlope

Results(2) = Mylntercept

Results(3) = MyRSq

MyLlNEST = Results

End Function

Các thứ hai là chúng ta dùng từ khóa Array

Function MyLINEST(known-ys, known-xs)

MyLINEST = Array(MySlope,My Intercept, MyRSq)

End Function

Nếu dùng từ khóa Array, chỉ trả về mảng chỉ có một chiều nếu muốn trả về một mảng hai chiều chúng ta phải dùng Array(Array(…))

Hy vọng với kiến thức được tổng hợp bên trên sẽ giúp các bạn trong VEC.

Lê Văn Duyệt

23/10/07

levanduyet@yahoo.com

www.giaiphapexcel.com

Chia sẻ:

• Twitter
• Facebook

Thích Đang tải...

Có liên quan