Function to put Now value in Cell not working

[NessPJ]

Hello all,

I made this function in VBA, but its not working. It gives me a "Application defined or object-defined error."

Sub Mysub()

Sheets("Parameters").Activate
TIMESTAMP (ActiveWorkbook.Sheets("Parameters").Range(B8).Value)

End

Private Function TIMESTAMP(TargetCell As Range)

TargetCell = Format(Now, "dd-mm-yy HH:mm:ss")

End Function

Changing TargetCell As String does not work either.

Any ideas what i'm doing wrong?

 

[Joe4]

You are missing the word "Sub" after "End" and double quotes around "B8".

But I am not sure why you created a function to do something that simple. Can't you just do:

Sub Mysub()
    Sheets("Parameters").Activate
    Range("B8").Value = Format(Now, "dd-mm-yy HH:mm:ss")
End Sub

 

[NessPJ]

But I am not sure why you created a function to do something that simple.

Well thats actually because i use it a lot more during the course of my entire routine. Hence why i figured it would look better as a Function.

Right now i have this but it still won't work sadly:

Private Sub Start()

TIMESTAMP (Sheets("Parameters").Range("B8").Value)

End Sub

Private Function TIMESTAMP(TargetCell As Range)

TargetCell = Format(Now, "dd-mm-yy HH:mm:ss")

End Function

 

[Joe4]

That is not how you would typical right a function. A function is like any other Excel function, it typically returns a value. So you usually set something equal to it (i.e. a range).
In the function, near the end you need to set the function name equal to whatever value you wanted to return.
A procedure ("Sub") is what you use when you want it to apply actions to something.

If you really wanted to use a Function for this, it would look like this:

Private Sub Start()
    Sheets("Parameters").Range("B8").Value = TIMESTAMP
End Sub

Private Function TIMESTAMP()
    TIMESTAMP = Format(Now, "dd-mm-yy HH:mm:ss")
End Function

As you can see, there is very little to be gained by using a Function for code that you can write with one short line, like I showed in my previous post.
You typically see Functions used when it is a more complex calculation that will be used multiple times.

 

[NessPJ]

As you can see, there is very little to be gained by using a Function for code that you can write with one short line, like I showed in my previous post.
You typically see Functions used when it is a more complex calculation that will be used multiple times.

I agree, in this case it is basically the same as setting the Now value everytime i need it for the specified cells.
I thought a function could be used like this (as i showed in my previous example), but i guess not.
Thanks for explaining!

 

[Joe4]

You are welcome.

Just think of it this way:
- Functions export values. So anytime you want to get just a value (you can feed in inputs, and usually do), you would use a function.
- Procedures (Sub) perform actions, like updating a range of cells, saving a file, etc. (procedures can also take in inputs, but often don't).