1 Attachment(s)
Put formula in a cell with coding
We are almost there.
We need to make some construction using that WorkRange.Address within VBA that allows it to be used within the main formula string: We cannot just insert WorkRange.Address any more as we cannot just insert WorkRange, as it will take WorkRange.Address as just that actual text, as if we had given excel some text, like
Let ActiveCell = "WorkRange.Address" ___ https://i.postimg.cc/sBYcwhW3/Active...ge-Address.jpg
, but we don't want that, we want the short text notation used in an Excel spreadsheet for a range, (that of the cell in the Excel spreadsheet column letter and row number format), that is returned from WorkRange.Address when it is used in VBA coding.
The VBA coding syntax for doing that sort of thing is to insert it into the main formula string at the appropriate position in a special way like this
_______________ " & WorkRange.Address & "
What that does, is do the VBA business on WorkRange.Address and then stick the result into the literal text
, pseudo like, if , for example, WorkRange was the second cell in a spreadsheet, ( Set WorkRange = Range("B1") ) then:
" & WorkRange.Address & " will be turned by VBA into $B$1 , and that $B$1 will be inserted into the text.
We are using it in a formula, but the idea is easier to see in a simple text, thus
Code:
Sub TextIn()
Dim Rng As Range
Set Rng = Range("A1")
Let Range("D10") = "The first cell has an address of " & Rng.Address & ", honest"
End Sub
Rub that macro and then In the cell, D10 you will see
The first cell has an address of $A$1, honest
https://i.postimg.cc/94Cs4tTT/Range-A1.jpg
In this post we will now do some examples of how to put the basic formula into a cell. Some will be relevant to this Thread, some won't necessarily, - at the end of the day we are Human here and are not so keen to encourage the robots to take over with their well ordered cold logic!
From now on I will use this simple test range, which is in both worksheets in the uploaded file. ( the letter f in cell E5 is the last filled cell in column E)
https://i.postimg.cc/Kz4bFvvc/Simple-test-range.jpg https://i.postimg.cc/gX9QHpZ1/Simple-test-range.jpg
https://i.postimg.cc/7YKpPrcj/Simple-test-range.jpg
_____ Workbook: SimpleCodingPutFormulasInCell.xls ( Using Excel 2007 32 bit )
| Row\Col |
E |
|---|
| 1 |
|
| 2 |
a |
| 3 |
b |
| 4 |
|
| 5 |
f |
| 6 |
|
Often, or at least initially, I will tend to put the formula somewhere to the left of the range, for no particular reason other than a guess that it could bee convenient there. Of course, if I am passing a work range, then that will determine where things are relatively speaking.
Simplest Codings Put formula in
I assume here that I want my formula in column D, and I have selected the cell in column D that I want the formula in.
Here are three coding offerings
Code:
Sub PutFormulaIn_1()
Let ActiveCell = "=LOOKUP(3,1/(" & ActiveCell.Offset(0, 1).EntireColumn.Address & "<>"""")," & ActiveCell.Offset(0, 1).EntireColumn.Address & ")"
End Sub
Sub PutFormulaIn_2a()
Call PutFormulaIn_2b(ActiveCell.Offset(0, 1).EntireColumn)
End Sub
Sub PutFormulaIn_2b(ByVal WorkRange As Range)
Let ActiveCell = "=LOOKUP(3,1/(" & WorkRange.Address & "<>"""")," & WorkRange.Address & ")"
End Sub
Sub PutFormulaIn_3a()
Call PutFormulaIn_3b(ActiveCell.Offset(0, 1).EntireColumn)
End Sub
Function PutFormulaIn_3b(ByVal WorkRange As Range)
Let ActiveCell = "=LOOKUP(3,1/(" & WorkRange.Address & "<>"""")," & WorkRange.Address & ")"
End Function
(The coding is also in the uploaded file, SimpleCodingPutFormulasInCell.xls )
Run any of those codings, ( by running either of these:
Sub PutFormulaIn_1()
Sub PutFormulaIn_2a()
Sub PutFormulaIn_3a() __
), and the result will be the same:
In the selected cell, if it is in column D you will see f
In the formula bar you will see =LOOKUP(3,1/($E:$E<>""),$E:$E)
https://i.postimg.cc/BnZzPSR3/Result...ist-Coding.jpg
https://i.postimg.cc/BnZzPSR3/Result...ist-Coding.jpg
Strictly speaking, none of those solutions is a UDF solution.
The last function could be considered a UDF, if I called it from the spreadsheet by typing in any cell something like
=PutFormulaIn_3b(E:E)
If you try doing it just like that it will not work. That is where the typical answers come from, those answers of like ….
…. A UDF cannot add a formula to a cell. …..
In the next post I will show how that can be done. But before that I will throw in a solution of a different sort, just in case it might be worth considering….
Simple alternative way to do it Event Coding
This solution does not really fit in to this Thread. But we are not prejudice here at excel fox.
Event Coding
VBA can be thought very approximately as the coding that actually makes Excel or Microsoft Office work. Very graciously Microsoft let us tap into it, and so we can do an amazing number of things.
(Using Microsoft Office without the VBA you already have, can be compared with having some all purpose vehicle parked outside your door, that can do just about everything that any vehicle can and much more like flying to Mars, submerging to the centre of the earth, digging out a trench to make a river to save a Forest for younger generations etc., etc., but you just use it once a week for the same short journey to go to the local shops )
Microsoft do not let us do everything, understandably perhaps, or else we would make everything ourselves, or more likely make a mess.
When things happen, for example, a cell value gets changed, various things happen, a chain of events occur, as it were. We can tap into those as well.
We will do that in worksheet, Sheet2
We go to the second worksheet, Sheet2, right click on the Sheet2 tab at the bottom, select View Code , that gets us the VB Editor we might be familiar with, but the biggest empty window showing is a special code module belonging to the worksheet. There is one such code module for each worksheet.
https://i.postimg.cc/F15RRvcj/Mess-a...eet-Change.jpg
https://i.postimg.cc/5Qg17Zs0/Mess-a...eet-Change.jpg
Now mess around selecting things from the two drop downs you see at the top of the worksheet code module until you get the Worksheet_Change sub routine showing
It is empty initially. It is part of the chain events that happen when something is changed in a worksheet. Microsoft do not want to show us all that goes on, but we can see this sub routine that is always done, or rather run, even though it does nothing as it is empty. But we can add coding to it, so that something else is done when there is a change in the worksheet.
The Target variable is given to us and filled with the range object of what was changed. So that is handy, me thinks…..
How about this:
I will write a bit of coding to put in the empty routine, that checks what range got changed. If it was a single cell, and say, a small z was typed in, then I will put the formula in that cell, the formula that gives me the last value in the column to the right of that cell
Here is the coding
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Value2 = "z" Then
Target.Select ' After I hit Enter I will be in the next cell down, so this puts me back in the cell I typed z in
Call PutFormulaIn_3b(ActiveCell.Offset(0, 1).EntireColumn) '
Else
' do nothing
End If
End Sub
The coding is also in the uploaded file, SimpleCodingPutFormulasInCell.xls
So just to recap, clarify what you do and what goes on in Sheet2:
Select a cell (in Sheet2), to the left of the column you want to get the last value of, for example D4.
Type a small z in it, and hit Enter https://i.postimg.cc/K1GWSsYL/type-z-in-D4.jpg
The formula we want then gets put in that cell, ( the z is overwritten with the formula )
https://i.postimg.cc/L8LFPcFK/z-over...th-formula.jpg
For convenience I use the function PutFormulaIn_3b . I could just as well have put all the coding in , or indeed used any of the other routines .
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Value2 = "z" Then
Target.Select ' After I hit Enter I will be in the next cell down, so this puts me back in the cell I typed z in
' Call PutFormulaIn_3b(ActiveCell.Offset(0, 1).EntireColumn) '
' Call PutFormulaIn_1
' Call PutFormulaIn_2a
' Call PutFormulaIn_2b(ActiveCell.Offset(0, 1).EntireColumn)
Call PutFormulaIn_3a
Else
' do nothing
End If
End Sub
1 Attachment(s)
Doing the impossible, again
A UDF to change the contents of any cell, in our case to put the formula in to get the last value in a column
Here, we develop a UDF to put our formula in any cell of our choosing, ( well almost any cell, in any case a cell other than the one we use the UDF formula in – which is regarded as impossible)
We will use the same test range in column E, as in the previous posts, in a third worksheet which I will name Marnhullman
https://i.postimg.cc/KYs7c9cD/Ws-Marnhullman.jpg
https://i.postimg.cc/QB1K0JTF/Ws-Marnhullman.jpg
This is an example coding, and it is in th uploaded file. (You need all that coding)
Code:
Function PutFormulaIn_4a(rngInput As Range) As String
Rem 1 ' The conventional use of a UDF to return a value in the cell that it is in, in a similar way to how a formula or in built function does
Let PutFormulaIn_4a = "last in column " & Split(rngInput.Cells.Item(1).Address, "$")(1) ' rngInput.Cells.Item(1).Address will give us a simple string which will have a form like $E$19 If isplit that using $ as the delimeter, I end up with three elements indicie 0 1 2 (0)is an empty string, (1) is E , (2) will be the number
Rem 2 ' Unconventional use of the general thing we might call a UDF
Worksheets("Marnhullman").Evaluate Name:="=PutFormulaIn_4b(" & rngInput.Address & "," & ActiveCell.Address & ")" ' The Evaluate(" ") string, that is to say the Name:=" " bit in this case, uses Excel spreadsheet syntax, not VBA syntax, and we can get that spreadsheet A1 type address syntax from the .Address stuff in VBA
End Function
Sub PutFormulaIn_4b(ByVal rngInput As Range, ActivCel As Range)
Dim WorkRange As Range
Set WorkRange = rngInput.Columns(1).EntireColumn
Let ActivCel.Offset(1, 0).Value = "=LOOKUP(3,1/(" & WorkRange.Address & "<>"""")," & WorkRange.Address & ")"
End Sub
The first procedure, Function PutFormulaIn_4a, is what we might call the UDF.
If, for example you were to type in cell D4
=PutFormulaIn_4a(E3)
https://i.postimg.cc/nL4vtHjx/Use-UDF-in-D4.jpg
https://i.postimg.cc/xkdkvV3p/Use-UDF-in-D4.jpg
, then we will get the following formula written in a cell
=LOOKUP(3;1/($E:$E<>"");$E:$E)
https://i.postimg.cc/KYPnyZkS/Formula-put-in-D5.jpg
https://i.postimg.cc/xkdkvV3t/Formula-put-in-D5.jpg
This solution probably comes closes to answering the very initial post over at excelforum.com
However, there are just a few deviations/ features to discuss , as well as a general explanation of what is going on.
A couple of non perfections:
_ This does not work to put the formula in the same cell as where you use the UDF. But it does work to put the formula in any other cell, (which is the thing regarded by most as impossible) . I have arranged that it puts the formula in the next cell down. That is what the
.Offset(1, 0)
, in the last code line is about – it is offset by 1 row down and 0 column offset. So you can change that as you wish to put the formula somewhere else, but avoid no offset, as that can cause a nasty crash. (I suspect that with no offset it may be trying to do two things simultaneously in a cell, which causes the nasty crash)
_ Initially the formula always shows 0. I suspect that may be because for some reason it has not yet been done/ evaluated/calculated. But as soon as you do most other things in the worksheet, or save the file, or probably do various other things, then the worksheet seems to get refreshed, and the formula gets done. ( After that it will update as any formula would if you change any cell values that it is working on )
General Explanation of what is going on
If we consider the first part of Function PutFormulaIn_4a ( section Rem 1 ) , along with the example use I did above, then that is what a typical UDF is all about: What is going on there is that, some of the information you pass to it, (when you call it from a cell ), is used to put something in the cell you wrote in. In this example I have arranged that it puts some text in telling you which column you are trying to get the last value from.
Rem 2 section and the other procedure, Sub PutFormulaIn_4b is part of the trick to get a formula out in a cell from when using a UDF. ( Whether or not Rem 2 section and the other procedure, Sub PutFormulaIn_4b is part of what we might refer to as the UDF named PutFormulaIn_4a is open to debate. That is perhaps convenient as the Experts claiming this cannot be done, can say the UDF is not changing the contents of any cell if they regard only Rem 1 section as a UDF )
So Rem 2 section and the other procedure, Sub PutFormulaIn_4b gets the job done.
Nobody is too sure what is going on here. But we know that in general the
VBA Evaluate(" ")
Or
VBA Evaluate Name:=" "
( those are just 2 different syntaxes of the same thing )
, is something that allows us to do things in VBA that we do in a spreadsheet cell. In other words, you can write in between the quotes pair what you can write in a cell. VBA then tries to give you the result as it would in the cell. So using it in the way I am here, seems to set off an independent second procedure. As it is somehow independent, it seems to work similarly to how any procedure I run normally from within VBA would work. So I do not have the restrictions that I have with the typical standard UDF.
That is basically it.
One small point is that we do have 2 VBA Evaluate things, a general one, and one for every worksheet. I have sometimes found that if I have a lot of these sorts of UDFs in different worksheets, then using one of them can inadvertently set the others off in the wrong worksheet. Restricting things to a worksheet, ( as I am doing in Rem 2 with Worksheets("Marnhullman"). ) seems to cure that problem. If you miss out the Worksheets("Marnhullman"). , then you default back the general Evaluate, and it may be OK, and you remove the restriction to a particular worksheet.
An interesting observation is that in the second procedure, ActiveCell does not work as we might expect. ( That is why I had to pass over the address of the active cell in order to use it ). This observation supports some ideas about why this trick works: It is thought that Excel has lost its orientation a bit, when it runs the second procedure, which gives it the independence that helps make it work for our purpose. Now, surprisingly, ActiveCell is not a property of a worksheet, nor generally a property of Excel. It is a Windows property. So using Evaluate is doing spreadsheet things but somehow not directly linked to a worksheet. Hence we have interesting new possibilities using it. The VBA Evaluate function is often regarded by smart people as one of the most powerful functions.
Alan
