How to write/recover user defined object variables to worksheets for preference, else arrays

Working with customised excel ribbons, I can save some ribbon.control objects as user-defined object variables, by recognising their control.IDs during CallBacks, and setting my own corresponding object variables. This works ok: new object variables yield the same ID values as the original ribbon.control objects they were derived from. These user defined variables help to coordinate Button property changes as task context changes occur within the task that the buttons control.

To improve the ease with which I select the appropriate object variables, I would like to save the object variables, or a reference to each object variable, on an excel sheet (for choice) or an array. The 2-D positioning of the objects on a sheet would place them in logical relations to other data, and hence simplify coding.

If Collections are the way forward, I have used collections to create and evaluate responses to ctls in UserForms that have been created on the fly. But I'd rather read the variables from excel cells!! And arrays have the same dimensions as sheets.

I realise that the object variables will not persist when the excel file is closed.
I've read this: https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/creating-object-variables

I know nothing about the possibilities, practicalities and risks of what I think I'd like to do.
Could you give me some advice, or guide me to resources, please.

Thanks
Kelvin
Kelvin4Asked:
Who is Participating?
 
jkpieterseCommented:
I'm not sure why you would need the pointers to the objects. You would normally use ribbon callbacks which fire upon Invalidate (those will return the value to the right control) and have functions which you can call from those callbacks to decide what needs to be done for a specific control.
0
 
Fabrice LambertFabrice LambertCommented:
If I understand right, you want to save the ribbon state when the workbook is closed, and restore it when the workbook is opened ?
0
 
Kelvin4Author Commented:
Sorry, Fabrice: I'll clarify:
The MyTag string, that defines via callbacks which btns are visible/enabled, is saved to sheet when the workbook is closed, and recovered as MyTag when the workbook is open. Its not a problem, and is how I re-open the ribbon with the same active btns visible, as when the file was closed.

My PROBLEM comes typically when I have a toggle btn in a Tab which is set either to Read_write or to Read_only. It determines whether button processes can or cannot be carried out, without altering their visibility/enablement status. At some later point in the process, I want to re-set these btns (from Read only to Read write, or vice versa). To do this I wish to have access to the toggle buttons concerned as objects, so I can change displayed images in toggle btns directly (and behind the scenes, read write status). If there were just done on one instance of this toggle re-set process, I could adapt the existing callouts (and I have done so). However, this re-setting of toggle images is now required  at least once on every custom Tab I create. I need to be sure that the images re set are on the same tab as the butns whose actions resets the image. Cross-talk between tabs would be bad.

So I decided it was time to set myself up with object variables for the butns concerned, a total of one toggle and one reSet butn per tab. For ease of use I'd like these variables to be located in a matrix structure to I can refer to the correct stored object variable, just as I would use .cells methods for worksheet cell addresses, or index pairs for 2D-arrays.

As I have already got ribbon Tab-related data in a worksheet, it was natural to ask "can I save object variables to worksheet cells", as it would be so easy to recover the right object variable from such a data structure.

My question is about how to storing control object variables whilst the file is open, speaking under correction, I dont think its about ribbon technicalities.

Thanks!

Kelvin
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Kelvin4Author Commented:
Thanks for your interest, and I do understand your legitimate concerns. But I cant really go into the purpose of my ribbon, and hence the options open for its design. But thanks..  :-) .

As per the title, IS it possible to save object variables in Excel workbook cells, and how?

With respect to all Expert Commentators, yrs
Kelvin
0
 
Kelvin4Author Commented:
Thanks for quick responses - both
jkp: I slept on your comments and accept your points. Have just done a pilot and am getting what I want, as you suggested. Thanks!!
0
 
jkpieterseCommented:
To answer your question: Yes you can, provided it is only used in the currently running Excel session. As soon as the workbook is closed the pointers must be invalidated and NOT BE USED, otherwise Excel will crash!
The code below assumes you have a range name called RibbonPointer. This range name is used to temporarily store the address pointer to the moRibbon object variable which is instated in the onLoad callback:

Dim moribbon As IRibbonUI
'Note conditional compilation!  Used because VBA7 (64bit) uses a different API declaration syntax
#If VBA7 Then
Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
       (ByRef destination As Any, ByRef SOURCE As Any, ByVal length As Long)
#Else
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
                              (ByRef destination As Any, ByRef SOURCE As Any, ByVal length As Long)
#End If

'onLoad callback of the ribbon, sets the object varaible and stores its pointer
Sub rxreftreecustomUI_onLoad(ribbon As IRibbonUI)    'IRibbonUI)
    Set moribbon = ribbon
    shAppSettings.Range("RibbonPointer").Value = ObjPtr(ribbon)
    ThisWorkbook.Saved = True
End Sub

'Use the pointer to the ribbon object so you can invalidate it and such.
#If VBA7 Then
Private Function GetRibbon(ByVal lRibbonPointer As LongPtr) As Object
#Else
Private Function GetRibbon(ByVal lRibbonPointer As Long) As Object
#End If
    Dim oribbon As IRibbonUI
    CopyMemory oribbon, lRibbonPointer, LenB(lRibbonPointer)
    Set GetRibbon = oribbon
    Set oribbon = Nothing
End Function

Public Function GetRibbonObjectReference() As IRibbonUI
    If moribbon Is Nothing Then
        Set moribbon = GetRibbon(shAppSettings.Range("RibbonPointer").Value)
    End If
    Set GetRibbonObjectReference = moribbon
End Function

Public Sub InValidateRibbon(Optional Dummy As Boolean)
    If Val(Application.Version) >= 12 Then
        GetRibbonObjectReference.Invalidate
    End If
End Sub

Open in new window

0
 
Kelvin4Author Commented:
Thanks, that was very generous.
Kelvin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.