Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Base and reading from Access
I have a VBA macro that is getting an array of data from an Access
database. I use this code to retrieve the data: Set rs = db.OpenRecordset("Select Age,Layer from Factors where LOB ='" & Rnames(j) & "';") [error testing] DBRecord = rs.GetRows(30) The module it resides in has Option Base 1 at the top. However the array DBRecord that it returns has an Option Base of 0. This becomes very confusing as the array is then passed as an argument to a function that is entirely set up as Option Base 1, yet now inside it this one array has a different Option Base. Is there some elegant solution other than rewriting the function and converting it to Option Base 0? Don <donwiss at panix.com. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Base and reading from Access
Don,
As a general programming practice, you should never explicitly hard code the bottom (or top) of an array when reading or writing its elements. Use LBound and UBound instead. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Don Wiss" wrote in message ... I have a VBA macro that is getting an array of data from an Access database. I use this code to retrieve the data: Set rs = db.OpenRecordset("Select Age,Layer from Factors where LOB ='" & Rnames(j) & "';") [error testing] DBRecord = rs.GetRows(30) The module it resides in has Option Base 1 at the top. However the array DBRecord that it returns has an Option Base of 0. This becomes very confusing as the array is then passed as an argument to a function that is entirely set up as Option Base 1, yet now inside it this one array has a different Option Base. Is there some elegant solution other than rewriting the function and converting it to Option Base 0? Don <donwiss at panix.com. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Base and reading from Access
On Wed, 21 Jan 2004 19:07:32 -0600, Chip Pearson wrote:
As a general programming practice, you should never explicitly hard code the bottom (or top) of an array when reading or writing its elements. Use LBound and UBound instead. Oh I am, but this is intricate indexing into an array for interpolation on two dimensions. I'm gathering the solution is to rewrite the function into Option Base 0. Don <donwiss at panix.com. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option Base and reading from Access
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might want to consider the ConvertBase function. E.g., if myArray is a 2-D 0-based array (in both dimensions), ConvertBase myArray, 1, 1 will increase both the lower and upper bound of each dimension by 1. Alan Beban Don Wiss wrote: I have a VBA macro that is getting an array of data from an Access database. I use this code to retrieve the data: Set rs = db.OpenRecordset("Select Age,Layer from Factors where LOB ='" & Rnames(j) & "';") [error testing] DBRecord = rs.GetRows(30) The module it resides in has Option Base 1 at the top. However the array DBRecord that it returns has an Option Base of 0. This becomes very confusing as the array is then passed as an argument to a function that is entirely set up as Option Base 1, yet now inside it this one array has a different Option Base. Is there some elegant solution other than rewriting the function and converting it to Option Base 0? Don <donwiss at panix.com. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel user enter a store # to query Access data base | Excel Discussion (Misc queries) | |||
prevent reading or any access to selected cells | New Users to Excel | |||
Chart connected to an Access data base | Charts and Charting in Excel | |||
Access data base is able to store any number of records | Excel Worksheet Functions | |||
MS Excel data reading in MS Access | Excel Discussion (Misc queries) |