0

I'm starting with VBA and I'm very upset to discover I have fallen at the first hurdle in this book I am following.

I should type the following

Function Hello() As String
    Hello = "Greetings"
End Function

This all works fine but next I am supposed to change the text and see the function change on the Excel spreadsheet. Unfortunately I can't get this to work. Does anyone know why?

Screenshot

I've saved the document as an Excel Macro-enabled workbook and tried opening and closing.

Community
  • 1
  • 1
NIcolaHearn
  • 29
  • 7
  • The function won't recalculate unless you re-enter it. (Just select the cell and press f2 then Enter) – Rory Nov 11 '15 at 09:44
  • Hi Rory, thanks I have tried the F2 thing and retyped. In fact I closed and reopened the whole project. Also, nothing is working now. I just get #NAME? in the cell :( – NIcolaHearn Nov 11 '15 at 09:50
  • If you get a `#NAME?` error, you have macros disabled, or you changed the name of the function, or you moved it into a class module, or the module has the same name as the function. – Rory Nov 11 '15 at 09:53

5 Answers5

4

You seem to have this function in the code-file for thisWorkbook, but it should be in a module. Add a module to your project, place your code there and make the function Public. See also: How to Call VBA Function from Excel Cells?

Community
  • 1
  • 1
steenbergh
  • 1,642
  • 3
  • 22
  • 40
2

Normally a Function should be in a Module, not in ThisWorkbook. You store Event handlers in the ThisWorkbook or a Sheet module.

It doesn't recalculate because it doesn't have a Range input, since the function doesn’t have any arguments, it is treated constant output and hence updating the function doesn’t update the cell value.

But if you modify it to accept a Range input, and if there are any changes to the input range, it will recalculate.

PatricK
  • 6,375
  • 1
  • 21
  • 25
0

declare your function right

Function Hello(str As String) As String

then use something like

cells(1,1).value=str

function does the job for you but first you must call the function by parameter like this

cells(1,1).value=Hello("How are you")

the result will be that in cell 1,1 will "How are you" be written. but from this on I am not sure what are you trying to accomplish. If you need funct. to write in specific cell all the time you should use something like

 Funtion Hello(row as integer, column as integer) as string
    cells(row,column).value=inputbox("give me the input")
    end function

then to use this you write into code

result=Hello(1,2)

this example works

Function Area(row As Integer, column As Integer) As String
Cells(row, column).Value = InputBox("give something here")
End Function

Sub my()
    result = Area(2, 2)
    End Sub

run my()

Lance
  • 203
  • 2
  • 15
0

Ok I worked it out, I had written the function into the file "This Workbook" instead of into the Module I created...

I knew it would be something simple! All working now.

NIcolaHearn
  • 29
  • 7
0
Function Hello() As String
Application.Volatile
Hello = "Greetings"
End Function
Maux
  • 1
  • 2