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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Jan Karel PieterseExcel and VBA ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Jan Karel PieterseExcel and VBA ExpertCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
objects

From novice to tech pro — start learning today.