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
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