Build formula in cell

[DocAElstein]

Code: Select all

Sub FlippingDisplayFormulas()
  ActiveWindow.DisplayFormulas = False
  ActiveWindow.DisplayFormulas = True
End Sub

That is a useful thing to know about when playing around and building up formals in ceils. I find it a bit curios that it is a Window property, The documentation currently say something along the lines …. property applies only to worksheets and macro sheets … Perhaps earlier a window and a macro sheet where more closely related. Still I can’t think of any thing to think about , even laterally, to help me think about formulas and windows more closely related. Even here I have a lot of older windows, some of the very first German wood framed double glazing ones, and I can’t see any obvious things to do with formulas that I can relate to them???
A useful thing to know about.

[DocAElstein]

It seemed the simple equation of "=CONCAT("= ",A2," + ",B2," + ",C2)" (without the quotes) gets the result of "= 10 + 11 + 20", ....
..At first I thought you wanted to also include the result in the column, and the only ways I could see to do that were to use VBA:
1) Create a custom format for each cell ..
....
2) Create a custom comment for each cell,

Hi PJ
It’s nice to get the **chance to extend the discussion a while and add usefully to the subject after the original poster is away happy with a solution, JIMHO, making the thread part of a more a useful rich data base.
Also, personally, I think it helps learning and understanding when you see things done in different ways.
(**In most other forum and similar places the Thread would have been closed, deleted, and us chastised or worse.

I don’t have the CONCAT( , or might have on a Excel 2016 I have not got around to using much yet. I probably would not use it for that reason of not being backward compatible. Just a personal preference, that’s all. I like a stable Office up to about 2013, after that I like to avoid it for a lot of reasons, too many to mention, including the disadvantages of non backward compatibility.

_._

Those custom format and commenting were interesting offerings from you. I am not so familiar with custom formatting and comments.
I messed up initially with the first one, when I looked at them. I foolishly ran the macro first and then put a formula in D1, and copied it down. Of course that messes up as then you copy the custom formatting as well, - and that is bad, because you need , just as you said … a custom format for each cell, ….
Any variation of this modification, for example in your first macro would get over that problem, ( if you had not already put the formulas in, in which case you need no modification)

Code: Select all

' NOTE: working formula must be in cell before running this macro
    For Each c In Selection
     Let c = "=" & CStr(c.Offset(0, -3).Value) & "+" & CStr(c.Offset(0, -2).Value) & "+" & CStr(c.Offset(0, -1).Value)

I don’t doubt you know that, I’m am just adding it for the future benefit of anyone passing that may be as slow as me at getting the point, - unlikely perhaps, but theoretically possible, Lol.
In other words the point I am making is that your first macro assumes the formulas are already there, before it is run,
or
that you put them in each cell in a way other than dragging down. That could be done as I showed above at each loop,
or
, in one go like this for example,
Let Selection = "=SUM(A1:C1)"
. which was one of snb’s earlier first offerings
_.__
…

my methods are not as concise as SNB, but I haven't a clue how to decode that bit of programming anyway. Reminds me of one liner APL code and it's esoteric symbols …

….well I am glad you said that and not me, Lol, :) , … esoteric, obfuscated and something else that is pretty true I think but its not liked if I mention it.
They are not usually so difficult to decipher,.. .well, as long a you know the answer, and are very familiar with his "way" of doing it. Sometimes I work through and decipherer them, occasionally it's worth it.
Regarding these couple of offerings, I did take a look at the first one here , ( and here ) ,
, and the second one here
They were more of less than I expected this time around, and so I went off instead in a digressive tangent into the much more detailed very fundamental looking at the more basic coding that they are.

The TLDR is
The first one is this sort of thing
Range("D1:D5") = "=A1+B1+C1" , which is putting the formula in
, the second is this sort of idea
Range("D1:D5") = Evaluate("A1:A5 + B1:B5 + C1:C5") , which is putting the result value in
I would say the originals are a bit too confusing to be helpful, and I would be a bit wary if you don’t already know of the posting "style", but that is just my opinion , whatever turns anyone on, - it takes all sorts to make a less boring world, and we need that to help the AI and Chat bots taking over, Lol!
_.________

I had another think about it since I finally realised exactly what was wanted. This is VBA again, - I realise the OP did not want that and he is long since fully happy, but I thought it might add another useful alternative for the greater Thread discusion
My 2 Cents coding would be something like this . That is again just a personal preference, as I like to have a lot of extra stuff there, but not necessarily see it all the time. The idea to my 2 cents coding is similar to that of me liking a lot of those 'comments over to the right in a code window, where you would only see most of them if you used the horizontal scroll bar to see them: I don’t necessarily want to see them all the time, as they would annoy me as much as they do most people, but I like a lot of info there in case I do need it later for future reference.
My 2 cents coding constructs a string with everything in it, but the extra info is deliberately separated from the main info by a large space and I would deliberately keep the column only narrow enough to see the main info. I do quite like the comment idea of yours as well, although it is approaching the frilly feature side of things I am less keen on. I will have to check that out though and see for example what basic formatting it has in the text.
I don’t like personally custom formatting much, but it is nice to see an example like you did, as it’s the format required that confuses me, so having a few examples is useful.
_.___

..... betting a deceased equine …I was BEATING ..... I swore afterwards they were deceased.

In the extra text that I mentioned that I put in for later reference, I sometimes have some off topic stuff that can help later as a welcome diversion, Perhaps for example, a video link maybe to a favourite song, or even one of those nature videos. Equine can come up there, but usually live ones, - I have heard of some weird Bestiality and necrophilia, but I never took any interest, - I figured that might be just beating a dead horse…..

Alan

[ErikJan]

Some brief comments:

• Nice to see someone mention APL... . When I was very young that was my programming language for years. Very powerful

• I never use or used concatenate (but that doesn't mead it's wrong of course). Personally I find this = " & A2 & " + " & B2 & " + " & C2 easier to read than this =CONCAT("= ",A2," + ",B2," + ",C2)

• And yes, a VBA solution is, as I said, easy. I'm confused by all the fancy code. Building on an earlier example, this does what I want:

Code: Select all

Sub show_formula()
Dim c As Range
For Each c In Selection
    c = "= " & c.Offset(0, -3) & " + " & c.Offset(0, -2) & " + " & c.Offset(0, -1)
Next
End Sub

But, again, I didn't want to use VBA at the time...

[DocAElstein]

That suggest , perhaps that the offset is useful to you , that is to say you would be interested in cells to the left of your selection or something like that, rather than if you knew the cells addresses specifically that you wanted to sum . Otherwise if you did knew the actual cells then, in that case, that was pretty well one of snbs, (even though it might not of looked like it, Lol.)
It was that first one here:
https://eileenslounge.com/viewtopic.php ... 08#p314208 ( which for my greater sins I took a look at here , ( and here )
That solution actually comes down to just
Range("D1:D5") = "=A1+B1+C1"
, which is putting the formula in, as you are.

The second of snbs boiled down to this, on the RHS = Evaluate("A1:A5 + B1:B5 + C1:C5") , which is putting the result value in, since it is evaluating the formula string, rather than putting the formula string in. ( It puts the result of the sum in. We usually think of Evaluate as, well... evaluating. But we can get it to return strings though***)

His first one, as I mentiond, puts the formula string in, and you are doing that as well.

_.__________

Just for fun, this would be a non looping version very close to yours, using the so called, ( or Alan called at any rate ) Evaluate Range type solution. In other words we can also use a Evaluate Range type solution to put the formula string in***. Such things can get a bit messy as quotes in Evaluate are a bit tricky if you don’t understand them. I do , but it took me a few years , Lol

This would be the not using the offset version

Code: Select all

Sub SelOffset()
 Let Selection = Evaluate("""=A1+B1+C1""")
End Sub

What is happening there is that Evsluate returs a simple string in this case, the string we want. That string then goes in the selection


This would be a direct equivalent of yours, using the offset. It’s not too difficult to explain, if you understand the last one. All I am doing is getting those three addresses using the offset, ( and also the Replace I use to get rid of the “$” in the returned address, because I want a relative reference ).
VBA will do that Adress replace stuff first, then it will be left with the formula string which the Evaluate does the business on

Code: Select all

Sub SelOffset2()
 Let Selection = Evaluate("""=" & Replace(ActiveCell.Offset(0, -3).Address, "$", "") & "+" & Replace(ActiveCell.Offset(0, -2).Address, "$", "") & "+" & Replace(ActiveCell.Offset(0, -1).Address, "$", "") & """")
End Sub

( Note: I am using the windows ActiveCell property on the selection. That will return a cell, if it can, which it usually can on a good day. But one important thing to note is that it will usually get the first cell you selected when making your multicall selection. Not a lot of people know that. If you make your selection as most people typically do, starting from top left, then all will be well. Start, for example at the bottom, and you will get weird results as the windows ActiveCell property on such a made selection will likely return the last cell, meaning, as example, where I had a 5 row test range, that I would be putting finally =A5+B5+C5 in the first cell instead of =A1+B1+C1, and that will be bad )


These Evaluate Range solutions are fun, but for smaller data a looping solution is easier to understand and debug if something goes wrong. However for a large amount of data they can be quite a bit faster sometimes

Alan

P.S. Edit. I may have missed the point that you are adding spaces in a particular way that suits you better to look at in the formula bar. My last offering allows you to do that as well, as ever you please

Code: Select all

Selection = Evaluate("""= " & Replace(ActiveCell.Offset(0, -3).Address, "$", "") & " + " & Replace(ActiveCell.Offset(0, -2).Address, "$", "") & " + " & Replace(ActiveCell.Offset(0, -1).Address, "$", "") & """")

Same with all the others.

Code: Select all

Range("D1:D5") = "= A1 + B1 + C1"

etc..... ( The only thing to be careful about is a space before the = , as that will stop the formula being recognised as a formula. I believe ( another Alan theory ), it's the first = that is most important as regards telling Excel its a formula when playiing around with strings in cells, but that's open to debate and opinion as well )

[ErikJan]

As I said... I merely took an earlier example and tweaked it to show how simple all of this is: one line of code in a loop.
That example used the contents of the three cells to the left, yes (as the example did that) but it can be tweaked in any way needed obviously.

Everything I see and read here is (much) more complex and convoluted. Granted, from an academic / learning point of view that is great (and I do agree it's good to have that all in this thread even). Personally I always look for the most simple and straightforward solutions to my problems

[DocAElstein]

I don’t disagree, Excel and VBA lets you do things in such a lot of different ways, at least in something like this, so you are totally spoilt for choice.
Also what is simplest for any particular person is a bit personal choice, or depend on how you see and perceive things, at the time, etc. etc. ... I like variations, of this, currently
Range("D1:D5") = "= A1 + B1 + C1"
, but it really is personal choice, probably just as I got interested in looking at the very basics of that recently. A while back I loved to death the Evaluate Range one liners. I still like them, a lot , but now we are really just good friends. After I got much more intimate with them, they seemed (much) less complex, and had no intriguing secrets anymore, :( , as relationships sometimes go. We see eachother still, but now its a more plutonic relationship
Ask me next month and I will have another favourite probably,