Variant Trees/Lisp Like Lists in a single cell.

J

John Creighton

I want to be able to create Lisp like nested lists in a spreadsheet. This
would have a wide variety of applications. For instance I could store and ND
array in a single cell of the spreadsheet. I am however not able to Nest more
then 2 deep without getting error 2015.

Right now I am working on a simple function to display the list. Here is an
example function call:

ListDsp(1,List(List(7,List(8,9)),4))

This should return:
‘[1[7,[8,9]],4]

The display function is used to represent it in the spreadsheet. My idea is
if I passed this to an addList the add List would have input arguments of
type range. Therefore it would have access to the underlying formula in the
cell and be able to replace ListDsp with lisp and then let Excel evaluate the
new expression. Here is my code:

Public Function ListDsp(ParamArray args() As Variant) As String

ListDsp = "["

For i = LBound(args, 1) To UBound(args, 1) - 1
If IsArray(args(i)) Then
ListDsp = ListDsp & ListDspStrip(args(i)) & ","

Else
ListDsp = ListDsp & args(i) & ","
End If
Next i
If IsArray(args(i)) Then
ListDsp = ListDsp & ListDspStrip(args(UBound(args))) & "]"
Else
ListDsp = ListDsp & args(UBound(args)) & "]"
End If
End Function
Public Function ListDspStrip(ParamArray args0() As Variant) As String
' Split (myStr,delim)
' re.Pattern = "^\[{0,1}(.*)\]${0,1}" 'Trim Brackets
'Dim result As Range(
'result.value = args(0)
args = args0(0)

ListDspStrip = "["
For i = LBound(args, 1) To UBound(args, 1) - 1
If IsArray(args(i)) Then
ListDspStrip = ListDspStrip & ListDspStrip(args(i)) & ","

Else
ListDspStrip = ListDspStrip & args(i) & ","
End If
Next i
If IsArray(args(i)) Then
ListDspStrip = ListDspStrip & ListDspStrip(args(UBound(args))) & "]"
Else
ListDspStrip = ListDspStrip & args(UBound(args)) & "]"
End If
End Function
Public Function List(ParamArray args() As Variant) As Variant
List = args
End Function
 

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

Top