TÀI LIỆU LẬP TRÌNH - TẠO BẢNG HÀM CHO RIÊNG BẠN

Chia sẻ: Nguyễn Khắc Việt | Ngày: | Loại File: DOC | Số trang:23

Thêm vào BST

Báo xấu

172
lượt xem 56
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Nếu bạn có một phép tính toán phức tạp mà bạn sử dụng thường xuyên trong Excel, bạn không cần phải nhập nhiều lần một công thức dài phức tạp. Thay vào đó, bạn có thể tạo các hàm của riêng bạn để thực hiện các tính toán. Bạn có thể sử dụng hàm để tạo ra các công thức được dễ dàng hơn để nhập và duy trì.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: TÀI LIỆU LẬP TRÌNH - TẠO BẢNG HÀM CHO RIÊNG BẠN

A/ Tạo Bảng Hàm Cho Riêng Bạn Nếu bạn có một phép tính toán phức tạp mà bạn sử dụng thường xuyên trong Excel, bạn không cần phải nhập nhiều lần một công thức dài phức tạp. Thay vào đó, bạn có th ể t ạo các hàm c ủa riêng bạn để thực hiện các tính toán. Bạn có thể sử dụng hàm đ ể tạo ra các công th ức đ ược d ễ dàng hơn để nhập và duy trì. Để tạo các hàm riêng của bạn, bạn làm việc trong Microsoft Visual Basic ® cho các ứng dụng (VBA). VBA- Visual Basic for Appliction là một ngôn ngữ lập trình được xây dựng vào Excel. VBA rất linh hoạt và có thể làm tất cả mọi thứ mà các công thức Excel có thể làm, và nhiều hơn thế nữa. Ví dụ, bạn có một công thức phức tạp để tìm hoa hồng bán hàng, nơi tỷ lệ phần trăm hoa hồng thay đổi dựa trên nhiều yếu tố như những gì các sản phẩm và kết hợp của các sản phẩm đại di ện bán hàng, cho dù các đại diện bán hàng nhỏ hơn hạn ngạch và tổng doanh s ố bán hàng cho quý. Thay vì nhập công thức dài mà mất tất cả những yếu tố này vào tài khoản mỗi khi b ạn muốn tính hoa hồng để bán, bạn có thể tạo ra một lệnh chức năng tùy chỉnh. Sau đó, bạn phải gõ các công thức hoa hồng của bạn là tên hàm của bạn Hơn nữa, thời gian tới công ty thay đổi các quy tắc cho cách tính hoa hồng, bạn không phải tìm và thực hiện thay đổi tẻ nhạt cho tất cả các công thức phức tạp. Bạn chỉ cần phải thay đ ổi ở một nơi, các hàm tuỳ chỉnh, để cập nhật tất cả các công thức trong bảng tính của bạn Các bước sau đây cho thấy làm thế nào để tạo ra và sử dụng một hàm tùy chỉnh. Đ ể ví d ụ đ ơn giản, chức năng này tính toán hoa hồng bán hàng tại một tỷ lệ đúng 6%. Đ ể tận dụng đ ầy đ ủ các hàm tùy chỉnh, bạn sẽ thay thế mã VB phức tạp hơn cho tính toán tỷ lệ phần trăm đơn giản 1. Trên menu Tools, chọn Macro, và sau đó nhấp vào Visual Basic Editor. 2. Trên menu Insert trong cửa sổ Microsoft Visual Basic, bấm vào Module. 3. Trong cửa sổ Modulen, gõ code cho hàm của bạn. Ví dụ, một hàm để tính toán một khoản hoa hồng bán hàng 6% có thể trông như thế này: Dòng đầu tiên trong ví dụ trên tạo ra một hàm tên là Lệnh, thực hiện các tính toán của nó về một số hoặc tham chiếu trong ô. chức năng này sử dụng một biến, MyNum, đ ể l ưu tr ữ số l ượng hoặc giá trị từ các ô. Dòng thứ hai tính toán giá trị của hàm lệnh bằng cách nhân số hoặc tham chiếu trong ô (MyNum) bằng 0, 06 (một hàm phức tạp hơn có thể có một vài dòng mã để thực hiện các phép tính của nó). Dòng thứ ba kết thúc đoạn mã hàm. 4. Trên menu File, nhắp chuột vào Close and Return to Microsoft Excel.
5. Trên bảng tính, hãy sử dụng hàm của bạn trong các công thức như bạn sẽ làm ở b ất kỳ hàm tính nào. Ví dụ, bạn có thể sử dụng hàm lệnh để tính hoa hồng trên doanh số bán hàng: Một hàm mà bạn tạo ra theo cách này có thể chỉ để sử dụng trong một bảng tính mà bạn tạo ra nó. Nếu bạn muốn sử dụng hàm khác, bạn có thể sao chép mã cho hàm VBA module trong bảng tính khác, hoặc, nếu bạn là một chuyên viên thiết kế, bạn có thể sao chép các hàm vào một bảng tính được sử dụng như một thư viện hàm và biên soạn bảng tính như là một chương trình bổ xung. Lưu ý Ví dụ trong bài viết này rất đơn giản để cho bạn thấy những điều cơ bản. Để tạo các hàm phức tạp trong VBA, bạn sẽ cần một số kiến thức về các mẫu đối tượng VBA trong Excel 2000, các cấu trúc ngôn ngữ VB, và môi trường VBA. Ví dụ, bạn muốn khai báo đối số và trả lại kiểu dữ liệu trong bất kỳ hàm bạn tạo ra. Để bắt đầu học về lập trình trong VBA, xem các tài liệu tham kh ảo dưới đây. Xem Thêm Thông Tin Để biết thêm thông tin về việc tạo công thức bao gồm các hàm, chức năng nhập công thức trong Office Assistant hoặc trên tab Answer Wizard trong cửa sổ Trợ giúp của Excel, và sau đó nhấn Search. Để biết thông tin về việc tạo các hàm tùy chỉnh trong VBA, gõ quy trình hàm trong Office Assistant hay trên tab Answer Wizard trong cửa sổ trợ giúp của Excel Visual Basic, nhấp vào tìm kiếm- Search, và sau đó nhấp vào chủ đề "Viết một quy trình tạo hàm" và "Câu Lệnh Hàm." Nếu bạn chưa quen với lập trình VBA, thì Hướng dẫn của trương trình Microsoft Office 2000/Visual Basic có thông tin để giúp bạn từ bước đầu. Để biết thông tin làm thế nào để có được hướng dẫn này, bạn gõ dòng chữ programmer’s guide-(chương trình hướng dẫn) trong Office Assistant hoặc trên tab Answer Wizard trong cửa sổ trợ giúp, sau đó nhấn Search. CÁCH TẠO HÀM USER DEFINED TRONG EXCELL 1. Mở một workbook mới. 2. Vào VBA (Nhấn Alt + F11)
3. Chèn một module mới (Insert> Module) 4. Copy và Paste ví dụ hàm user defined trong excel 5. Thoát khỏi VBA (Nhấn Alt + Q) 6. Sử dụng hàm (nó sẽ xuất hiện trong hộp thoại Paste Function, Shift + F3, theo thể loại “User Defined ") Nếu bạn muốn sử dụng một UDF (Universal Disk Format) ở nhiều hơn một workbook, bạn có thể lưu các hàm của bạn trong phần bổ xung tùy chỉnh của riêng bạn. Đ ơn giản chỉ c ần l ưu file excel của bạn có chứa VBA của bạn có hàm như một file bổ xung (có đuôi .xla).. Sau đó nhập phần bổ xung (Tools> Add-Ins ...). Cảnh báo! Hãy cẩn thận về cách sử dụng hàm tùy chỉnh trong bảng tính mà bạn cần phải chia sẻ với người khác. Nếu họ không có phần bổ xung của bạn thì các hàm sẽ không hoạt động khi họ sử dụng các bảng tính. LỢI ÍCH CỦA HÀM USER DEFINED Tạo một hàm toán học tùy chỉnh hoặc phức tạp.  Đơn giản hóa công thức mà nếu không sẽ rất dài "công thức lớn"  Chẩn đoán như việc kiểm tra định dạng ô.  Tùy chỉnh thao tác text-văn bản.  Công thức ma trận nâng cao và hàm ma trận.  GIỚI HẠN CỦA UDF • Không thể "ghi" một UDF Excel như ghi một macro Excel. • Thêm hạn chế hơn so với macro VBA thông thường. UDF không thể thay đổi cấu trúc hoặc định dạng của một bảng tính hoặc ô tính. • Nếu bạn gọi (lệnh gọi) một hàm khác hay macro từ một UDF, các macro đó có mức hạn chế tương tự thấp hơn UDF. • Không thể đặt một giá trị trong ô tính ngoại trừ ô (hoặc vùng) đó có chứa công th ức. Nói cách khác, giới hạn của UDF có nghĩa là để được sử dụng như "công thức", không nhất thiết phải là "macro". • Hàm user defined excel trong VBA thường chậm hơn nhiều so với hàm biên soạn trong C + + hoặc FORTRAN (ngôn ngữ lập trình bậc cao……) • Thường khó khăn để theo dõi lỗi. • Nếu bạn tạo một phần bổ xung có chứa giới hạn UDF của bạn, bạn có thể quên rằng bạn đã sử dụng một hàm tùy chỉnh, làm giảm khả năng chia sẻ của file.  Thêm các hàm user defined cho workbook của bạn sẽ kích hoạt cờ hiệu "macro" (thực hiện vấn đề bảo mật trong: Tools> Macros> Security...). VÍ DỤ VỀ HÀM USER DEFINED
Ví dụ 1: Tạo địa chỉ đến một đường liên kết Ví dụ sau có thể hữu ích khi giải nén siêu liên kết từ bảng hoặc đường link đã đ ược sao chép vào Excel, khi làm phần xử lý cuối cùng trên các truy vấn web Excel, hoặc nhận được địa chỉ email từ danh sách các siêu liên kết “mailto” Hàm này cũng là một ví dụ về cách sử dụng đối số UDF Excel tùy chọn. Cú pháp cho hàm tùy chỉnh Excel này là: =LinkAddress(cell,[default_value]) Để xem một ví dụ về cách làm việc với đối số tùy chọn, tìm kiếm lệnh IsMissing trong các tập tin trợ giúp của Excel VBA (nhấn F1). Function LinkAddress(cell As range, _ Optional default_value As Variant) 'Lists the Hyperlink Address for a Given Cell 'If cell does not contain a hyperlink, return default_value If (cell.range("A1").Hyperlinks.Count 1) Then LinkAddress = default_value Else LinkAddress = cell.range("A1").Hyperlinks(1).Address End If End Function Ví dụ 2: Giải nén các phần tử thứ N từ một Chuỗi ký tự Ví dụ này cho thấy làm thế nào để tận dụng lợi thế của một số hàm có sẵn trong VBA đ ể làm một số thao tác văn bản hoàn chỉnh. Điều gì nếu bạn đã có một chuỗi số điện thoại định dạng sau: 1-800-999-9999 và bạn muốn lấy ra chỉ 3 ký số đầu tiên? UDF này lấy các đối số như lấy chuỗi văn bản, số lượng các phần tử mà bạn muốn lấy (n), và dấu phân cách như một ký tự (ví dụ "-"). Cú pháp của ví dụ về hàm user defined trong Excel là: =GetElement(text,n,delimiter) Ví dụ: Nếu cột B3 chứa dãy số "1-800-333-4444" và cột C3 chứa công thức =GetElement(B3,3,"-") thì cột C3 sẽ cho ra kết quả là “333”. Để đưa “333” về dạng số ta nên sử dụng =VALUE(GetElement(B3,3,"-")). Function GetElement(text As Variant, n As Integer, _ delimiter As String) As String
'Returns the nth element from a delimited text string Dim txt, str As String Dim count, i As Integer 'Manipulate a copy of the text string txt = text 'If a space is used as the delimiter, remove extra spaces If delimiter = Chr(32) Then txt = Application.Trim(txt) 'Add a delimiter to the end of the string If Right(txt, Len(txt)) delimiter Then txt = txt & delimiter End If 'Initialize count and element count = 0 str = "" 'Get each element For i = 1 To Len(txt) If Mid(txt, i, 1) = delimiter Then count = count + 1 If count = n Then GetElement = str Exit Function Else str = "" End If Else str = str & Mid(txt, i, 1) End If Next i
GetElement = "" End Function Ví dụ 3: UDF cho một Công thức Toán Tùy Chỉnh Một trong những điều thú vị về tuỳ chỉnh các hàm Excel là bạn có thể đ ơn giản hóa các công thức Excel mà nếu không sẽ sử dụng lồng nhau câu lệnh Nếu ... thì .... Ví dụ, giả sử chúng ta có một hàm đơn giản, bao gồm phép chia, nhưng công thức thay đổi khi số chia là số 0. Tương tự, chúng ta muốn làm một số kiểm tra lỗi, vì vậy chúng ta không kết thúc với # VALUE trên tất cả các bảng tính của chúng ta. Đối với ví dụ này, chúng ta sẽ xem xét các công th ức KEI ( Keyword Effectiveness Index mục lục hiệu ứng của từ khóa), mà khi một cái gì đó trông đơn giản như thế này khi sử dụng tích hợp hàm Excel: IF(supply=0,demand^2,demand^2/supply) Cú pháp cho hàm user defined là: =KEI(demand,supply,[default_value]) Function KEI(demand As Variant, supply As Variant, _ Optional default_value As Variant) As Variant 'Keyword Effectiveness Index (KEI) If IsMissing(default_value) Then default_value = "n/a" End If If IsNumeric(demand) And IsNumeric(supply) Then If supply = 0 Then KEI = demand ^ 2 Exit Function Else KEI = demand ^ 2 / supply Exit Function End If End If KEI = default_value End Function B / Xây dựng một Excel Add-In (phần bổ xung cho Hàm Excel) Giới thiệu về Add-Ins
Một Excel Add-in là một tập tin (thường có đuôi mở rộng là . xla hoặc .xll) mà Excel có thể nhập vào khi nó khởi động. Các tập tin có chứa mã (VBA trong trường hợp phần đuôi mở rộng là . xla) mà có thêm hàm bổ xung cho Excel, thường ở dạng các hàm mới. Add-Ins cung cấp một phương pháp tối ưu về gia tăng sức mạnh của Excel và chúng là những phương tiện lý tưởng để phân loại các hàm tùy chỉnh của bạn. Excel đ ược gắn với một tr ạng thái khác của Add-Ins sẵn sàng cho bạn nhập liệu và bắt đầu sử dụng, và nhiều Add-Ins thứ ba có sẵn. Bài viết này cho bạn thấy làm thế nào để viết một hàm tùy chỉnh bằng cách sử dụng Excel VBA và làm thế nào để lưu và cài đặt nó như là một Add-In. Các hàm tùy chỉnh thường được gọi là UDFs (User Defined Functions – các hàm xác định đối tượng sử dụng). Nếu bạn chưa bao giờ xây dựng một UDF trước đây thì đây là một nơi tốt để bạn bắt đầu, hoặc bạn muốn xem hướng dẫn Cách viết hàm VBA cơ bản trong Excel là phần giải thích cụ thể với những ví dụ khác nữa. Trong Excel hướng dẫn, Tính toán tuổi của một người - Giới thiệu về câu lệnh với hàm IF lồng (dùng nhiều hàm IF trong một câu lệnh) tôi trình bày cách sử dụng câu lệnh IF để tính toán tuổi của một ai đó từ ngày sinh của họ. Thật ngạc nhiên, Excel không có sẵn hàm cho một yêu cầu mang tính phổ biến này, vì vậy nó-UDF là một ứng viên lý tưởng cho một chức năng tùy chỉnh. Nếu bạn đã yên tâm với chức năng-hàm tùy chỉnh bằng văn bản, bạn có thể đi th ẳng vào phần giải thích làm thế nào để lưu UDFs của bạn như là một Add-In. [xem tiếp phần Add-In] Thực hành viết Hàm Một Add-In có thể chứa nhiều UDFs như bạn muốn, và bạn có thể thêm nhiều hơn chỉ đơn giản bằng cách mở và chỉnh sửa các file Add-In. Bước 1: Thêm/chèn một Mã Module cho một Workbook Khởi động Excel hoặc, nếu bạn đã có Excel mở, hãy tạo một bảng tính mới (rỗng): Mở Visual Basic Editor từ Tools> Macro> Visual Basic Editor (nhấn tổ hợp phím ALT + F11). Trong cửa sổ Project Explorer chọn VBAProject (Book1). Sự lựa chọn này sẽ cho ra một workbook trống. Nếu bạn đã được làm việc trong Excel với một cái tên khác – hãy kiểm tra tên trong thanh tiêu đề của cửa sổ Excel nếu bạn không chắc chắn. Nếu Project Explorer không hiển thị thì hãy mở nó bằng cách vào View> Project Explorer Module.
Từ menu Insert chọn Module. Thao tác này đã thêm một mã module rỗng cho bảng tính được chọn. Bạn cũng sẽ thấy module xuất hiện trong cửa sổ Project Explorer. Bước 2: Nhập Mã Trong cửa sổ mã hãy gõ dòng... Function Age(DoB As Date) …và nhấn ENTER. Các trình soạn thảo VB tự động đi nhập dòng "End Function" ( kết thúc hàm) và đặt con trỏ của bạn trong khoảng trống giữa chúng (giữa dòng lệnh và dòng End Function). Khi bạn gõ chữ “Function” là để xác định các mã được viết tiếp theo cũng là 1 hàm, trái ngược với một macro hay một thường trình con (subroutine), được biểu thị bằng chữ “Sub”. Tiếp theo là tên của hàm, bạn có thể đặt bằng bất cứ tên gì mà bạn thích. Nhưng hãy đặt tên hàm đơn giản và dễ diễn tả. Không cho phép có khoảng trống giữa chữ và dấu gạch chân (ví dụ Age_in_years) hoặc, tốt hơn là, với chữ viết hoa (ví dụ AgeInYears). Một hàm thông thường cần một hoặc nhiều "đối số". chúng được đưa vào trong dấu ngoặc sau tên hàm. Một đối số là một phần thông tin mà hàm sử dụng để thực hiện các phép tính của nó. Khi trình bày các đối số nó là sự thực hành tốt để xác định loại dữ liệu. Trong trường hợp này chỉ có một đối số là cần thiết, ngày sinh sẽ được sử dụng để tính toán tuổi của người đó. Đ ể đơn giản hơn, tôi đã gọi là đối số này là "DoB". Các đối số hoạt động giống như các biến trong các macro. Khi bạn gõ từ "As" sau tên của đối số thì trình soạn thảo VB sẽ hiển thị cho bạn một danh sách có sẵn. Bạn có thể nhập hoặc chọn từ danh sách. Giữa dòng lệnh hàm và câu khai báo End Function (kết thúc hàm), bấm TAB (để đoạn mã tiếp theo thụt đầu dòng cho rõ ràng, dễ nhìn) và gõ dòng...
Age = Int((Date - DoB) / 365.25) Điều này cho biết cách viết hàm trong Excel. Nó sử dụng hai hàm có sẵn, hàm INT (là hàm đ ể lấy một dãy số - hoặc số nguyên – số ngoài kết quả) và hàm DATE (một hàm Visual basic tương đương với hàm TODAY trong excel là hàm trả về ngày hiện tại). Câu lệnh đó được đọc là… "Lấy ngày hiện tại trừ đi ngày sinh. Chia kết quả đó cho 365,25 và hiển thị các đáp án như là một số nguyên bằng cách làm tròn xuống." Hàm được hoàn tất hiển thị như thế này: Function Age(DoB As Date) Age = Int((Date - DoB) / 365.25) End Function Bước 3: Kiểm tra Hàm Bạn có thể kiểm tra hàm ngay lập tức. Chuyển sang Excel trong một bảng tính mới (một b ảng tính tương tự mà bạn đang sử dụng để tạo ra mã module của hàm) nhập một ngày vào ô tính. Trong 1 ô khác nhập vào hàm của bạn, cách nhập tương tự như bạn nhập hàm có sẵn của Excel, ví d ụ như = Age (A1) Một UDF có giá trị để mở các bảng tính bất cứ khi nào bảng tính chủ (bảng tính có ch ứa các module code của UDF) được mở. Tuy nhiên, nếu bạn cố gắng sử dụng hàm trong một bảng tính khác thì bạn có thể gặp phải trục trặc... Các bảng tính khác không thể tìm thấy hàm mà bạn tạo nên xuất hiện báo l ỗi # NAME? Để tránh điều này xảy ra bạn có thể thêm tên bảng tính của máy chủ (book1) vào hàm đ ể Excel biết được nơi tìm thấy hàm...
Cách này vẫn cho ra kết quả nhưng rất phiền hà và chứng minh một trong những lý do giải thích tại sao nơi tốt nhất cho các hàm tùy chỉnh của bạn là bên trong một Excel Add-In (với một số ngoại lệ ... xem thêm tại http://www.fontstuff.com/vba/vbatut03.htm#caution#caution.) Thêm tính năng cho hàm Cho phép cho Thiếu dữ liệu Một điều bất lợi của nhiều hàm là khi bạn chuẩn bị một bảng tính trước khi nhận dữ li ệu c ủa nó (ví dụ như trong một mẫu mà bạn muốn các hàm được đặt ở vị trí sẵn sàng đ ể tính d ữ li ệu nhập vào của người dùng) các hàm cố gắng để tính toán các ô trống, thường báo lỗi hay kết quả vô nghĩa. Nếu hàm Age của chúng ta tính toán một ô trống vẫn tạo ra một kết quả, trong tr ường h ợp này là 102 (năm hiện tại là 2002). Điều này xảy ra bởi vì nó có giá trị của ô trống là 0, và diễn giải số 0 như là ngày 0 tháng một năm 1900. Điều này là hợp lý với Excel vì ngày đ ầu tiên nó bi ết là ngày 1, tháng một năm 1900. Thông thường bạn sẽ cố gắng để dự đoán hàm này bằng cách nhập vào tuổi c ủa bạn nh ư là một phần của câu lệnh hàm IF. Ví dụ = IF (ISBLANK (A1 ),"", age (A1)) Câu lệnh IF cho biết Excel dùng để nhập một giá trị của "con số 0" (được đại diện bởi "") nếu ô dữ liệu là trống nhưng để tính toán hàm Age nếu nó chứa một giá trị... Điều này làm việc tốt, nhưng bạn có thể tránh cho mình những rắc rối bằng việc kết hợp xử lý sự cố loại này vào trong mã của chính hàm đó. Sửa đổi hàm các mã như sau: Function Age(DoB As Date) If DoB = 0 Then Age = "" Else Age = Int((Date - DoB) / 365.25)
End If End Function Ngoài ra một thông báo lỗi tùy chỉnh có thể bao gồm cách thay thế các cặp dấu ngoặc kép trong code ở dòng 3 với một thông báo trong dấu ngoặc kép, ví dụ như Age = "Không ngày sinh". Kết quả sẽ được... Tăng thêm độ chính xác cho hàm Phép tính được sử dụng trong ví dụ trên là rất chính xác, nhưng không hoàn toàn chính xác. Nó hoạt động trên nguyên tắc có số một trung bình là 365,25 ngày trong một năm (thường là 365 ngày nhưng là 366 ngày mỗi 4 năm) vì vậy chia tuổi của người với ngày là 365,25 thì nên cho tuổi của họ vào trong nhiều năm. Các hoạt động này gần như ổn mọi lúc nhưng cũng có lỗi dù rất hiếm. Nếu người đó có ngày sinh nhật là ngày hôm nay và được sinh ra vào một năm là bội số của 4 năm tr ước thì kết qu ả tính sẽ cho ra là 1 năm. Một khả năng nhỏ, nhưng nếu chúng ta sẽ làm điều đó chúng ta cũng có thể làm điều đó đúng! Trong hướng dẫn Tính toán tuổi của một người - Giới thiệu về câu lệnh với hàm IF lồng tôi trình bày cách dùng câu lệnh IF trong Excel để tính tuổi của một ai đó từ ngày sinh c ủa họ với đ ộ chính xác hoàn toàn. Tôi có thể làm như vậy trong VBA cho hàm tùy chỉnh c ủa mình (mặc dù cú pháp của một câu lệnh VBA IF là hơi khác nhau trong Excel) nhưng tôi thích sử dụng một câu lệnh CASE hơn. Excel không có câu lệnh CASE nhưng VBA thì có. Tôi thấy câu lệnh CASE dễ tính hơn câu lệnh IF để tính toán khi sự logic là hơi phức tạp. Sau đây là mã cho hàm cải tiến của tôi: Function Age(DoB As Date) If DoB = 0 Then Age = "No Birthdate" Else Select Case Month(Date) Case Is < Month(DoB) Age = Year(Date) - Year(DoB) - 1 Case Is = Month(DoB)
If Day(Date) >= Day(DoB) Then Age = Year(Date) - Year(DoB) Else Age = Year(Date) - Year(DoB) - 1 End If Case Is > Month(DoB) Age = Year(Date) - Year(DoB) End Select End If End Function MẸO: Chọn dòng mã trong ô màu xám ở trên, sao chép chúng (nhấn phím kép: CTRL + C) và dán trực tiếp vào cửa sổ code VBA của bạn (nhấn phím kép: CTRL + V). Cách thực hiện code Đặt tên cho hàm và hiển thị một Hàm Age(DoB As Date) date of birth tham số đơn mà bắt buộc phải có ngày tháng năm Một câu lệnh IF để xác định trong ô If DoB = 0 Then dữ liệu có giá trị hay ko. Giá trị của Age = "No Birthdate" một ô rỗng được xem là 0. Nếu điều đó là đúng thì hàm trả về thông báo "Không Ngày sinh". Nếu ô dữ liệu là rỗng thì hãy xem Else như tháng đó là ngày hôm nay Select Case Month(Date) Nếu tháng của ngày hôm nay nhỏ Case Is < Month(DoB) hơn tháng của ngày sinh thì chắc chắn Age = Year(Date) - Year(DoB) - 1 họ phải có ngày sinh, vì vậy tuổi của họ được tính là năm nay trừ đi năm sinh và trừ thêm 1 Nếu tháng của ngày hôm nay trùng Case Is = Month(DoB) với tháng của năm sinh thì chúng ta cần biết họ có ngày sinh hay không, vì vậy…
Nếu ngày hôm nay trùng với ngày If Day(Date) >= Day(DoB) Then sinh nhật của họ thì tuổi của họ sẽ tính Age = Year(Date) - Year(DoB) là năm nay trừ đi năm sinh… … nói một cách khác, họ phải có Else ngày sinh nhật, do đó tuổi của họ là lấy Age = Year(Date) - Year(DoB) - 1 năm nay trừ đi năm sinh và trừ đi 1 End If Nếu tháng của ngày hôm nay lớn Case Is > Month(DoB) hơn tháng của ngày sinh, thì tuổi của Age = Year(Date) - Year(DoB) họ sẽ bằng năm nay trừ đi năm sinh Đóng câu lệnh CASE, câu lệnh IF End Select và hàm End If End Function Cách tính này có thể có vẻ khá phức tạp nhưng bạn chỉ phải nhập nó một lần! Khi bạn đã tạo ra hàm của mình thì khi làm việc bạn sẽ nhập tên hàm mà bạn đã đặt Tạo Phần Bổ Xung Cho Hàm Trong Excel – Add-In Bước 1: Thêm một mô tả cho hàm. Khi một hàm được đưa vào các hướng dẫn hàm (ví dụ như công cụ dán Hàm) người dùng thấy một mô tả về hàm giúp họ lựa chọn những điều đúng. Đây không phải là trường hợp với hàm tùy chỉnh [nhấn Ctrl+click vào hình nhỏ dưới đây để xem một hình ảnh với kích cỡ đầy đủ]... Nếu bạn muốn có phần mô tả bạn thì bạn phải tự thêm nó vào. Đây là cách thực hiện ... Vào Tools> Macro> Macros để mở hộp thoại Macro. Bạn sẽ thấy tất cả các macro có sẵn được liệt kê nhưng không có chức năng tùy chỉnh. Trong phần Macro name: bạn gõ tên hàm bạn muốn mô tả vào hộp văn bản
Nhấp vào nút [Options] nút để mở hộp thoại Macro Options. (Nếu nút Options là màu xám thì tên hàm bạn đã nhập chưa được công nhận. Kiểm tra lại bạn đã gõ đúng hay chưa ) Nhập mô tả cho hàm của bạn trong mục Description: nhập trong hộp văn bản... Nhấn [OK] để đóng hộp thoại Macro Options và sau đó click [Cancel] để đóng hộp thoại Macro. Mô tả hiện đã được gắn vào hàm. Bạn có thể lặp lại các quy trình này đ ể s ửa đổi các mô t ả nếu cần. Mô tả này được hiển thị khi chức năng tùy chỉnh được chọn trong click Function Wizard [nhấn Ctrl+click vào hình nhỏ dưới đây để xem một hình ảnh với kích cỡ đầy đủ]... Bước 2: Lưu Workbook như là một Add-In Workbook chứa module code của bạn bây giờ đã được lưu như một file bổ xung cho hàm của Excel với đuôi mở rộng là (*. xla). Trong cửa sổ Excel vào File> Save để mở hộp thoại Save As. Nhập tên file bổ xung và sử dụng Save as type: tùy chọn để thay đổi loại tập tin Microsoft Excel Add-In (*. xla).
Trước khi click vào [OK] kiểm tra vị trí mà bạn đang lưu các file Add-In. Điều này sẽ khác nhau tùy theo đặc thù mà bạn thiết lập. Các vị trí trên máy tính của tôi là: Excel XP(2002) on Windows XP: C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns Excel 2000 on Windows 98: C:\Windows\Application Data\Microsoft\AddIns Excel 97 on Windows 98: C:\Program Files\Microsoft Office\Office\Library Bạn có thể lưu trữ bất cứ nơi nào bạn thích, nếu bạn muốn nó đ ược li ệt kê cùng v ới nh ững phần Add-In có sẵn thì bạn nên lưu nó vào đúng vị trí. Excel XP và Excel 2000 tự động sẽ đưa bạn vào thư mục chính xác nhưng Excel 97 thì không. Lời khuyên: Để kiểm tra vị trí lưu, đầu tiên cài đặt cho phần Save as type: vào Microsoft Excel Add-In sau đó mở Save in: danh sách thả xuống để lộ đường dẫn đến thư mục lưu. Bạn có thể đóng bảng tính gốc (Book 1). Bạn không cần phải lưu các thay đổi khi có thông báo hỏi lưu hay không. Bước 3: Thêm một mô tả cho các Add-In Đó là một ý tưởng hay để thêm một mô tả cho các Add-In. Mô tả này sẽ được hiển thị trong hộp thoại Add-Ins khi bạn chọn một Add-In để cài đặt. Đầu tiên sử dụng phần quản lý tập tin để xác định vị trí file Add-In c ủa bạn. Nhấp chuột ph ải vào biểu tượng file và chọn Properties từ menu ngữ cảnh. Trong hộp thoại thuộc tính tập tin nhấp vào tab Summary. Gõ mô tả cho Add-In của bạn trong phần Comments: tại hộp văn bản. Nếu muốn, bạn cũng có thể gõ 1 tên cho Add-In của mình trong Title: tại hộp văn bản. Điều này rất hữu ích nếu bạn đã lựa chọn một tên ngắn hoặc khó hiểu cho các tập tin *.xla c ủa b ạn nh ưng mu ốn hiển thị nhiều hơn một tên mô tả trong hộp thoại Add-Ins. Cuối cùng nhấn [OK] để lưu sự thay đổi. Phần Add-In của bạn đã sẵn sàng cho việc cài đặt, và có thể được phân bổ cho những người dùng khác nếu có yêu cầu. Bước 4: Cài đặt các Add-In
Nếu Excel không được đóng cửa kể từ khi bạn tạo Add-In (hoặc từ khi một phần Add-In được sao chép vào của đĩa cứng máy tính) hãy khởi động lại Excel để đảm bảo r ằng nó làm mới danh sách Add-In có sẵn. Vào Tools> Add-Ins để mở hộp thoại Add-Ins. Nếu bạn đã lưu phần Add-In của mình ở vị trí mặc định thì bạn sẽ thấy tên của nó hiển thị trong Add-Ins có sẵn ( Add-Ins available): trong 1 cửa sổ (nếu bạn đã lưu trữ Add-In của mình trong một thư mục khác, hãy sử dụng nút [ Browse] để tìm nó). Nhắp chuột vào tên của Add-In để xem mô tả của nó ở phía dướicủa hộp thoại. Để cài đặt Add-In, hãy đánh dấu vào hộp kiểm tra bên cạnh tên Add-In c ủa bạn và bấm [OK]. (nhấn Ctrl+click vào hình nhỏ dưới đây để xem một hình ảnh với kích cỡ đầy đủ)... Ngay sau khi các Add-In được cài đặt thì chức năng của nó sẽ được ứng dụng trong Excel. Ta có thể tìm thấy chúng trong phần User Defined của Function Wizard (Paste Function Tool) hoặc chỉ cần gõ chúng vào một ô như bất cứ một hàm có sẵn nào. Các Add-In sẽ vẫn được cài đ ặt cho đ ến khi bạn quay lại hộp thoại Add-Ins và bỏ cài đặt nó bằng cách bỏ đánh dấu chọn từ hộp kiểm tra. Tạo Bổ Sung và Thay Đổi một Add-In File Add-In của bạn có thể chứa nhiều module và các chức năng tùy chỉnh như bạn muốn. Bạn có thể thêm chúng vào bất kỳ lúc nào. Nếu Add-In của bạn được cài đặt bạn sẽ thấy nó được liệt kê trong cửa sổ Project Explorer của các trình soạn thảo VB (Visual Basic). Xác định vị trí các module có chứa các hàm của bạn và bổ sung hay thay theo ý bạn. Nếu Add-In của bạn không được cài đ ặt, hãy tìm file Add-In và kích đúp vào nó để mở nó trong Excel. Bạn sẽ không thể nhìn thấy nó trong cửa sổ Excel nhưng nó s ẽ xuất hiện trong Project Explorer của trình soạn thảo VB. Nhớ lưu các thay đổi của bạn! Thực hiện điều này từ cửa sổ trình soạn thảo VB File> Save (nhấn Ctrl+click vào hình nhỏ dưới đây để xem một hình ảnh với kích cỡ đầy đủ)
Thêm Ghi Chú cho Code Của Bạn Thêm ghi chú vào code VBA (Visual Basic for Application) của bạn rất đơn giản (đúng cách gọi là "ghi chú"). Nhập một dấu nháy đơn (một nửa của dấu ngoặc kép) và sau đó gõ ghi chú. Dấu nháy đơn để chương trình biên dịch mã bỏ qua tất cả mọi thứ theo sau nó trên cùng một dòng nên không được coi là mã thực thi và trình biên dịch không chạy nó. Bất cứ điều gì bạn gõ sau dấu nháy đơn là "ghi chú" và được hiển thị bằng màu xanh lá cây trong cửa sổ mã của trình soạn thảo VB... Bạn có thể sử dụng kỹ thuật này để thêm ghi chú giải thích cho mã của bạn (khi nào xem lại một số mã mà bạn đã viết thời gian trước và tự hỏi chúng có ý nghĩa gì?). Bạn cũng có thể tạm thời viết ghi chú một số dòng mã để chúng không chạy – điều này hữu ích cho việc kiểm tra code của bạn. Nếu bạn nghĩ rằng bạn không cần một dòng mã, đ ừng xóa nó mà hãy viết ghi chú cho nó trước. Sau đó kiểm tra mã của bạn đ ể xem nó còn ho ạt đ ộng hay không. Nếu mọi thứ đều OK bạn có thể xoá dòng đó. Nếu mã của bạn không chạy, chỉ cần loại bỏ các dấu nháy đơn và dòng code của bạn được phục hồi. Bảo Vệ Code của Bạn bằng Mật Khẩu. VBA cho phép bạn bảo vệ code của mình bằng mật khẩu, cho dù đó là mã macro sau một bảng tính thông thường hay mã mô-đun của một Add-In. Code được bảo vệ bằng mật khẩu sẽ ngăn chặn sự xâm nhập để xem hay kiểm tra trái phép mã code của bạn nhằm mục đích an ninh hay quyền tác giả.
Để bảo vệ Add-In của bạn bằng mật khẩu, xác định vị trí nó trong VB Editor. Kích chuột ph ải vào tên của nó trong cửa sổ Project Explorer và chọn VBAProject Properties... từ menu ngữ cảnh. Trong hộp thoại VBAProject – Project Properties nhấp vào tab Protection,. Đặt một dấu chọn trong hộp kiểm tra Lock project for viewing, sau đó nhập và xác nhận mật khẩu của bạn. Nhấn [OK] và vào File> Save để lưu thay đổi. [Nhấn Ctrl+click vào hình nhỏ dưới đây để xem một hình ảnh với kích cỡ đầy đủ]... Việc bảo vệ mật khẩu sẽ có hiệu lực từ lần mở Excel kế tiếp. Bất kỳ cố gắng nào để xem các mã Add-In thì module sẽ xuất hiện một bảng yêu cầu nhập mật khẩu... Gán Macros Add-In vào Nút Thanh Công Cụ Tùy Chỉnh và các Mục Menu. Các Add-Ins không chỉ dùng để chứa các hàm mà chúng còn có thể chứa các macro hữu ích. Bạn có thể gán một macro được chứa trong một Add-In cho một nút thanh công cụ tùy chỉnh hay mục trình đơn tùy chỉnh. Thông thường để làm điều này bạn nhấp chuột phải vào mục mới của bạn hoặc mục menu và chọn Assign Macro và lấy macro của bạn từ danh sách được cung cấp. Tuy nhiên, tên của các macro có trong Add-Ins không được hiển thị trong danh sách. Đừng lo lắng! Chỉ cần gõ tên c ủa các macro trong Macro Name: hộp ở trên cùng của hộp thoại. Excel sẽ tìm thấy nó và macro sẽ chạy như mong đợi khi click vào nút hoặc chọn mục menu.
Nhắc Nhở Cuối Cùng! Một hàm tùy chỉnh mà nằm trong một code mô-đun trong phạm vi một bảng tính sẽ đi bất c ứ nơi nào bảng tính đi. Nói cách khác nếu bạn mở file workbook trên một máy khác, hoặc gửi e-mail file đó cho người khác thì hàm sẽ di chuyển cùng với bảng tính và sẽ luôn luôn sử dụng được. Nếu bảng tính của bạn đề cập đến một hàm tùy chỉnh đ ược chứa trong một Add-In, bảng tính sẽ chỉ có thể tính hàm khi các Add-In hiện hữu. Nếu bạn mail bảng tính đ ến người khác bạn cũng phải gửi luôn Add-In cho họ! Nếu bạn muốn sử dụng các hàm tùy chỉnh chứa trong một Add-In trong code của một bảng tính khác, bạn sẽ phải thiết lập một tham chiếu đến Add-In. Đọc phần Làm thế nào để sử dụng các hàm Add-In của bạn trong Excel. C / Sử dụng các chức năng Add-In Excel của bạn trong VBA Nếu bạn đã xây dựng một Add-In để chứa các chức năng tùy chỉnh của bạn trong Excel, có th ể bạn đã phát hiện ra rằng, mặc dù hàm hoạt động tốt trong một bảng tính Excel, b ạn không th ể s ử dụng chúng trong các quy trình VBA của mình trong các bảng tính khác. Như thể Visual Basic Editor không thể nhìn thấy chúng. Vâng, đó là vì nó không thể nhìn thấy được! Bài viết này giải thích cho bạn cách chỉnh sửa nó. Trước tiên, hãy cân nhắc xem liệu điều này có thực sự là những gì bạn muốn làm hay không. Các Add-In chủ yếu được dùng cho việc thêm chức năng bổ xung cho bảng tính c ủa bạn. Khi b ạn nhập một Excel Add-In thì chức năng hàm của mình ngay lập tức trở nên sẵn sàng đ ể s ử d ụng cho tất cả các bảng tính của bạn. Nếu bạn tạo một quy trình VBA mà phụ thuộc vào một hàm tùy chỉnh có trong bảng tính khác, mà bảng tính khác đó phải được mở bất cứ khi nào bạn muốn sử dụng hàm của nó. Tương tự với một hàm tùy chỉnh trong một Add-In. Nếu Add-In đ ược nhập chính xác, nhưng giả sử bạn mail bảng tính của bạn cho người khác, hoặc phân bổ nó cho nhóm làm việc của bạn. Bạn phải nhớ phân bổ cả các Add-In. Nó có thể đơn giản hơn để gộp cả một bản sao c ủa hàm trong mã bảng tính nên bạn có thể truy cập trực tiếp vào nó (bạn có thể cần làm cho nó một chức năng riêng hoặc thay đổi tên của nó để tránh các trùng tên). Tôi không nói là đừng làm điều đó. Bạn chỉ cần suy nghĩ về nó trước tiên, và nếu bạn chắc chắn các Add-In sẽ chạy được, cứ thực hiện. Đây là cách... Khi Nào Có Trục Trặc? Tôi đang viết một quy trình cho một Add-In trong bảng tính của tôi. Trong quy trình này tôi muốn sử dụng chức năng RemoveSpaces mà tôi tạo ra một lúc trước và được lưu trong my Martin's Functions Add-In mà hiện tại được cài đặt trong my copy of Excel. Nhưng khi tôi thử chạy quy trình của tôi, tôi gặp lỗi.
Các Visual Basic Editor chạy như thể hàm không tồn tại, nhưng tôi biết nó tồn tại và tôi có th ể nhìn thấy nó nếu tôi nhìn vào mã Add-In bên trong. Trong thực tế, mã hoạt động tốt nếu tôi chạy nó từ bên trong Add-In của mình. Tôi cần Visual Basic Editor để có thể xem các chức năng trong Add-In của mình từ bên trong mã mô-đun của một bảng tính khác. Đặt tên VBA Project cho Add-In của bạn Mỗi workbook đều có một tên VBA Project. Nó được gọi là VBAProject. Bạn có thể thay đ ổi tên đó nếu bạn muốn nhưng thường tôi không bận tâm, bởi vì nó không quan trọng lắm. Bạn đã bao giờ tự hỏi tại sao tất cả các bảng tính được hiển thị trong cửa sổ Project Explorer của trình biên tập visual basic thì được gọi là "VBAProject"? Nếu bạn có cài đặt bất kỳ một Add-In nào của Microsoft bạn sẽ thấy rằng chúng có tên khác nhau. Các chuyên viên thiết kế của Microsoft đã cung cấp Analysis ToolPak Add-In the VBA Project Name "funcres". Điều đầu tiên phải làm là đặt cho Add-In của bạn một tên VBA Project đ ộc nhất. Điều này là bởi vì bạn sẽ tham chiếu đến nó bởi tên này trong bước tiếp theo và nếu có nhiều h ơn một tên b ị trùng thì Visual Basic Editor sẽ không biết sử dụng cái nào. Trong cửa sổ Project Explorer chọn tên Add-In của bạn. Nếu nó chưa được mở, hiển thị cửa sổ đặc tính của Visual Basic Editor. Bạn sẽ thấy rằng chỉ có một đặc tính, đó là tên. Gõ một tên khác