BrainDump
  Home arrow BrainDump arrow Building Complex User-defined Functions wi...
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Silverlight  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
ASP Web Hosting  
ASP.NET Web Hosting 
Windows Web Hosting
 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
BRAINDUMP

Building Complex User-defined Functions with Visual Basic Applications in Excel
By: Codex-M
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 3
    2009-08-19

    Table of Contents:
  • Building Complex User-defined Functions with Visual Basic Applications in Excel
  • Building the Function
  • Using Worksheet Cell Ranges inside Visual Basic Statement
  • Supported Functions and Other Important Stuff

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    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.

    More BrainDump Articles
    More By Codex-M


     

    BRAINDUMP ARTICLES

    - Introduction to Office Live Workspace
    - Using MS Excel for One-way Analysis of Varia...
    - Comparing Data Sets Using Statistical Analys...
    - Import Blogger Posts into WordPress Using Wi...
    - Download WordPress from an FTP Server and Ru...
    - Install and Run WordPress in XAMPP Local Host
    - What Windows 7 Brings to the Table
    - Virtualization and Sandbox Detection
    - Advanced Firebug Techniques in Windows XP Ho...
    - Editing CSS with Firebug in Windows XP Home
    - Using Firebug in Windows XP Home
    - Migrating to Exchange Server 2007
    - Using System Restore on a Non-Bootable PC
    - Finding Logged on Users and More Scripting S...
    - Developing Macro Commands in MS Excel





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 4 Hosted by Hostway
    For more Enterprise Application Development news, visit eWeek