Professional Excel Development Chapter 5

[lumpus]

I am fairly new to excel vba so forgive me if these are stupid questions.
Excel VBA 2003


1st
what exactly does this mean (more specifically the part starting with
rngName.Value, "=" & rngSetting.Value)

wksSheet.Names.Add rngName.Value, _
"=" & rngSetting.Value
(see code below)

2nd
can someone explain to me step by step what the Private Function

sSheetTabName(ByRef wkbProject As Workbook, _
ByRef sCodeName As String) As String
does and how it works.

(see bottom of the page)

Option Explicit
Option Private Module


Private Const msFILE_TIME_ENTRY As String = "PetrasTemplate.xls"
Private Const msRNG_NAME_LIST As String = "tblRangeNames"
Private Const msRNG_SHEET_LIST As String = "tblSheetNames"

' 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




Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

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

End If

Next rngName

Next rngSheet

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Private Function sSheetTabName(ByRef wkbProject As Workbook, _
ByRef sCodeName As String) As String

Dim wksSheet As Worksheet
For Each wksSheet In wkbProject.Worksheets
If wksSheet.CodeName = sCodeName Then 'sCodeName is paremeter in
argument
sSheetTabName = wksSheet.Name
Exit For
End If
Next wksSheet
End Function

 

[Mike Glen]

Hi lumpus,

Try posting on an Excel vba newsgroup as this one was devoted to Project and
is closing down.

Mike Glen
Project MVP