Minimal working example with VBA function unroll()
,
which takes a reference to a cell with a single function
(like sum
,count
, min
) and unrolls its argument (a list of ranges) as a list of single cells.
Option Explicit
Function rangeText(s As String) As String
Dim i As Integer, j As Integer
i = Excel.WorksheetFunction.Find("(", s)
j = Excel.WorksheetFunction.Find(")", s)
rangeText = Mid(s, i + 1, j - i - 1)
End Function
Function rangeToList(s As String)
Dim rg As Range: Set rg = Range(s)
Dim i, j As Integer: Dim c As String
For j = 0 To rg.Rows.Count - 1
For i = 0 To rg.Columns.Count - 1
c = c + IIf(c <> "", ",", "") + Chr(64 + rg.Column() + i) + Format(rg.Row() + j)
Next i
Next j
rangeToList = c
End Function
Function unroll(x As Range) As String
Dim s As String: Dim i, j As Integer: Dim list() As String
If Not x.HasFormula Then
s = "Not a formula"
Else
s = rangeText(x.Formula)
list = Split(s, ",")
s = ""
For i = 0 To UBound(list)
s = s + IIf(i > 0, ",", "") + rangeToList(list(i))
Next i
End If
unroll = s
End Function
![enter image description here](../../I/static/images/f78f59ab2c7ceb4a08f56a43c31c73707f2a58c602c7873e2a46067601864800.png)
Note: as a minimal example, it does not handle
two-letters column references correctly.
* Edit *
Added Function ColumnNoToName
to handle cell references with columns > 26.
![enter image description here](../../I/static/images/c726ffd7dac3dff3140042beb056210b349959fc2a54a76ed5def04d829e43f3.png)
Option Explicit
Function rangeText(s As String) As String
Dim i As Integer, j As Integer
i = Excel.WorksheetFunction.Find("(", s)
j = Excel.WorksheetFunction.Find(")", s)
rangeText = Mid(s, i + 1, j - i - 1)
End Function
Function ColumnNoToName(colNo As Integer) As String
Dim lo, hi As Integer: Dim s As String
lo = (colNo - 1) Mod 26
If colNo > 26 Then
hi = (colNo - 1 - lo) \ 26
s = Chr(64 + hi)
End If
s = s + Chr(64 + lo + 1)
ColumnNoToName = s
End Function
Function rangeToList(s As String)
Dim rg As Range: Set rg = Range(s)
Dim i, j As Integer: Dim c As String
For j = 0 To rg.Rows.Count - 1
For i = 0 To rg.Columns.Count - 1
c = c + IIf(c <> "", ",", "") _
+ ColumnNoToName(rg.Column() + i) _
+ Format(rg.Row() + j)
Next i
Next j
rangeToList = c
End Function
Function unroll(x As Range) As String
Dim s As String: Dim i, j As Integer: Dim list() As String
If Not x.HasFormula Then
s = "Not a formula"
Else
s = rangeText(x.Formula)
list = Split(s, ",")
s = ""
For i = 0 To UBound(list)
s = s + IIf(i > 0, ",", "") + rangeToList(list(i))
Next i
End If
unroll = s
End Function
Function cellFormula(x As Range) As String
cellFormula = x.Formula
End Function
3I'm confused why you need to do this... – Aurus Huang – 2017-04-07T06:10:31.810
Short answer: no, you can't do it unless typing it manually. I agree with previous comment: why do you need it? The result will be exactly the same. – Máté Juhász – 2017-04-07T06:17:01.117
Hi, @AurusHuang & Mate, there are two main reasons, first, to show students how to work on formulas (a little bit complicated to enter into details), and secondly, think of a scenario where you need to shuffle columns around, and you have on column with sums of other (say previous) columns. – streamline – 2017-04-07T07:04:11.987
Thank you, @Louis, for edits. If the question is answered or inadequate, please close it. – streamline – 2017-04-07T07:07:36.163
I think you can do this with VBA. It will be a little bit tricky and complicated. But you have access to all elements of the range, you can iterate over the elements and you can derefer the single elements of the range. – IQV – 2017-04-07T07:25:29.320
Hi, @IQV, those were my thoughts, exactly. Still, I was wondering if there's some command / function in Excel that I didn't know about. Also, I know a little bit of macro programming, but I'm afraid I won't produce the cleanest code. Do you have any suggestions? Thanks for your input! – streamline – 2017-04-07T07:39:46.377
1It's a long time I wrote VBA-macros. But the
Range
-function, simplefor
-loops and the functionsCells
andOffset
should do it for your problem. – IQV – 2017-04-07T07:43:34.850Why does it matter if you produce clean code? You're writing a macro, not a program! Second, you can use Range("A1").Formula to get the formula. From here, you perform the logic and replace the cell contents – Dave – 2017-04-07T08:16:56.973
a simple program in any programming language could do that, you could hire somebody to write a web interface to do that, it may cost you but not much, like somebody may do it for $10 – barlop – 2017-04-07T08:35:25.693
@barlop, the question was not about paying someone to do it, but to learn how to do it. I have lots of students who major in computer sciences and that can do it for free. that's not the problem... – streamline – 2017-04-07T14:18:41.727
@streamline well, you're a bit lucky to have got an answer that gave you a programmed solution.. 'cos the site for programming is stackoverflow, but they'd say they don't do homework type questions where somebody says they want a program written.. They may even have said that here, except that it was possible that maybe it didn't need programming.. If you want to learn then the expected wawy is to see ah ok it requires VBA, and then try and do something simpler like make a macro whereby if you type =A in a cell, it will say =Z and if you get stuck, ask.. And build it up to what you want. – barlop – 2017-04-07T14:45:48.750
@Barlop, I appreciate any kind of answers, even criticism if it is constructive. As I said, I wanted to show to my students that have minimal Excel skills some stuff about computing the variance for multiple samples and this entitles shuffling columns around. As for the 'gift', I asked for some advice. Being 'lucky' is a matter of interpretation. It is up to the moderators to close/block an inadequate/improper question, and to the veteran users to report/modify it, as/if needed. I'm convinced that Dave's answer will help a lot of people, undoubtedly! – streamline – 2017-04-07T20:28:14.140
Converted is a strong word. SUM(A1:D2) is the same as SUM(A1,A2,B2,...D2) is the same as =A1+A2....+D2 is the same as SUM(A1:A2, B1:B2, ..., D1:D2). I believe using VBA ins't even a factor unless you like programming for the sake of programming. For fun programmers do mundane things like this, but it's what programmers do. – ejbytes – 2017-04-19T21:47:06.743