0

When I use pre-configured good code, functions in VBA return values. For example, this: 

Sub TestFunction() 'Test Function
  MsgBox CheckCell(1)
End Sub

Function CheckCell(CellValue) As Boolean
  If IsNumeric(CellValue) Then
    CheckCell = True
  Else
    CheckCell = False
  End If
End Function

Correctly returns a value of 'True' when run (or, depending on the input 'False', or if modified to be integers, then integers etc).

However, to me, identical looking code that I have written myself: 

Sub TestFunction() 'Test Function
MsgBox LastRowInOneColumn()
End Sub

Function LastRowInOneColumn() As Integer
'Find the last used row in Column A
    Dim LastRow As Integer
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        MsgBox LastRow
        Exit Function
    End With
End Function

Does not work, no matter how I configure it (Adding a return statement, modifying the inputs etc). Why? What is the difference in how I'm performing these two functions?

To answer some obvious things I've checked:

  1. There are a number of values in the A1 column (14 in my sample).

  2. The first message box in the function itself prompts with the correct number, but the value is not returned (Second message box has a value of 0).

  3. This seems to happen to all functions I write myself, even though they appear identical to numerous samples I've tried, so I understand I must be the one causing the error.
  4. I'm using VBA in Excel 2016.
Community
  • 1
  • 1
HDCerberus
  • 2,113
  • 4
  • 20
  • 36
  • 3
    You are not returning the value. `LastRowInOneColumn = .Cells(.Rows.Count, "A").End(xlUp).Row` – Scott Craner Sep 12 '17 at 21:56
  • I would also suggest you use `Long` instead of `Integer` – Scott Craner Sep 12 '17 at 21:57
  • I'm not sure I understand why this works, bit I now understand the return value needs to have the same name as the function for some reason? I don't understand why placing "Return LastRow" wasn't working. If you add your answer with a short explanation on why the name of the return value needs to be the same as the function, I'll happily mark it as accepted. – HDCerberus Sep 12 '17 at 22:00
  • 1
    `Return LastRow` is VB.Net syntax, not VBA. The VBA `Return` statement is used in conjunction with the VBA `GoSub` statement (see https://msdn.microsoft.com/VBA/Language-Reference-VBA/articles/gosubreturn-statement), and I strongly recommend that you don't use the `GoSub` statement!! – YowE3K Sep 12 '17 at 22:00

0 Answers0