Changing cell value through VBA

[Krevis]

I have empty worksheet where I created a module with following

Function ChangeValue()
Range("B5").Value = "Hello"
End Function

It was my belief that entering =ChangeValue() on A1 would be enough to change value on B5 to "Hello"
But instead cell focus simply move to B1 and doesn't do anything further

Number of searches on google turned up different way to phrase Range("B5").Value
such as Cell(2,5).value =
but it also simply move the cell focus to A2

What are some other troubleshooting steps? What's the correct way to change the content of specific cell?

The reason for using function is that I was passing a parameter into a function on a different problem but got stuck with a same problem where specified cell content wouldn't change.

 

[Fluff]

A function called from a sheet cannot change another cell, in exactly the same way that a regular Xl function cannot change other cells.

 

[Joe4]

If you wanted to write it to a specific cell, you would use a Sub Procedure and not a Function, i.e.

Sub ChangeValue()
    Range("B5").Value = "Hello"
End Function

A function just takes input (optional) and produces output (like any native built-in Excel functions, such as SUM, LEFT, etc).
So that would look something like:

Function ChangeValue() as String
    ChangeValue = "Hello"
End Function

Then you would use that like any other Excel function.
So, in cell B5, you could enter the function:
=ChangeValue()
and you would get that value.

You can also call the functions in procedures. So if you wanted to run the function and return the value in cell B5, you could so this (in conjunction with the function above):

Sub MyTest
    Range("B5") = ChangeValue()
End Sub

 

[Krevis]

Thank you for the detailed answer. Lit me up like a light bulb ?!

I will recode it by calling the function that returns a value

 

[Joe4]

You are welcome!
Glad we could help.