for each next question

[lumpus]

Another question professional excel development chapter 5

In the part of the program where (see below) For Each rngSheet In
rngSheetList and For Each rngName In rngNameList how does the program know to
cycle through each cell in the column and the rows respectively?




' Description: This module contains utility procedures designed
' to assist the programmer during development.
'
' Authors: Stephen Bullen, www.oaltd.co.uk
' Rob Bovey, www.appspro.com
'
' Chapter Change Overview
' Ch# Comment
' --------------------------------------------------------------
' 05 Initial version
'
Option Explicit
Option Private Module

' **************************************************************
' Module Constant Declarations Follow
' **************************************************************
' We duplicate these constants here because we want this utility
' module to be completely self-contained.
Private Const msFILE_TIME_ENTRY As String = "mytryatlesson5.xls"
Private Const msRNG_NAME_LIST As String = "tblRangeNames"
Private Const msRNG_SHEET_LIST As String = "tblSheetNames"


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments: This procedure transfers the settings specified in
' the wksUISettings table into the appropriate
' worksheets as defined names.
'
' This procedure cannot be run from outside the
' add-in. Run it from this module by placing your
' cursor somewhere inside the body of the procedure
' and pressing F5. The PetrasTemplate.xlt workbook
' must be open before this procedure can be run.
'
' Date Developer Chap Action
' --------------------------------------------------------------
' 03/15/04 Rob Bovey Ch05 Initial version
'
Public Sub WriteSettings()

Dim rngSheet As Range
Dim rngSheetList As Range
Dim rngName As Range
Dim rngNameList As Range
Dim rngSetting As Range
Dim sSheetTab As String
Dim wkbBook As Workbook
Dim wksSheet As Worksheet



' Turning off screen updating and calculation
' will speed the process significantly.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' The time entry workbook.
Set wkbBook = Application.Workbooks(msFILE_TIME_ENTRY)
' The list of worksheets in the first column.
Set rngSheetList = wksUISettings.Range(msRNG_SHEET_LIST)
' The list of setting names in the first row.
Set rngNameList = wksUISettings.Range(msRNG_NAME_LIST)

' The outer loop processes all the worksheets in the
' first column of the table.
For Each rngSheet In rngSheetList


' We need an object reference to the worksheet so we
' can easily add a sheet-level defined name to it.
' The sSheetTabName() function converts a CodeName
' into its corresponding sheet tab name.
sSheetTab = sSheetTabName(wkbBook, rngSheet.Value)
Set wksSheet = wkbBook.Worksheets(sSheetTab)

' The inner loop adds each setting to the current sheet.
' If the setting already exists it will be replaced.
For Each rngName In rngNameList

' The value of the setting is contained in the cell
' where the worksheet row and range name column
' intersect.
Set rngSetting = Intersect(rngSheet.EntireRow, _
rngName.EntireColumn)


' We only create defined names for settings that
' have been given a non-zero-length value.

If Len(rngSetting.Value) > 0 Then
wksSheet.Names.Add rngName.Value, _
"=" & rngSetting.Value
' MsgBox rngName
'MsgBox rngName.RefersTo



End If

Next rngName

Next rngSheet ' refers to the worksheets

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[lumpus]

I should add that in the named range
tblRangeNames
RefersTo

=OFFSET(wksUISettings!$A$1,0,1,1,COUNTA(wksUISettings!$1:$1)-1)

tblSheetNames
RefersTo

=OFFSET(wksUISettings!$A$1,1,0,COUNTA(wksUISettings!$A:$A)-1,1)

 

[JLGWhiz]

Another question professional excel development chapter 5

In the part of the program where (see below) For Each rngSheet In
rngSheetList and For Each rngName In rngNameList how does the program know to
cycle through each cell in the column and the rows respectively?

The For statement set the parameters for the loop. One loop is looking for
sheets and the other is looking for names. The Next command will sent the
interrogater back to the beginning of the loop until that paticular For
criteria has been exhausted.
Therefor, the inner loop will complete a full range of execution before
going to the next step of the outer loop. Whet the outer loop has exhausted
its range or criteria, the code execution move to the next line outside the
loops. See exmpl below

For Each c In MyRange 'Begins outside loop
For Each i In yourRange 'Begins inside loop
If something = something else Then
Do Things
End If
Next i 'recycles until yourRange has been exhausted
Next c 'recycles after each For Each i loop is completed until MyRange is
exhausted
Exit Sub 'Executes after both loops are completed.

 

[JLGWhiz]

A little further explanation:

Your named ranges, rngSheetList and rngNameList are defined ranges, so the
code will automaticall start at the top left cell, across and down in the
applicable range to look for the criteria that you define between the For
Each... and Next.

If your range is a single column, then it searches downward one cell at a
time. If your range is a row then it searches left to right one cell at a
time. This search pattern is built in to the For Each...Next loop function
and cannot be altered.

 

[lumpus]

Thank you that explains it for me

A little further explanation:

Your named ranges, rngSheetList and rngNameList are defined ranges, so the
code will automaticall start at the top left cell, across and down in the
applicable range to look for the criteria that you define between the For
Each... and Next.

If your range is a single column, then it searches downward one cell at a
time. If your range is a row then it searches left to right one cell at a
time. This search pattern is built in to the For Each...Next loop function
and cannot be altered.