Excel Dynamic Range Simple Example

;///////////////////////////////////////////////////////////////////////
; Winbatch 2004F - Excel Dynamic Range
;                  Simple Example
;
; You may come across the need to assign a range name that
; adapts to the number of rows of actual data - especially if you
; are looping in array values and constantly clearing cells
;
; Stan Littlefield, September 14, 2004 [Please retain this header]
;///////////////////////////////////////////////////////////////////////

; no error handler included
oXL = CreateObject("Excel.Application")
If oXL == 0 Then Exit
oXL.Visible          = 1  
oXL.ScreenUpdating   = 1   
oXL.UserControl      = 1
oXL.DisplayAlerts    = 0
oXL.WorkBooks.Add()
;or you could open an existing Workbook
;oXL.WorkBooks.Open(cXLS)

oWS = oXL.ActiveWorkbook.Worksheets(1)
oWS.Activate()
sName = oWS.Name    
wName = "MyRange"
oWS.Cells(1,1).Activate()

;choose either, depending on how you might address the range
;oXL.ActiveWorkbook.Names.Add(::Name="%wName%",RefersTo="=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)" )
oXL.ActiveWorkbook.Names.Add(::Name="%wName%",RefersToR1C1="=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),1)" )

; put in some sample data
n = Random(20)
If n<5 Then n=5

For i = 1 To n
   oWS.Cells(i,1).Value = Random(200)
Next

oWS.Cells(1,3).Formula = "=Sum(%wName%)"

oWS=0
oXL=0

Message("Dynamic Range %wName% Created",StrCat("Add some more row values to",@CRLF,"Column A to test Formula",@CRLF,"in Column C"))

Exit
;/////////////////////////////////////////////////////////////////////////////////////

Article ID:   W16610

File Created: 2005:02:18:12:21:38

Last Updated: 2005:02:18:12:21:38

Database Search

TechHome

Excel Dynamic Range Simple Example