Building Complex User-defined Functions with Visual Basic Applications in Excel
(Page 1 of 4 )
This is the last part of a three-part series on creating user-defined functions using Visual Basic in Microsoft Excel. In part two, we started making complex statements by including conditionals (IF statements) in our applications, and began using VB objects like “hyperlinks” to extract the URLs of hyperlinked cell addresses in Excel worksheets. In this article, we will extend those ideas.
Just by taking advantage of the above concepts, one can make really useful user- defined functions in Excel suitable for academic, engineering, office and research applications. In this third part, we will show how we can make user-defined functions for developing more complex applications, such as those found in the engineering and technological sectors.
If you have not read the previous tutorials (parts one and two), it is highly recommended that you read those first, as this article assumes you know the basics already. If you are ready, let's get started.
Using Built-in Functions in Visual Basic User-defined Source Code
If you are programming your own user-defined functions, you may find it complex to create new lines of code to suit every application. If a Microsoft built-in function can do that job, then it would be probably easier on your part to create complex functions consisting of both customized functions that you have written and built-in functions from Excel.
In this document from Microsoft it is shown that it is possible to use Excel worksheet functions in Visual Basic, although this Microsoft documentation does not provide in-depth examples.
To incorporate built-in functions in your user-defined VB program, use this syntax:
Application.WorksheetFunction.BuiltinFunction(variables)
It tells VB that we are going to use built-in functions in the calculations. In order to understand this, consider a practical application. Say, for example, that we are going to calculate the %CV. This is the "coefficient of variation" in statistics which is a measurement of spread or variation. Mathematically, this is equivalent to:
%CV = Sigma / Mean x 100%
When Sigma is small as compared to the mean, the %CV is small also, and the process is said to be stable. "Sigma" is the standard deviation of the samples, while "mean" is the average of the samples. %CV is commonly used in the manufacturing/product engineering sector as a measurement of product parameter/process stability.
Next: Building the Function >>
More BrainDump Articles
More By Codex-M
