Vba worksheet functions in Excel

In the previous chapters, we saw a number of built-in functions of VBA which performs some specific functions.

Some of them are CStr(), Cint(), InStr(),Abs(), Exp() and so on, that performs some action.

But there are some functions which are much needed in VBA program, but is not available as built-in function.

Some of such functions are SumIf(),SumIfs(),CountIf(),CountIfs(),VLookUp(),HLookUp() and so on.

These functions are often need to be used but is not available with VBA.

Worksheetfunctions are used in this scenario.As the name implies, this function gives access to all the functions used

in Excel sheet which are not readily available in VBA.

For example, However complex a VBA application maybe, there might arise a situation where one want to search

for a value using VLookUp().In such case, Worksheetfunctions are helpful.

The following example illustrates the usefulness of Worksheetfunctions

  1. Sub worksheetfunctions()
  2. Set myRange = Worksheets("Sheet1").Range("A1:C10")
  3. answer = Application.WorksheetFunction.Min(myRange)
  4. MsgBox answer
  5. End Sub

There are various other Worksheetfunctions which are as detailed below:

  1. WorksheetFunction.Acos()
  2. WorksheetFunction.Acosh()
  3. WorksheetFunction.Acoth()
  4. WorksheetFunction.Acot()
  5. WorksheetFunction.BesselI()
  6. WorksheetFunction.BesselJ()
  7. WorksheetFunction.BesselK()
  8. WorksheetFunction.BesselY()
  9. WorksheetFunction.Guass()
  10. WorksheetFunction.Gcd()
  11. WorksheetFunction.Gamma()

excel vba worksheet functions

Note: The Worksheet function requires a live and meaningful worksheet even to demonstrate.

Hence, test run is not attached with this chapter.

 

You can find similar Excel Questions and Answer hereunder

1) What are worksheets events in VBA. Here we explain some of the main one like like activate or deactivate

2) How can I export a WorkSheet as a PDF using VBA?

3) How to modify strings in VBA, what are the functions you can manipulate strings with

4) How can I identify all cells with Data Validation in my WorkSheet?

5) How can I insert a watermark in my Worksheet?

6) How can I loop through all WorkSheets and get their names using VBA?

7) How do I add a shape using VBA?

8) What is a module in VBA. How to create a module in VBA

9) How can I copy and rename a WorkSheet using VBA?

10) How can worksheet functions be accessed in VBA?

 

Here the previous and next chapter