DMax DMin and DSum Macro

Session III Part 2

Database function in excel had definied to the following Function :

1. DAVERAGE Returns the average of selected database entries
2. DCOUNT Counts the cells that contain numbers in a database
3. DCOUNTA Counts nonblank cells in a database
4. DGET Extracts from a database a single record that matches the specified criteria
5. DMAX Returns the maximum value from selected database entries
6. DMIN Returns the minimum value from selected database entries
7. DPRODUCT Multiplies the values in a particular field of records that match the criteria in a database
8. DSTDEV Estimates the standard deviation based on a sample of selected database entries
9. DSTDEVP Calculates the standard deviation based on the entire population of selected database entries
10. DSUM Adds the numbers in the field column of records in the database that match the criteria
11. DVAR Estimates variance based on a sample from selected database entries
12. DVARP Calculates variance based on the entire population of selected database entries
    

In This Sections, we will try to create macro for DSUM, DMIN dan DMAX function. Now, open your macro in your microsoft excel work, create module and insert code below :

Function JMin(data, Field, citeria) As Double
JMin = Excel.WorksheetFunction.DMin(data, Field, citeria)
End Function

Function JMax(data As Range, Field As String, citeria As Range) As Double
JMax = Excel.WorksheetFunction.DMax(data, Field, citeria)
End Function

Function JSum(data As Range, Field As String, citeria As Range) As Double
JSum = Excel.WorksheetFunction.DSum(data, Field, citeria)
End Function




In your worksheets, the maro will work like this :



JMin, this function looks at the records of the minimum profit of apple trees over 12 in height.
JMax, this function looks at the records of the maximum profit of apple trees over 12 in height.
JSum, this function looks total profit from apple trees at all.

List of Worksheet Functions

Session III Part 1

Before go to the next code experiment, you should be know the list all of the worksheet functions. The following function definied in worksheet functions (by category) :

• Add-in and Automation functions
• Cube functions
• Database functions
• Date and time functions
• Engineering functions
• Financial functions
• Information functions
• Logical functions
• Lookup and reference functions
• Math and trigonometry functions
• Statistical functions
• Text functions

In This Sections, we will try to create Math and trigonometry functions.

Three common Statistical function Formula

Macro for Statistical function Formula
Session II Part 2.

In Statistical function, we will try to use excel function in macro. The common Statistical function in excel had definied to following function :

Now, can will create 3 function formula, MIN, MAX, and MEDIAN function. Open your module and copy code below :
Function nMin(data As Range) As Currency
nMin = Excel.WorksheetFunction.Min(data)
End Function
Function nMax(data As Range) As Currency
nMax = Excel.WorksheetFunction.Max(data)
End Function
Function nMedian(data As Range) As Currency
nMedian = Excel.WorksheetFunction.Median(data)
End Function



In worksheets, the macro will shown like this :









It's so easy, you can try another function in your code. Macro in excel is very usefull in a big data processing, i show you on next article. Sorry for my english.

Macro for Statistical function Formula

Macro for Statistical function Formula
Session II Part 1.

In Statistical function, we will try to convert code to macro. Statistical function in excel had definied to following function :
AVEDEV
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
BETADIST
BETAINV
BINOMDIST
CHIDIST
CHIINV
CHITEST
CONFIDENCE
CORREL
COUNT
COUNTA
COUNTBLANK
COUNTIF
COUNTIFS
COVAR
CRITBINOM
DEVSQ
EXPONDIST
FDIST
FINV
FISHER
FISHERINV
FORECAST
FREQUENCY
FTEST
GAMMADIST
GAMMAINV
GAMMALN
GEOMEAN
GROWTH
HARMEAN
HYPGEOMDIST
INTERCEPT
KURT
LARGE
LINEST
LOGINV
LOGNORMDIST
MAX
MAXA
MEDIAN
MIN
MINA
MODE
NEGBINOMDIST
NORMDIST
NORMINV
NORMSDIST
NORMSINV
PEARSON
PERCENTILE
PERCENTRANK
PERMUT
POISSON
PROB
QUARTILE
RANK
RSQ
SKEW
SLOPE
SMALL
STANDARDIZE
STDEV
STDEVA
STDEVP
STDEVPA
STEYX
TDIST
TINV
TREND
TRIMMEAN
TTEST
VAR
VARA
VARP
VARPA
WEIBULL
ZTEST

Arithmetic and Summary function

Macro for Arithmetic function Formula
Session I Part 2.

To create Arithmetic function for decimal, you had to change type of parameters and the Function statements to. Change the code that shown at earlier article like this :

Function kali(addr As Currency, addr2 As Currency) As Currency
kali = addr * addr2
End Function
Function bagi(addr As Currency, addr2 As Currency) As Currency
bagi = addr / addr2
End Function
Function tbh(addr As Currency, addr2 As Currency) As Currency
tbh = addr + addr2
End Function
Function krg(addr As Currency, addr2 As Currency) As Currency
krg = addr - addr2
End Function




In summary function, we create function that definied in excel. worksheetsfunction, Now Add the following code to your macro :

Function jumlah(data As Range) As Currency
jumlah = Excel.WorksheetFunction.Sum(data)
End Function



In the your worksheet, semple macro will do like this :



It's so simple, I hope you interest with this, I like excel macro, you should to. Sorry for my english

New Release Motorcycle Honda

New Release Motorcycle Honda 2008

read more | digg story

Macro for Arithmetic function Formula

Macro for Arithmetic function Formula
Session I Part 1.

In This Session, I will show you how to create a unit of code for Arithmetic function. A Function is a unit of code enclosed either between the Function and End Function statements.

Now, you can create a macro with one module. Fill it with the following Function
1. Create four following Function
Function kali(addr As Integer, addr2 As Integer) As String
kali = addr * addr2
End Function
Function bagi(addr As Integer, addr2 As Integer) As String
bagi = addr / addr2
End Function
Function tbh(addr As Integer, addr2 As Integer) As String
tbh = addr + addr2
End Function
Function krg(addr As Integer, addr2 As Integer) As String
krg = addr - addr2
End Function



2. Function number 1 and 2 is Multiplication and Division function, you can see result for Multiplication and Division function on range "E" and sampel formula on range "F"




3. Function number 3 and 4 is Addition and Subtraction function, you can see result for Multiplication and Division function on range "E" and sampel formula on range "F"



OK guys, This is very simple macro. Next time I will show you how to create more complex function.

Macro for formula In Excel

Macro for formula In Excel, Section I.

Hello all, this is my first posting, from this blog I will show you how create macro for formula in Microsoft Excel definitely according to my experience. I hope this is useful for you.

1. For You that used Microsoft excel 2007, to create macro, please follow the following steps:




2. Give macro name then clik on create button.




3. Will shown the page like this, afterwards kept results of your
work.




4. Great Work!.. On Next post, we will try to make simple formula with macro. Sorry for my english.