в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:38Z

1 версия

Новая страница

==A function with a range argument==



<source lang="vb">

=TopAvg(Data,5)
Function TopAvg(InRange, N)
Dim Sum As Double
Dim I As Long
Sum = 0
For i = 1 To N
Sum = Sum + Application.WorksheetFunction.LARGE(InRange, i)
Next i
TopAvg = Sum / N
End Function
</source>

==Calculating a mortgage payment==



<source lang="vb">

Sub PmtCalc()
Dim IntRate As Double
Dim LoanAmt As Double
Dim Periods As Integer
IntRate = 0.0825 / 12
Periods = 30 * 12
LoanAmt = 150000
MsgBox WorksheetFunction.pmt(IntRate, Periods, LoanAmt)
End Sub
</source>

==Finding the maximum value in a range==



<source lang="vb">

Sub ShowMax()
Dim TheMax As Double
TheMax = WorksheetFunction.Max(range("A1:C3"))
MsgBox TheMax
End Sub
</source>

==Insert the values directly as the function arguments:==



<source lang="vb">

Sub pmt()
MsgBox WorksheetFunction.pmt(0.0825 / 12, 360, -150000)
End Sub
</source>

==Test the effect of the Option Base statement: declare that arrays are 1-based==



<source lang="vb">

Option Base 1
Sub Array1()
Dim aiData(10) As Integer
Dim i As Integer
For i = LBound(aiData) To UBound(aiData)
aiData(i) = i
Next i
Debug.Print "Lower Bound = " & LBound(aiData)
Debug.Print "Upper Bound = " & UBound(aiData)
Debug.Print "Num Elements = " & WorksheetFunction.Count(aiData)
Debug.Print "Sum Elements = " & WorksheetFunction.Sum(aiData)
End Sub
</source>

==Use case select statement to call common functions==



<source lang="vb">

Function STATFUNCTION(rng, op)
Select Case UCase(op)
Case "SUM"
STATFUNCTION = WorksheetFunction.Sum(rng)
Case "AVERAGE"
STATFUNCTION = WorksheetFunction.Average(rng)
Case "MEDIAN"
STATFUNCTION = WorksheetFunction.Median(rng)
Case "MODE"
STATFUNCTION = WorksheetFunction.Mode(rng)
Case "COUNT"
STATFUNCTION = WorksheetFunction.Count(rng)
Case "MAX"
STATFUNCTION = WorksheetFunction.Max(rng)
Case "MIN"
STATFUNCTION = WorksheetFunction.Min(rng)
Case "VAR"
STATFUNCTION = WorksheetFunction.Var(rng)
Case "STDEV"
STATFUNCTION = WorksheetFunction.StDev(rng)
Case Else
STATFUNCTION = CVErr(xlErrNA)
End Select
End Function
</source>

==Use fully qualified properties:==



<source lang="vb">

Sub sumDemo()
MsgBox WorksheetFunction.Sum(Sheets("Sheet1").Range(_
Sheets("Sheet1").Range("A1"), _
Sheets("Sheet1").Range("A10")))
End Sub
</source>

==Use the LARGE function to determine the kth-largest value in a range==



<source lang="vb">

Sub func()
SecondHighest = WorksheetFunction.Large(range("NumberList"), 2)
End Sub
</source>

==use the Range property as an argument within another Range property==



<source lang="vb">

Sub range()
WorksheetFunction.Sum (Worksheets("Sheet2").range(Worksheets("Sheet2"). _
range("A1"), Worksheets("Sheet2").range("A7")))
With Worksheets("Sheet2")
WorksheetFunction.Sum (.range(.range("A1"), .range("A7")))
End With
End Sub
</source>

==Use the Transpose function to turn the one column into one row==



<source lang="vb">

Sub TransposeArray()
Dim myArray As Variant
myArray = WorksheetFunction.Transpose(range("myTran"))
MsgBox "The 5th element of the Array is: " & myArray(5)
End Sub
</source>

==Using a lookup function==



<source lang="vb">

Sub GetPrice()
Dim PartNum As Variant
Dim Price As Double
PartNum = InputBox("Enter the Part Number")
Sheets("Prices").activate
Price = WorksheetFunction.VLookup(PartNum, range("PriceList"), 2, False)
MsgBox PartNum & " costs " & Price
End Sub
</source>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Excel/WorksheetFunction - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия