Tina Norris Fields
tinanfields at torchlake.com
Sat Jan 21 10:02:03 CST 2006
John, Also be sure to name the individual sheets - so much better than getting blown away by the change of position of a sheet. Named ranges have been around since Lotus 123 - way back. They really make things easier. A long time ago, I needed to create a tax computation for payroll for my church, using whatever software was already on the church computer - used a spreadsheet with named ranges for all the tax rates and all the minimums, so I could write an IF formula, seven layers deep, to compute the deductions for social security, fica, medicare. Each year, all I had to change for the new computations were the contents of the named ranges. It took a while to create - and of course, these days, the church has switched to QuickBooks for accounting - but, at the time. . . . . Tina Gustav Brock wrote: >Hi John > >That's an Excel book you are asking for ... >Didn't you once work with a fellow fluid in Excel? Or was that in your Mexico days? > >I'm certainly no expert in Excel but I've found that if you define everything you touch as ranges and define and use Named Ranges to a degree where you may find it ridiculous (for a single row or column or even a cell) you are pretty well off, as your code becomes much easier to read and maintain. Also, if you are working on an existing file - you can easily adjust the layout of worksheet without changing any code as the Named Ranges will persist. > >Further, don't take anything for granted; a worksheet used to be no. 2, one day it is no. 1, so always move it to position 2 if that is important, and so on. > >And never ever use Selection unless, of course, you wish to highlight an area for the user in interactive mode. > >For your entertainment, I have a single function which has proven extremely useful for me as Excel lookup in a range behaves in a way you - with a database background - would not expect. Not like DLookup. It has in-line documentation as it otherwise for non Excel gurus like me is nearly impossible to understand what it does and why: > >Function RLookup( _ > ByVal rngLookup As Range, _ > ByVal strField As String, _ > Optional intSearchOrder As Integer = xlByRows, _ > Optional intOffset As Integer = 1) _ > As Variant > >' Looks up a value in the named range rngLookup from the cell, >' offset intOffset rows below or columns right from the found cell. >' >' 2000-07-30. >' Cactus Data ApS, Gustav Brock > >' Default intSearchOrder is xlByRows, as named ranges built this way are >' easily attached by Access. > > Dim rng As Range > Dim intRow As Integer > Dim intColumn As Integer > > ' No special error handling. > On Error Resume Next > > If intSearchOrder = xlByRows Then > ' Search by rows. > ' Read from found row, offset intOffset rows, and found column. > Else > ' Search by columns. > ' Read from found row and found column, offset intOffset columns. > intSearchOrder = xlByColumns > End If > > With rngLookup > ' Start search from upper left cell in range by starting in lower right cell. > ' Search case sensitive for whole words in values only. > Set rng = .Find(strField, .Cells(.Rows.Count, .Columns.Count), xlValues, xlWhole, intSearchOrder, , True) > End With > If Not rng Is Nothing Then > ' Searched value found. > ' Lookup value to retrieve. > With rng > intRow = .Row - (intOffset * (intSearchOrder = xlByRows)) > intColumn = .Column - (intOffset * (intSearchOrder = xlByColumns)) > ' Return value, offset intOffset rows or columns from cell with found value. > RLookup = .Worksheet.Cells(intRow, intColumn).Value > End With > Set rng = Nothing > End If > >End Function > >/gustav > > > >>>>jwcolby at ColbyConsulting.com 20-01-2006 18:25:46 >>> >>>> >>>> >LOL, all service packs applied so I guess I build a brand new workbook. > >Does anyone have a library of functions for doing things out in a >spreadsheet? I need to do things like name ranges, lock ranges, format >ranges etc. > >John W. Colby >www.ColbyConsulting.com > > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock >Sent: Friday, January 20, 2006 10:31 AM >To: accessd at databaseadvisors.com >Subject: Re: [AccessD] Syntax for seeing named range in excel > >Hi John > >Yes, it is possible to update a linked xls sheet/range - except if you have >applied the latest servicepack. > >A Named Range can be created like this: > > ActiveWorkbook.Names.Add "John", "=$C1:$D4" > >or more specific: > > ActiveWorkbook.Names.Add "John", "=NameOfWorksheet!$C1:$D4" > >If that Named Range exists, it will be adjusted to the new range settings. > >/gustav > > > >>>>jwcolby at ColbyConsulting.com 19-01-2006 19:53 >>> >>>> >>>> >OK oh hero, now is it possible to update the named range through a query? I >tried saving the working select query, then opening that and modifying the >data but that definitely does not work. Is this part of the "patent >lawsuit" changes or am I just doing something wrong? > >If a "Update" syntax would work that would be fine. I am running code >anyway, not manually updating the data as I was trying to do in the test >above. > >As I mentioned, I have a template with the named ranges all set up. I would >like to be able to update that spreadsheet (append data into it) rather than >have to build up a new spreadsheet. I can create the spreadsheet but then I >would need the syntax to create named ranges so I can get the data later - >using the syntax I just got from you. > > >John W. Colby >www.ColbyConsulting.com > > >