Chapter 5 professional Excel Development

[lumpus]

I have two questions from chapter 5 of "profession excel development"

I am fairly new to vba so forgive me if these are dumb questions

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

 

[Don Guillett]

Homework?

 

[Bob Phillips]

1st

It is adding a defined name and calling it by the value in rngName, and
giving it a RefersTo value of the value in rngSetting

2nd

his function returns the Excel sheet name when the sheets codename is known.

The workbook object and the coiodename is passed as parameters.

A loop is setup to iterate through all worksheets in the target workbook
If the current sheet's coden ame is the same as the codename passed as the
parameter, then the Excel sheet name is extracted, and the loope is exited
The found value is returned.

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[lumpus]

Thanks for the help.

BTW it is not homework just teaching myself vba it useful for
my job.

 

[Bob Phillips]

I never said it was. I must say that it seemed unlikely, homework usually
applies at a much more basic level than that particular tome

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)