Does VBA support flattening/serializing to "binary" string?

T

tbd

[Excel 2002 says] Jello again!
(please be patient, there is a question below...)
I'm working on a spreadsheet that's formatted to look like a calendar.
There's a month-name across the top of the sheet (spanning multiple cells);
Weekday names under that, and "days" arranged just like squares you see on a
calendar. Days are always a fixed width (two cells) but each day can hold a
different "list" - the number of rows in each day WILL vary (OK, the days are
really rectangles). It's expected the list in each day will contain 4 to 10
rows.

When the user changes the sheet, it initiaites a lot of input
validation and processing which are very dependent on the date, and row/col
within the date being edited. It seems impractably messy to figure out the
date/row/col from absolute coordinates given that, calendar top/left is
variable, rows/cols between cells is variable, day height (row-count) is
variable, (as user adds rows, can grow, pushing down remaining weeks.) I'm
not saying it's impossible, just impractical.

If not, I could write date/row/col data on a "shadow" sheet. It would
be great if a date/row/col typedef could be cast to/from a string stored in
shadow-sheet:
Q. Is there a way, with VBA, to cast arbitrary data-structures to/from
strings?
Correlary question: Can cells reliably store "binary" strings?

Any help, ideas, constructive criticism is appreciated,
Cheers!
 
T

tbd

Hi Offace,
This is great, definately food for thought - started experimenting and
anticipate it may take a while. Wanted to say THANKS! Assuming VBA doesn't
have a simple casting syntax, you're suggestions seem the best - perhaps only
- alternative.
For the record, this is what I'm trying to accomplish (without CType.)
There would be a symmetric Write function as well.

Thanks/Cheers!
P.S. This reply dialog "differs" from what I'm used to(?)
Code: 
Public Type DayKey
dDate As Date
iRow As Integer
iCol As Integer
End Type

Public Sub QD_Read(ByVal AbsRow As Integer, _
ByVal AbsCol As Integer, _
ByRef dDate As Date, _
ByRef datRow As Integer, _
ByRef datCol As Integer)
Dim tKey As DayKey

tKey = CType(WS.Cells(AbsRow, AbsCol).Text, DayKey)

dDate = tKey.dDate
datRow = tKey.iRow
datCol = tKey.iCol
End Sub
 
O

Offace

| [Excel 2002 says] Jello again!
| (please be patient, there is a question below...)
| I'm working on a spreadsheet that's formatted to look like a calendar.
| There's a month-name across the top of the sheet (spanning multiple cells);
| Weekday names under that, and "days" arranged just like squares you see on a
| calendar. Days are always a fixed width (two cells) but each day can hold a
| different "list" - the number of rows in each day WILL vary (OK, the days are
| really rectangles). It's expected the list in each day will contain 4 to 10
| rows.
|
| When the user changes the sheet, it initiaites a lot of input
| validation and processing which are very dependent on the date, and row/col
| within the date being edited. It seems impractably messy to figure out the
| date/row/col from absolute coordinates given that, calendar top/left is
| variable, rows/cols between cells is variable, day height (row-count) is
| variable, (as user adds rows, can grow, pushing down remaining weeks.) I'm
| not saying it's impossible, just impractical.
|
| If not, I could write date/row/col data on a "shadow" sheet. It would
| be great if a date/row/col typedef could be cast to/from a string stored in
| shadow-sheet:
| Q. Is there a way, with VBA, to cast arbitrary data-structures to/from
| strings?
| Correlary question: Can cells reliably store "binary" strings?
|
| Any help, ideas, constructive criticism is appreciated,
| Cheers!

Hello tbd, I am not sure if this is relevant, nor if you have already looked into
these functions.
If they apply then bonus, if not sorry. These functions deal with either
Variants, Objects or Strings as Pointers.

I'm going off a long memory here, you may need to reference stdole library in
references, have you looked at?

- StrPtr(Ptr As String)
- VarPtr(Ptr As Any)
- ObjPtr(Ptr As UnKnown)

All return a Long, being a pointer to in your case a string buffer, that can be
assigned into a Byte() Array. Again this is off a long memory, but you may need
to utilise the RtlMoveMemory API function to copy, and clear the memory allocated.

Now that's in VBA, not an Excel Cell.

By "data-structures" do you mean something like,

Public Type DataStructure

struct member a plenty
End Type

For this take a look at http://vb.mvps.org/tips/varptr.asp, especially the part on
VarPtr, it deals with User-Defined Types.

I really hope there's something in this that will be of use to you, if not my
apologies.

Regard,

- Offace
 
O

Offace

Look into those functions (as you doing) tdb, I really think you need to clear the
memory allocated by those functions manually, I said before it's been a long time
since I have used those functions. Do post back your experiment results, this
seems quite interesting, you are on your way a low(er) level vba programming, also
with regards to your other posts, I'm waiting for you to dump what you're doing,
and re-doing it all with C++ DLLs.

Also note that I have seen these function (not properly used in wrapper
functions), crash and\or freeze apps, despite VBA being quite a "forgiving"
language. Please look into the memory, locking and clearing it when your done,
the more I think about it the comes back to me, a vague example approach;

I've just found some really old code, here's a mash up....

Code: 
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal
dwBytes As Long) As Long
Private Declare Function GlobalFree Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As
Any, hpvSource As Any, ByVal cbCopy As Long)

Public Type DayKey
dDate As Date
iRow As Integer
iCol As Integer
End Type

Dim tKey As DayKey
Dim hMem As Long, pMem As Long
Dim dteTheDate As Date

hMem = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, Len(tKey))
pMem = GlobalLock(hMem)   'Lock and get pointer
CopyMemory ByVal pMem, tKey, Len(tKey)    'Copy structure's contents into
block

With tKey

'Do your\some stuff with you struct members

End With    'tKey


CopyMemory tKey, ByVal pMem, Len(tKey)

dteTheDate = tKey.dDate
'... And so on....

' ******* Here's the part I want to drill in to your function, if this approach
works for you ****************************
'  Put these in an function\sub exit, even if the function catches an
exception.

Call GlobalUnlock(hMem)   ' Important!
Call GlobalFree(hMem)       ' Important!


I hope this helps out a bit more, and is relevant for you. Oh yes one more thing;

Private Const GMEM_MOVEABLE = &H2
Private Const GMEM_ZEROINIT = &H40

Go well ;~)

- Offace


| Hi Offace,
| This is great, definately food for thought - started experimenting and
| anticipate it may take a while. Wanted to say THANKS! Assuming VBA doesn't
| have a simple casting syntax, you're suggestions seem the best - perhaps only
| - alternative.
| For the record, this is what I'm trying to accomplish (without CType.)
| There would be a symmetric Write function as well.
|
| Thanks/Cheers!
| P.S. This reply dialog "differs" from what I'm used to(?)
|
Code: 
| Public Type DayKey
|  dDate As Date
|  iRow As Integer
|  iCol As Integer
| End Type
|
| Public Sub QD_Read(ByVal AbsRow As Integer, _
|                   ByVal AbsCol As Integer, _
|                   ByRef dDate As Date, _
|                   ByRef datRow As Integer, _
|                   ByRef datCol As Integer)
|  Dim tKey As DayKey
|
|  tKey = CType(WS.Cells(AbsRow, AbsCol).Text, DayKey)
|
|  dDate = tKey.dDate
|  datRow = tKey.iRow
|  datCol = tKey.iCol
| End Sub
|
 
T

tbd

Wow, Offace. I've been programming a long time (not VB/VBA) and only used
the Win32 API occasionally. You seem VERY familier with this environment.

Was hoping that with memory address from VarPtr (Thank you for link) , I
could "trick" VBA into storing/reading flattened Type, as string. But
flattened Type could have \0 bytes and that might screw-up reading back
string.

To be honest, this seems a bit more complex than I was hoping but I'll
probably look at it again in 6 or 8 hours - and will remember to Unlock and
Free if I allocate and lock!

Many thanks!
;^)

Offace said:
Look into those functions (as you doing) tdb, I really think you need to clear the
memory allocated by those functions manually, I said before it's been a long time
since I have used those functions. Do post back your experiment results, this
seems quite interesting, you are on your way a low(er) level vba programming, also
with regards to your other posts, I'm waiting for you to dump what you're doing,
and re-doing it all with C++ DLLs.

Also note that I have seen these function (not properly used in wrapper
functions), crash and\or freeze apps, despite VBA being quite a "forgiving"
language. Please look into the memory, locking and clearing it when your done,
the more I think about it the comes back to me, a vague example approach;

I've just found some really old code, here's a mash up....

Code: 
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal
dwBytes As Long) As Long
Private Declare Function GlobalFree Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As
Any, hpvSource As Any, ByVal cbCopy As Long)

Public Type DayKey
dDate As Date
iRow As Integer
iCol As Integer
End Type

Dim tKey As DayKey
Dim hMem As Long, pMem As Long
Dim dteTheDate As Date

hMem = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, Len(tKey))
pMem = GlobalLock(hMem)   'Lock and get pointer
CopyMemory ByVal pMem, tKey, Len(tKey)    'Copy structure's contents into
block

With tKey

'Do your\some stuff with you struct members

End With    'tKey


CopyMemory tKey, ByVal pMem, Len(tKey)

dteTheDate = tKey.dDate
'... And so on....

' ******* Here's the part I want to drill in to your function, if this approach
works for you ****************************
'  Put these in an function\sub exit, even if the function catches an
exception.

Call GlobalUnlock(hMem)   ' Important!
Call GlobalFree(hMem)       ' Important!


I hope this helps out a bit more, and is relevant for you. Oh yes one more thing;

Private Const GMEM_MOVEABLE = &H2
Private Const GMEM_ZEROINIT = &H40

Go well ;~)

- Offace


| Hi Offace,
| This is great, definately food for thought - started experimenting and
| anticipate it may take a while. Wanted to say THANKS! Assuming VBA doesn't
| have a simple casting syntax, you're suggestions seem the best - perhaps only
| - alternative.
| For the record, this is what I'm trying to accomplish (without CType.)
| There would be a symmetric Write function as well.
|
| Thanks/Cheers!
| P.S. This reply dialog "differs" from what I'm used to(?)
|
Code: 
| Public Type DayKey
|  dDate As Date
|  iRow As Integer
|  iCol As Integer
| End Type
|
| Public Sub QD_Read(ByVal AbsRow As Integer, _
|                   ByVal AbsCol As Integer, _
|                   ByRef dDate As Date, _
|                   ByRef datRow As Integer, _
|                   ByRef datCol As Integer)
|  Dim tKey As DayKey
|
|  tKey = CType(WS.Cells(AbsRow, AbsCol).Text, DayKey)
|
|  dDate = tKey.dDate
|  datRow = tKey.iRow
|  datCol = tKey.iCol
| End Sub
|
Click to expand...
 
O

Offace

I think VarPtr (emphasise I think) is a wrapper of some sort of the API functions
below. Again off memory, I think it should be OK if you read back a null, or null
terminating string. In VBA this is, VBA.Constants.vbNullChar (= \0). When
storing your type as a string, you may append a null terminating string at the end
of you string, this is (I think again) only necessary if you're returning a string
from a function, just remember to remove the \0.

If you can achieve it, great, you'll have a powerful tool or two for future
projects. Here's another link for you;

http://www.codeproject.com/KB/vbscript/UB_Pointers_In_VB.aspx
http://www.thevbzone.com/secrets.htm#VarPtr

Try and have yourself a weekend tbd, things like this may come together when you
take your time with them.

Go well ;~)


| Wow, Offace. I've been programming a long time (not VB/VBA) and only used
| the Win32 API occasionally. You seem VERY familier with this environment.
|
| Was hoping that with memory address from VarPtr (Thank you for link) , I
| could "trick" VBA into storing/reading flattened Type, as string. But
| flattened Type could have \0 bytes and that might screw-up reading back
| string.
|
| To be honest, this seems a bit more complex than I was hoping but I'll
| probably look at it again in 6 or 8 hours - and will remember to Unlock and
| Free if I allocate and lock!
|
| Many thanks!
| ;^)
|
| "Offace" wrote:
|
| > Look into those functions (as you doing) tdb, I really think you need to clear
the
| > memory allocated by those functions manually, I said before it's been a long
time
| > since I have used those functions. Do post back your experiment results, this
| > seems quite interesting, you are on your way a low(er) level vba programming,
also
| > with regards to your other posts, I'm waiting for you to dump what you're
doing,
| > and re-doing it all with C++ DLLs.
| >
| > Also note that I have seen these function (not properly used in wrapper
| > functions), crash and\or freeze apps, despite VBA being quite a "forgiving"
| > language. Please look into the memory, locking and clearing it when your
done,
| > the more I think about it the comes back to me, a vague example approach;
| >
| > I've just found some really old code, here's a mash up....
| >
| >
Code: 
| >
| > Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As
Long
| > Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long,
ByVal
| > dwBytes As Long) As Long
| > Private Declare Function GlobalFree Lib "kernel32" (ByVal hMem As Long) As
Long
| > Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As
Long
| > Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest
As
| > Any, hpvSource As Any, ByVal cbCopy As Long)
| >
| > Public Type DayKey
| >     dDate As Date
| >     iRow As Integer
| >     iCol As Integer
| > End Type
| >
| > Dim tKey As DayKey
| > Dim hMem As Long, pMem As Long
| > Dim dteTheDate As Date
| >
| >     hMem = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, Len(tKey))
| >     pMem = GlobalLock(hMem)   'Lock and get pointer
| >     CopyMemory ByVal pMem, tKey, Len(tKey)    'Copy structure's contents into
| > block
| >
| >     With tKey
| >
| >         'Do your\some stuff with you struct members
| >
| >     End With    'tKey
| >
| >
| >     CopyMemory tKey, ByVal pMem, Len(tKey)
| >
| >     dteTheDate = tKey.dDate
| >     '... And so on....
| >
| >    ' ******* Here's the part I want to drill in to your function, if this
approach
| > works for you ****************************
| >    '  Put these in an function\sub exit, even if the function catches an
| > exception.
| >
| >    Call GlobalUnlock(hMem)   ' Important!
| >    Call GlobalFree(hMem)       ' Important!
| >
| >
| >
| >
| > I hope this helps out a bit more, and is relevant for you. Oh yes one more
thing;
| >
| > Private Const GMEM_MOVEABLE = &H2
| > Private Const GMEM_ZEROINIT = &H40
| >
| > Go well ;~)
| >
| > - Offace
| >
| >
| > | > | Hi Offace,
| > | This is great, definately food for thought - started experimenting and
| > | anticipate it may take a while. Wanted to say THANKS! Assuming VBA doesn't
| > | have a simple casting syntax, you're suggestions seem the best - perhaps
only
| > | - alternative.
| > | For the record, this is what I'm trying to accomplish (without CType.)
| > | There would be a symmetric Write function as well.
| > |
| > | Thanks/Cheers!
| > | P.S. This reply dialog "differs" from what I'm used to(?)
| > |
Code: 
| > | Public Type DayKey
| > |  dDate As Date
| > |  iRow As Integer
| > |  iCol As Integer
| > | End Type
| > |
| > | Public Sub QD_Read(ByVal AbsRow As Integer, _
| > |                   ByVal AbsCol As Integer, _
| > |                   ByRef dDate As Date, _
| > |                   ByRef datRow As Integer, _
| > |                   ByRef datCol As Integer)
| > |  Dim tKey As DayKey
| > |
| > |  tKey = CType(WS.Cells(AbsRow, AbsCol).Text, DayKey)
| > |
| > |  dDate = tKey.dDate
| > |  datRow = tKey.iRow
| > |  datCol = tKey.iCol
| > | End Sub
| > |
| >
| >
| >
 
T

tbd

Hi Offace,
I was ready to put this behind me - opting to just store/parse ascii -
but one of the links you provided gives a really nice tutorial on pointers
and using CopyMemory. I've had some success experimenting, for instance -

StrPtr(rRange.Cells(x,x).ID)
actually returns a (unicode) string-buffer address (assuming the ID is
written once) - and CopyMemory can write/read it. Whether this will lead to
a quick/reliable data-serializer, I'm not sure yet...
I think it should be OK if you read back a null, or null terminating string.
Click to expand...
I think so too - now that I read that link! It described BSTRs keeping
str-length in leading bytes (so \0 isn't necessarily a sentinel.)

Anyway, I gotta quit for now, but will almost certainly have something
working "soon". With luck, no more building/parsing ascii - excellent!

Cheers!

Offace said:
I think VarPtr (emphasise I think) is a wrapper of some sort of the API functions
below. Again off memory, I think it should be OK if you read back a null, or null
terminating string. In VBA this is, VBA.Constants.vbNullChar (= \0). When
storing your type as a string, you may append a null terminating string at the end
of you string, this is (I think again) only necessary if you're returning a string
from a function, just remember to remove the \0.

If you can achieve it, great, you'll have a powerful tool or two for future
projects. Here's another link for you;

http://www.codeproject.com/KB/vbscript/UB_Pointers_In_VB.aspx
http://www.thevbzone.com/secrets.htm#VarPtr

Try and have yourself a weekend tbd, things like this may come together when you
take your time with them.

Go well ;~)


| Wow, Offace. I've been programming a long time (not VB/VBA) and only used
| the Win32 API occasionally. You seem VERY familier with this environment.
|
| Was hoping that with memory address from VarPtr (Thank you for link) , I
| could "trick" VBA into storing/reading flattened Type, as string. But
| flattened Type could have \0 bytes and that might screw-up reading back
| string.
|
| To be honest, this seems a bit more complex than I was hoping but I'll
| probably look at it again in 6 or 8 hours - and will remember to Unlock and
| Free if I allocate and lock!
|
| Many thanks!
| ;^)
|
| "Offace" wrote:
|
| > Look into those functions (as you doing) tdb, I really think you need to clear
the
| > memory allocated by those functions manually, I said before it's been a long
time
| > since I have used those functions. Do post back your experiment results, this
| > seems quite interesting, you are on your way a low(er) level vba programming,
also
| > with regards to your other posts, I'm waiting for you to dump what you're
doing,
| > and re-doing it all with C++ DLLs.
| >
| > Also note that I have seen these function (not properly used in wrapper
| > functions), crash and\or freeze apps, despite VBA being quite a "forgiving"
| > language. Please look into the memory, locking and clearing it when your
done,
| > the more I think about it the comes back to me, a vague example approach;
| >
| > I've just found some really old code, here's a mash up....
| >
| >
Code: 
| >
| > Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As
Long
| > Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long,
ByVal
| > dwBytes As Long) As Long
| > Private Declare Function GlobalFree Lib "kernel32" (ByVal hMem As Long) As
Long
| > Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As
Long
| > Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest
As
| > Any, hpvSource As Any, ByVal cbCopy As Long)
| >
| > Public Type DayKey
| >     dDate As Date
| >     iRow As Integer
| >     iCol As Integer
| > End Type
| >
| > Dim tKey As DayKey
| > Dim hMem As Long, pMem As Long
| > Dim dteTheDate As Date
| >
| >     hMem = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, Len(tKey))
| >     pMem = GlobalLock(hMem)   'Lock and get pointer
| >     CopyMemory ByVal pMem, tKey, Len(tKey)    'Copy structure's contents into
| > block
| >
| >     With tKey
| >
| >         'Do your\some stuff with you struct members
| >
| >     End With    'tKey
| >
| >
| >     CopyMemory tKey, ByVal pMem, Len(tKey)
| >
| >     dteTheDate = tKey.dDate
| >     '... And so on....
| >
| >    ' ******* Here's the part I want to drill in to your function, if this
approach
| > works for you ****************************
| >    '  Put these in an function\sub exit, even if the function catches an
| > exception.
| >
| >    Call GlobalUnlock(hMem)   ' Important!
| >    Call GlobalFree(hMem)       ' Important!
| >
| >
| >
| >
| > I hope this helps out a bit more, and is relevant for you. Oh yes one more
thing;
| >
| > Private Const GMEM_MOVEABLE = &H2
| > Private Const GMEM_ZEROINIT = &H40
| >
| > Go well ;~)
| >
| > - Offace
| >
| >
| > | > | Hi Offace,
| > | This is great, definately food for thought - started experimenting and
| > | anticipate it may take a while. Wanted to say THANKS! Assuming VBA doesn't
| > | have a simple casting syntax, you're suggestions seem the best - perhaps
only
| > | - alternative.
| > | For the record, this is what I'm trying to accomplish (without CType.)
| > | There would be a symmetric Write function as well.
| > |
| > | Thanks/Cheers!
| > | P.S. This reply dialog "differs" from what I'm used to(?)
| > |
Code: 
| > | Public Type DayKey
| > |  dDate As Date
| > |  iRow As Integer
| > |  iCol As Integer
| > | End Type
| > |
| > | Public Sub QD_Read(ByVal AbsRow As Integer, _
| > |                   ByVal AbsCol As Integer, _
| > |                   ByRef dDate As Date, _
| > |                   ByRef datRow As Integer, _
| > |                   ByRef datCol As Integer)
| > |  Dim tKey As DayKey
| > |
| > |  tKey = CType(WS.Cells(AbsRow, AbsCol).Text, DayKey)
| > |
| > |  dDate = tKey.dDate
| > |  datRow = tKey.iRow
| > |  datCol = tKey.iCol
| > | End Sub
| > |
| >
| >
| >
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating calendar days and completion dates. 4
Posting in each row of a Summary sheet the sum of corresponding itemson another sheet 2
Sub to stack data n paste unique order ID lines 3
Excel VBA to go to specific sheet & cell in the current workbook not working. 2
VBA Excel how to assign name in variable range 4
col A,B and C-P moved fr Sheet 1,2, to Sheet 4 4
Macro Fails 2
Cells formaring with VBA ? 0

Top