Thank you. I have used the method of looping through the names in the
workbook before, but I wanted to avoid it because I have several
hundred named cells and the main objective of my code is to determine
the name of the cell so I can write a value to that cell from a
separate sheet. For example:
Private Sub ReadFromDatabase()
'Get the array of variable names
'Essentially a list of all the named
'cells
aVarNames = Range("rangeDBVarNames")
'Determine the index number of the database record
'Simplified for example
iIndex = 10
Set rnArea = Range("rangeInput")
'Loop through each cell in the input range
For Each rnCell In rnArea
'Get the variable name
'THIS IS THE SLOW STEP IN THE PROCESS
varName = rnCell.Name.Name
'Determine the index of the variable
iVarName = WorksheetFunction.Match(varName, aVarNames, False)
'Write the value from the DB to the input sheet
rnCell.Value = Worksheets("Database").Range("DBStart").Offset
(iVarName, iIndex).Value
Next
End Sub
I ended up speeding this up by changing the approach and looping
through the list of variable names rather than looping through all the
cells in the input range. Which changed the writing to the input
range code as follows:
'Write the value from the DB to the input sheet
Worksheets("Input").Range(varName).Value = _
Worksheets("Database").Range("DBStart").Offset(iVarName,
iIndex).Value
This action still takes approximately 10 seconds to complete, but that
is better than the 30 it was originally taking.
So, if you/anyone knows of a quicker method for getting a cell name
other than what I had proposed above I'd be happy to hear it.
Otherwise, I'll probably stick to looping through the list of known
named cells as it seems to be a better solution for now.
Thanks again Tom.
Scott
On Nov 10, 2:03 pm, "Tom Jeffries" <[EMAIL PROTECTED]> wrote:
> I think this does what you want:
> Public Sub GetNames()
> Dim n As Integer
>
> For n = 1 To ActiveWorkbook.Names.Count
> Sheets("Sheet2").Cells(n, 1).Value = ActiveWorkbook.Names(n).Name
> Next
> End Sub
>
> On Sun, Nov 9, 2008 at 2:04 PM, <[EMAIL PROTECTED]> wrote:
>
> > Hi,
>
> > I have an excel workbook that is working in a similar manner to a
> > database in that I write the values for named ranges to a separate
> > sheet that contains all of the variables in columns. In order to
> > accomplish this I have a macro that searches a sheet for all of the
> > named cells on that sheet and then writes the value to the "database"
> > sheet for future reference.
>
> > The problem I have encountered is that using Range.Name.Name to access
> > the cell name is very slow, so if I am reading values in from the
> > database sheet or comparing values to the database sheet it ends up
> > taking 15 or 20 seconds to read.
>
> > So, does anyone know of a method for determining a cell name that is
> > quicker than what is shown below?
>
> > Public Sub Test()
> > Dim varName As String
>
> > On Error Resume Next
> > Set rnArea = Range("A1:Z100")
> > For Each rnCell In rnArea
> > 'Determine the variable name - will cause an error if there is no
> > name
> > varName = rnCell.Name.Name
> > Next
> > End Sub
>
> > Thanks in advance.
> > Scott
>
>
>
> GetNames.xls
> 31KViewDownload
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
