Data resources in VBA code

[Yacine]

Say you have code which relies on bigger data resources - in my case the list of all standard shapesheet cells.
If I wanted to avoid writing this list directly in the VBA code to keep it maintainable, I would like to save this list in a resource file.
One neat solution could consist of a simple shape in which you put the list as its text.
You would then drop this shape to the stencil of your solution and name it as desired.
Your code can now access this list by means of the following macro.

Code Select Expand


Function getMasterText(stencilName As String, masterName As String) As String
    Dim stencil_ As Document
    Set stencil_ = Documents(stencilName)
    Dim Mstr As Master
    Set Mstr = stencil_.Masters(masterName)
    Dim shp As Shape
    Set shp = Mstr.Shapes(Mstr.Shapes.Count)
    getMasterText = shp.Text
End Function


[Thomas Winkel]

Hi Yacine,

interesting approach.
In addition you could consider to hide the master in the stencil if it's only for data storage: Mstr.Hidden = true

Another approach could be to store the data in the stencils document ShapeSheet as User property.
Or in the document template.
But ShapeSheet cells are limited to 64k, shape text is not
Also shape text has the benefit that it's easier to store complex strings like JSON because in ShapeSheet you always need to escape " like so: ="Say ""Hello"""

Mabe I can use your idea to store big tables in JSON format, I'll keep that in mind