| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chapter 5 professional Excel Development
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chapter 5 professional Excel Development
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chapter 5 professional Excel Development
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chapter 5 professional Excel Development
Thanks for the help. BTW it is not homework just teaching myself vba it useful for my job. "Bob Phillips" wrote: 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chapter 5 professional Excel Development
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) "lumpus" wrote in message ... Thanks for the help. BTW it is not homework just teaching myself vba it useful for my job. "Bob Phillips" wrote: 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| can you group worksheets together and create a chapter? | Excel Worksheet Functions | |||
| Professional Excel Development | Excel Discussion (Misc queries) | |||
| Professional Excel Development book question | Excel Programming | |||
| Professional Data Recovery Software Development Help | Excel Discussion (Misc queries) | |||
| Chapter numbers | Excel Discussion (Misc queries) | |||
Linear Mode
