A base 0 array

[Daniel Duta]

Hello all,
I have a zero 2D array and I have to populate an excel sheet with it. As you know, for this simple task the array should be in 1 base. The question is: is there any way to convert a 2D array from base 0 to 1 (so on both dim's) without having to iterate through all array elements ? Thank you.

[georgekar]

using option base 1

[DataMiser]

As you know, for this simple task the array should be in 1 base

I have no idea why it would need to be in base 1, I personally have never used the Option base 1 statement in any project but that will allow you to have your arrays as base 1
You can also specify the starting and ending points as part of a dim statement if needed.

[techgnome]

Doesn't VB6 by default use Base 1 anyways?

-tg

[DataMiser]

No, by default it is base 0

[jmsrickland]

What difference does it make just start with element 1 even if it's base is 0

[georgekar]

When you pay a ticket for seat 0 and in the bus there is no 0 seat then you have an error. Is the same...for array...

(this was a joke....)

[DataMiser]

When you pay a ticket for seat 0 and in the bus there is no 0 seat then you have an error. Is the same...for array...

There should be no issue at all with a 0 based array. The programmer just needs to know that he is working with a 0 based array and code accordingly.

In a 0 based array there is always a 0 element so not sure what your analogy is supposed to mean.

Also note that it is a very simple matter to add or subtract 1 from whatever value you are using to link to the index if need be

[georgekar]

The real answer...
In a string we have position..so a position has to be a natural number, 1...N
An offset is a value that we add to a base address. So an array has a base address and an offset indicate the index of the array element. So 0 offset exist. Any offset is a part of an equation, and that equation gives a physical address. Address are always start from 0, because is the binary representation of the wires or lines of data bus on the motherboard. So if "zero" volt is in all lines in data bus and a read signal from other line has a "one" volt (for old IC this is 5 volt, but for new is very low, around 2 volt), then we have address zero. This zero is an offset (0 as base number of address and 0 the offset of program counter, 0+0 is 0, this means that this counter is an offset type so you can add offsets to produce jumps or branches ).
So here we need natural numbers as positions and no offsets. The upper left cell in excel is A1. So if we want to fill 100 cells then in an array zero based we have to provide one item more in the definition part. That is the difference.

[Schmidt]

Hello all,
I have a zero 2D array and I have to populate an excel sheet with it. As you know, for this simple task the array should be in 1 base. The question is: is there any way to convert a 2D array from base 0 to 1 (so on both dim's) without having to iterate through all array elements ?

AFAIK (and just tested it) - you can fill an XL-Range directly also from a Zero-based (2D) Array.

E.g. this fast, simple test did work here:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Arr(0 To 2, 0 To 1)
    Arr(0, 0) = "0_0"
    Arr(1, 0) = "1_0"
    Arr(2, 0) = "2_0"
    
    Arr(0, 1) = "0_1"
    Arr(1, 1) = "1_1"
    Arr(2, 1) = "2_1"
    
    Range("A1:B3").Value = Arr
End Sub

Olaf

[passel]

using option base 1

Usually, I wouldn't want to change the default for all my arrays, if I wanted one to have a base 1, just declare the array with a lower and upper bound. (p.s. per DataMiser post #3)
Dim ar(1 to 5) as Integer

The fact is that VB6 and earlier was one of those languages that didn't require the lower bound to be 0 or 1, or even a positive number.

Option Explicit

Private Sub Command1_Click()
  Dim ar(-5 To 5) As Integer
  Dim i As Integer
  For i = -4 To 3
    ar(i) = i * i
  Next
  For i = -5 To 5
    Debug.Print i, ar(i)
  Next
End Sub

Results

-5             0 
-4             16 
-3             9 
-2             4 
-1             1 
 0             0 
 1             1 
 2             4 
 3             9 
 4             0 
 5             0

[Daniel Duta]

Hello,
I am a bit confused. After I tested the Olaf's example it seems that Excel sheet allows to fill a Range from a 2D array (0 based) directly, even in a shifted way : Range("A1").Value=arr(0, 0). But always when we try the reverse process (Arr = Range("A1:B3").Value) we will get a 1 base array even the Option Base is 0 (the default index).
For this reason is preferable in vba to use 1 base arrays for both situations. Now, my issue occurs when I use the getRows method (from ADO control) trying to fill an array with data (a recordset).
The loaded array is a variant with lower bounds by default zero and with rows and columns dimensions in reverse order (first dim is for columns and second for rows). But any attempt to populate the Excel worksheet with data failed even I tried to resize and transpose the array properly. I work with Excel library from VB6 and the vba code that should do the task is below :

.Range("A1").Resize(UBound(mdbArray, 1) + 1, UBound(mdbArray, 2) + 1).Value = .WorksheetFunction.Transpose(mdbArray)

The error that I get each time is "Type mismatch(13)" which is not related to array bounds but to array content.
I suspect that a conflict occurs when my array (which contains Null values !) starts to populate the excel cells that have a different format (text). Curious is the thing that moving (and transposing) the array content in other variant array the error above does disappear. I really do not have any explanation for this. Below is the transfer process:

'Transpose Array from base 0 to 1
      Dim varArray() As Variant, i As Long, j As Long
      For j = 0 To UBound(mdbArray, 1)
        For i = 0 To UBound(mdbArray, 2)
            varArray(i + 1, j + 1) = mdbArray(j, i)
        Next i
      Next j

[georgekar]

Try use a space as null value

[techgnome]

The loaded array is a variant with lower bounds by default zero and with rows and columns dimensions in reverse order (first dim is for columns and second for rows).

Yeah, that does seem bass-ackwards until you realize that when rezising a multi-dimension array, you can only change the last element... so what Excel does on the back end is go, OK, loop through the cols, because that's not going to change, and count them up... and sets that as the first dimension. then it loops through the rows, expanding the array as it goes... which causes a (Col, Row) result rather than the expcted (Row, Col) as most of us are likely used to.

DISCLAIMER - this is an assumption and not rooted in any factual knowledge other than how the Redim work with multi-dimension arrays... but it does make sense to me, as silly as it would seem.

-tg

[georgekar]

Is the way that an array can strore values. Suppose you have 2x2 array with values 1,2 for one row and 4,5 for the other. In memory those numbers are in a line 1,4,2,5 so you can increase the last dimension and add a column 3,6 as 1,4,2,5,3,6.

In M2000 interpreter I do the reverse, so you can expand rows. 1,2,3,4 as 2 rows by 2 items and 1,2,3,4,5,6 as 3 rows by 2 items.

[georgekar]

A spreadsheet is not an array but a linked list and the address a1 is the key to find item. So a spreadsheet never need to redim.

[Daniel Duta]

Hi George,
I do not understand your argument regarding upper bound changing. As I know only the last array dimension can be resized and you cannot transform/expand directly a 2D array (via Preserve) from arr(2,2) in arr(3,2) . Anyway the error above seems to be related to array content and not to array bounds. I feel it is something wrong with those Null values stored in array (that are not vbnullstring values) and they could create a conflict with excel Cells format (text) when the Sheet is filled ...On the other hand, I do not understand once these data are moved from an array to other, without any conversion, why any error disappears.

[techgnome]

A spreadsheet is not an array but a linked list and the address a1 is the key to find item. So a spreadsheet never need to redim.

right... but when you extract that data from the spreadsheet into an array... you're no longer dealing with a spreadsheet but an array... its size has to be dimmed somehow...

-tg

[Schmidt]

I am a bit confused. After I tested the Olaf's example it seems that Excel sheet allows to fill a Range from a 2D array (0 based) directly, even in a shifted way : Range("A1").Value=arr(0, 0). But always when we try the reverse process (Arr = Range("A1:B3").Value) we will get a 1 base array even the Option Base is 0 (the default index).
For this reason is preferable in vba to use 1 base arrays for both situations.

In almost all situations I prefer to work with zerobound-arrays - so if you produce those
arrays at the VB6-end (or within VBA) with zerobounds, then leave them at it...
The write-direction (as demonstrated against XL-Ranges) works just fine with zerobound arrays.

As for the read-direction (from XL-Range into 2D-Array) - this could be corrected (though without transposing)
quite easily, when you change the LowerBounds-Members in the SafeArray2d-struct only.

You can check the following code out within an XL-Sheet (works also in VB6 of course).

Option Explicit

Private Declare Sub MemCopy Lib "kernel32" Alias "RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal CB&)

Private Type SAFEARRAY2D
  cDims As Integer
  fFeatures As Integer
  cbElements As Long
  cLocks As Long
  pvData As Long
  cElements1 As Long
  lLbound1 As Long
  cElements2 As Long
  lLbound2 As Long
End Type

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Test
End Sub

Private Sub Test()
Dim Arr()
  Arr = CreateZeroBased2DDemoArrayValues
  MsgBox "Arr-Dimensions(" & LBound(Arr, 1) & " to " & UBound(Arr, 1) & ", " & LBound(Arr, 2) & " to " & UBound(Arr, 2) & ")"
  
  Write2DArrayToRange Arr, "A1:B3"
  
  Arr = ReadOneBased2DArrayFromRange("A1:B3")
  
  Write2DArrayToRange Arr, "D1:E3"
  
  MsgBox "Arr-Dimensions(" & LBound(Arr, 1) & " to " & UBound(Arr, 1) & ", " & LBound(Arr, 2) & " to " & UBound(Arr, 2) & ")"
  
  ChangeLowerBoundsOn2DArray Arr, 0, 0
  
  MsgBox "Arr-Dimensions(" & LBound(Arr, 1) & " to " & UBound(Arr, 1) & ", " & LBound(Arr, 2) & " to " & UBound(Arr, 2) & ")"
  
  Write2DArrayToRange Arr, "G1:H3"
End Sub

Private Function Write2DArrayToRange(Arr(), strRange As String)
  Range(strRange).Value = Arr
End Function

Private Function ReadOneBased2DArrayFromRange(strRange As String) As Variant()
  ReadOneBased2DArrayFromRange = Range(strRange).Value
End Function

Private Sub ChangeLowerBoundsOn2DArray(V, Optional ByVal LB1 As Long, Optional ByVal LB2 As Long)
Dim pSafeArrayVar As Long, pSafeArray As Long, Dims As Integer, SA2D As SAFEARRAY2D
 
  If Not IsArray(V) Then Exit Sub
 
  MemCopy pSafeArrayVar, ByVal VarPtr(V) + 8, 4
  If pSafeArrayVar = 0 Then Exit Sub
  
  MemCopy pSafeArray, ByVal pSafeArrayVar, 4
  If pSafeArray = 0 Then Exit Sub
     
  MemCopy Dims, ByVal pSafeArray, 2
  If Dims <> 2 Then Err.Raise vbObjectError, , "only 2D-Arrays are supported by this function"
  
  MemCopy SA2D, ByVal pSafeArray, LenB(SA2D) 'copy over into the safearray-struct...
          SA2D.lLbound1 = LB1
          SA2D.lLbound2 = LB2
  MemCopy ByVal pSafeArray, SA2D, LenB(SA2D) '...and write the changed values back
End Sub

Private Function CreateZeroBased2DDemoArrayValues() As Variant()
Dim Arr(0 To 2, 0 To 1)
    Arr(0, 0) = "0_0"
    Arr(1, 0) = "1_0"
    Arr(2, 0) = "2_0"
    
    Arr(0, 1) = "0_1"
    Arr(1, 1) = "1_1"
    Arr(2, 1) = "2_1"
    CreateZeroBased2DDemoArrayValues = Arr
End Function

Now, my issue occurs when I use the getRows method (from ADO control) trying to fill an array with data (a recordset).

In case JET-mdbs are not an absolute necessity, then you could consider SQLite, which
over my wrapper provides a Rs.GetRows-Method which doesn't have this NullValue-Problem
(and also supports the direct hand-out of transposed Arrays over an optional parameter).

Olaf

[georgekar]

Hi George,
I do not understand your argument regarding upper bound changing. As I know only the last array dimension can be resized and you cannot transform/expand directly a 2D array (via Preserve) from arr(2,2) in arr(3,2) . Anyway the error above seems to be related to array content and not to array bounds. I feel it is something wrong with those Null values stored in array (that are not vbnullstring values) and they could create a conflict with excel Cells format (text) when the Sheet is filled ...On the other hand, I do not understand once these data are moved from an array to other, without any conversion, why any error disappears.

The last dimension is what to have to redim, and that is my argument.
As for null fields, you have to use something like this IsNull(rec.Fields(i%)) to change the value to something like a space (so after the writing to the excel the space may change the cell from null to a string but nobody see that...

[Daniel Duta]

Hello all,
I owe you a reply to my thread. I have solved the NullValue issue simply transposing the 2D array obtained with ADO control , via rs.GetRows method. It worked but I have no explanation why a variant array containing NullVallues moved in other variant array could eliminate NullValues without any other condition. The Olaf's function is very useful in cases where we may need to change the lower bound of a 2D array without loop. I have to recognize that I didn't use the SafeArray structure until now and I have noted it is not very well documented online.
For example, in the Private Type SAFEARRAY2D declaration are some elements that are not used in the function (actually only lLbound1 and lLbound2 are used) but it seems they have to be declared even they are not used.
In brief, how a safearray works ?

In case JET-mdbs are not an absolute necessity, then you could consider SQLite, which over my wrapper provides a Rs.GetRows-Method which doesn't have this NullValue-Problem

Could be considered SQLite a better alternative to MS Access? Does it work properly with VB6? How could I install your wrapper with SQLite ? Is SQLite a multi-user database ? Thank you.

[techgnome]

Could be considered SQLite a better alternative to MS Access?

It's as well as any other I suppose.

Does it work properly with VB6?

I've never seen any issues. Should work fine as long as you have the proper drivers and stuff.

How could I install your wrapper with SQLite?

That's something I don't know, but I imagine that it's a drop-in, register (maybe) and you're off to the races.

Is SQLite a multi-user database?

Ah, no... it's a single user local database system. Let me clarify it ISN'T a CONCURRENT multi-user database. I'm not even sure it can be used for "remote" databases.

-tg

[Schmidt]

I have to recognize that I didn't use the SafeArray structure until now and I have noted it is not very well documented online.

Wouldn't say so...
Googling for [SafeArray MSDN] leads to the description of the appropriate struct here:
http://msdn.microsoft.com/en-us/libr...=vs.85%29.aspx
And the Sub-Struct (which in its amount is determined by the cDims-Member of the above struct)
is described here:
http://msdn.microsoft.com/de-de/libr...=vs.85%29.aspx

In short, *all* VB-Arrays are based on this struct (and the surrounding Set of API-functions,
which do their work under the hood, whenever you call a ReDim, or assign Arrays to each other,
or call LBound or UBound)...

For example, in the Private Type SAFEARRAY2D declaration are some elements that are not used in the function (actually only lLbound1 and lLbound2 are used) but it seems they have to be declared even they are not used.

That's usually the case with *Members* of a Struct (of a UserDefined-Type) -
we don't need to touch (set, assign or overwrite) them, when they are already
filled in (or initialized) correctly.

In brief, how a safearray works ?

E.g. when we consider the simple example of a one-dimensional Long-array:

ReDim L(1 to 2) as Long

VB does the following under the hood:
- allocate memory which can take up the UDT-struct below (note, that the LenB-Len is different
from the 2D-SafeArray-Struct, which takes up 32bytes, instead of the 24Bytes of the 1D-Type-Alloc below).

Private Type SAFEARRAY1D
  cDims As Integer
  fFeatures As Integer
  cbElements As Long
  cLocks As Long
  pvData As Long
  cElements1 As Long
  lLbound1 As Long
End Type

- the cDims Member will be set to 1 (to represent a one-dimensional array)
- the cbElements Member will be set to 4 (since a single (Long-)Array-Member will occupy 4Bytes)
- the pvData Member will be set to the pointer of an *additional* mem-allocation (in this case the Long-Array-Data)
- the cElements1 Member will be set to 2, to describe the amount of members in the Long-Array
- the lLbound1 Member will be set to 1, to allow for "non-zerobased offsets/indexing", as required by our Redim-Line above

The 32Bit-pointer to this filled up SafeArray-Struct-allocation is then placed in our Array-Variable L.

So - other than in C - VB performs always *two* separate memory-allocations when an Array starts
its lifecycle - one for the SafeArray-Struct in the correct size (depending on cDims) - and an additional
one for the "real array-data" this struct represents and describes.

So there's an indirection involved, and as with most indirections - they cost a bit of performance,
but allows for more comfort and features - in our case we get self-describing Arrays which can be
of any type.

Could be considered SQLite a better alternative to MS Access?

I can only offer you a big fat 'YES' of course - but I'm obviously biased.

Seriously, the SQLite-project is one of the best maintained OpenSource-Projects,
with the most liberal license there is (Public Domain).

The feature-list is larger than what JET has to offer - the speed is better -
there's no DB-size limitation - there's FullTextSearch - there's InMemory-DBs,
there's strong encryptable DB-Files one cannot easily crack as with JET,
and a lot more ...

Does it work properly with VB6? How could I install your wrapper with SQLite ?

Of course it does work properly with VB6 - the vbRichClient-project was
written (in VB6) as a modern enhancement-framework for the somewhat dusty
vbRuntime.

But there's also a well-working ODBC-Driver, which one can use in conjunction
with MS-ADO (though its download-size is larger than the RC5-framework).

Here's VB6-Code for quite a number of RC5-based SQLite-Demos (including a _Hello_World-Folder):
http://vbRichClient.com/Downloads/SQLiteDemos.zip

Is SQLite a multi-user database ?

Not really, because it is thought to work InProcess in your Client-App -
and this - along with the SingleFile-DB-feature, is very comparable with JET.

*But* - other than JET (which supports only SingleReader/SingleWriter -threads
or -processes at any given time) - SQLite supports MultipleReader/SingleWriter -threads
or -processes.

That's the reason why you see it quite often in multiuser-scenarios, although the
server-instance is then usually provided by a WebServer (or your own AppServer-
instance). Many OpenSource Web-frameworks or -CMS use it as their "shipping-default-
DB-Engine", which is often good enough for "lower, or normal - mostly reading - traffic".

There's a "ready to use" (multithreaded) AppServer included in the RC5, so you can
set up a real Server-based engine (which works over sockets) using SQLite in the backend
with only a few lines of code.

Olaf

[LaVolpe]

...In brief, how a safearray works ?

I use SafeArrays to manipulate array structure quite often and am very familiar with them. For example, I will use a safearray to reference a byte array as a long array or vice versa, use safearray to change the LBound, use safearray to reference a VB string as an array and various other scenarios:

Private Type SafeArrayBound          ' the bounds description for each dimension
    cElements As Long
    ' ^ total number of items in this dimension
    lLbound As Long
    ' ^ the LBound of this dimension: UBound = lLbound + cElements - 1
End Type
Private Type SafeArray
    cDims As Integer
    ' ^ how many dimensions for this array
    fFeatures As Integer
    ' ^ not applicable for typical usage
    cbElements As Long
    ' ^ bytes per array item: 1=byte array, 2=integer, 4=long
    cLocks As Long
    ' ^ should always be zero. Prevents array from being resized if set
    pvData As Long
    ' ^ the memory address of the 1st array item
    rgSABound As SafeArrayBound     ' 1 dim array
    ' ^ to make multidimensional:  rgSABound(0 To x) As SafeArrayBound
    ' when multidimensional you fill these out from right to left:
    ' rgSABound(0).cElements = nr UBound(myArray,2)-LBound(myArray,2)+1
    ' rgSABound(1).cElements = nr UBound(myArray,1)-LBound(myArray,1)+1
End Type

' ^^ notes: the .rgSABound().cElements & .pvData don't have to reflect the entire array if not needed
' pvData is a pointer to the 1st array element you want to reference 
' .rgSABound().cElements is the total number of elements you want to reference
' If multidimensional array, obviously only the 1st .cElements can be adjusted

To apply the array, you must overlay the structure on the array pointer returned from APIs. The structure must be removed from the array else a crash can be expected, delayed but expected.

Also, I generally don't overwrite the existing structure because that requires more work of backing it up, ovwerwriting it, then restoring it. My standard operating procedures are to use a dummy array like so:

Let's say I wanted to reference a 2D Long array as a 1D zero-bound Long array:

Dim tSA As SafeArray ' 1D version
Dim arrDummy() as Long
With tSA
        .cbElements = 4 ' Long (4 bytes per array element)
        .cDims = 1 ' 1D array
        .rgSABound.cElements = x 
        ' ^^ calculate total number of array elements you want to reference
        .pvData = VarPtr(sourceArray(LBound(sourceArray,1), LBound(sourceArray,2))
End With
CopyMemory ByVal VarPtrArray(arrDummy()), VarPtr(tSA), 4& ' overlay array on Dummy

' before routine exits, remove the overlay
CopyMemory ByVal VarPtrArray(arrDummy), 0&, 4&

API declarations:

' VB5 users change msvbvm60 to msvbvm50 below
Private Declare Function VarPtrArray Lib "msvbvm60.dll" Alias "VarPtr" (ByRef ptr() As Any) As Long
Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByRef Destination As Any, ByRef Source As Any, ByVal Length As Long)

Do not use the above examples for String() arrays. They break the rules and require a bit more work.

Edited: if really interested, you can play with a project I created about 7 years ago. It's on planetsource code