Update stamp for change to a range of cells in a row vs single cell

[Hollywood22]

Hi all,

VBA newbie, hoping this is a quick edit. I found the following function online to create a timestamp function, and it works great for a single target cell. Until my corp allows the use of the most recent build with a built in changelog which could be a year or more, I am hoping someone can help modify the simple code below to:

1 - Act on a range of cells, i.e a1:z1
2 - If not too difficult add the user initials who did the change.

----------------------------------------------------------------------------------

Function MyTimestamp(Reference As Range)

If Reference.Value <> "" Then

MyTimestamp = Format(Now, "dd-mm-yyyy hh:mm:ss")

Else

MyTimestamp = ""

End If

End Function

-----------------------------------------------------------------------------------

Thanks in advance

 

[6StringJazzer]

How are you using this function? A function returns a value; in this case it's a single value. Are you using this function in a worksheet cell, or are you calling it from another VBA Sub and doing something with it there? More context will be helpful.

Also, it returns a date/time formatted into a String. It will be more useful to just return the actual date/time value and format as desired in the worksheet.

Where do you want the initials to go?

 

[Hollywood22]

Thanks for the reply. As noted, I found this through a google search so using it as is. Fully appreciate your points w.r.t. date format and the outcome of a function. Any other format is perfectly fine to me, I simply have thousands of rows on about 7 different sized tables so making individual ranges in the VBA as i've seen in other places is just too onerous

What Id like to use this for is to populate a cell in a table of data. The table itself is 15-30 rows of various data, mostly financial and before allowing other users to enter data, Id like a changelog of sorts such that if something breaks I can at least narrow my search based on which row of data was updated and when (by whom is nice but not absolutely needed)

So what id like is a function that i can apply to a range of cells i.e. A1:Z1 and populate cell AA1 with the update stamp, vs just looking at a single cell. I know the latest build has an actual change log ability but unfortunately my corporation wont allow it to be used until vetted which is likely a year away.

Thanks again for the time

 

[6StringJazzer]

Thanks for the reply. As noted, I found this through a google search so using it as is.

What I meant was how are you calling it. Read my whole first paragraph to understand what the possibilities might be.

 

[Hollywood22]

I am using it in a worksheet cell. My table is columns A to Q, so in order to check if any cell in A1:Q1 has been updated I would like to use this function in cell R1. Unfortunately I can only look at one of those cells in A1 to Q1 though with the function as is.

Currently in cell R1 i can use =Mytimestamp(A1) , and then cell T1 = Mytimestamp(B1) and so on but that is a lot of inefficiency obviously. something to the effect of =mytimestamp(A1:Q1) would be enough.

 

[6StringJazzer]

You can change the function to return an array, although I recommend that you update your profile to show your Excel version since that may matter in this case.

Note that I added "A" to the end of the function name. You can remove it if you want to continue to use the old name.

Function MyTimestampA(Reference As Range) As Date()

   Dim R As Long, C As Long
   Dim Result() As Date

   ReDim Result(1 To Reference.Rows.Count, 1 To Reference.Columns.Count)

   For R = 1 To Reference.Rows.Count
      For C = 1 To Reference.Columns.Count
         Result(R, C) = Now
      Next C
   Next R
  
   MyTimestampA = Result
  
End Function

$scratch.xlsm
	B	C	D	E
4	03-10-2023 22:37:58	03-10-2023 22:37:58	03-10-2023 22:37:58	03-10-2023 22:37:58
Highlight word

Cell Formulas
Range	Formula
B4:E4	B4	=MyTimestampA(B4:E4)
Dynamic array formulas.

 

[6StringJazzer]

I am re-reading your posts. It appears that you want to put this only in blank cells. You can't do that in a worksheet formula, because it would require you to skip non-blank cells. The way my code works is to transfer an array onto the worksheet. That will result in a SPILL error if there are any non-blank cells in the range.

You will have to do this with a macro that you explicitly call, not a formula in a cell. If you want to do that I can modify the code above.