WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
OLE with Excel
plus
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Winbatch COM: Test Use with Excel WorksheetFunction Object 

Keywords:   WorkSheetFunction Object
The WorksheetFunction Object is called via the Application Object and is used to provide results from most VB functions available to Excel. Examples I have seen of this Object are with compound command lines, 
	ApplicationObject.WorkSheetFunction.Function( parameters )
and since Winbatch does not currently handle compound commands I am testing a standard method of 'breaking dowm the dots'

The Example creates a small range in Excel then extracts the minimum value in that range to a variable. Not the most exciting example; however the implications are enormous as supported functions are: 

; Acos()
; Acosh()
; And()
; Asin()
; Asinh()
; Atan2()
; Atanh()
; AveDev()
; Average()
; BetaDist()
; BetaInv()
; BinomDist()
; Ceiling()
; ChiDist()
; ChiInv()
; ChiTest()
; Choose()
; Clean()
; Combin()
; Confidence()
; Correl()
; Cosh()
; Count()
; CountA()
; CountBlank()
; CountIf()
; Covar()
; CritBinom()
; DAverage()
; Days360()
; Db()
; DCount()
; DCountA()
; Ddb()
; Degrees()
; DevSq()
; DGet()
; DMax()
; DMin()
; Dollar()
; DProduct()
; DStDev()
; DStDevP()
; DSum()
; DVar()
; DVarP()
; Even()
; ExponDist()
; Fact()
; FDist()
; Find()
; FindB()
; FInv()
; Fisher()
; FisherInv()
; Fixed()
; Floor()
; Forecast()
; Frequency()
; FTest()
; Fv()
; GammaDist()
; GammaInv()
; GammaLn()
; GeoMean()
; Growth()
; HarMean()
; HLookup()
; HypGeomDist()
; Index()
; Intercept()
; Ipmt()
; Irr()
; IsErr()
; IsError()
; IsLogical()
; IsNA()
; IsNonText()
; IsNumber()
; Ispmt()
; IsText()
; Kurt()
; Large()
; LinEst()
; Ln()
; Log()
; Log10()
; LogEst()
; LogInv()
; LogNormDist()
; Lookup()
; Match()
; Max()
; MDeterm()
; Median()
; Min()
; MInverse()
; MIrr()
; MMult()
; Mode()
; NegBinomDist()
; NormDist()
; NormInv()
; NormSDist()
; NormSInv()
; NPer()
; Npv()
; Odd()
; Or()
; Pearson()
; Percentile()
; PercentRank()
; Permut()
; Phonetic()
; Pi()
; Pmt()
; Poisson()
; Power()
; Ppmt()
; Prob()
; Product()
; Proper()
; Pv()
; Quartile()
; Radians()
; Rank()
; Rate()
; Replace()
; ReplaceB()
; Rept()
; Roman()
; Round()
; RoundDown()
; RoundUp()
; RSq()
; Search()
; SearchB()
; Sinh()
; Skew()
; Sln()
; Slope()
; Small()
; Standardize()
; StDev()
; StDevP()
; StEyx()
; Substitute()
; Subtotal()
; Sum()
; SumIf()
; SumProduct()
; SumSq()
; SumX2MY2()
; SumX2PY2()
; SumXMY2()
; Syd()
; Tanh()
; TDist()
; Text()
; TInv()
; Transpose()
; Trend()
; Trim()
; TrimMean()
; TTest()
; USDollar()
; Var()
; VarP()
; Vdb()
; VLookup()
; Weekday()
; Weibull()
; ZTest()
;
; With this functionality you can position a WB multi-dimensional array
; or a LAFF db into a 'hidden' worksheet and perform multiple
; calculations or data manipulations, i.e. determine the min and max
; values of the 3rd element in a 5 element array, or set up a WB
; dialog to calculate a home mortgage payment. (just an idea)
;
; Stan Littlefield - November 17, 2002


#DefineFunction PutData( oWS, r,c,val )
oCell            = oWS.Cells(r,c)
oCell.Value      = val
ObjectClose( oCell )
Return( "" )
#EndFunction

; assumes Excel is installed, no error checking
BoxOpen("WorkSheetFunction Demo...","Please Wait")
DB               = ObjectOpen("Excel.Application")
DB.Visible       = @FALSE         ;we don't need to see this
DB.ScreenUpdating= @FALSE         ;and we want it to run fast
DB.UserControl   = @TRUE
DB.DisplayAlerts = @FALSE
oAPP             = DB.Workbooks
oXLS             = oAPP.add
oWS              = oXLS.Worksheets("Sheet1")
oWS.Activate()

PutData(oWS,1,1,"400")
PutData(oWS,2,1,"213")
PutData(oWS,3,1,"104")
PutData(oWS,4,1,"119")
PutData(oWS,5,1,"261")
oRange           = oWS.Range( "A1:A5" ) ; select data range

; break down the object and apply a function
oWF              = DB.WorkSheetFunction
answer           = oWF.Min( oRange )
message("Minimum Value - Range A1:A5",answer)

ObjectClose( oWF )
ObjectClose( oRange )
ObjectClose( oWS )
ObjectClose( oXLS )
ObjectClose( oAPP )
DB.Quit               ; worksheet used as scratchpad
ObjectClose(DB)       ; no need to save it

BoxShut()
Exit
; end of script

Article ID:   W15645

File Created: 2003:05:13:11:29:22
Last Updated: 2003:05:13:11:29:22