Need to return value from a function in vba

Question

I am trying to return a value from a function but is not happening.

Public sub test()
  x = 2
  msgbox number(x)
Exit Sub

Public Function number(num)
  if len(num) = 1 then
     num = "0" + num
  End if
End Function

The function is returning a null value.

Rory · Accepted Answer · 2017-07-10 11:16:16Z

4

You need to assign the result to the name of the function, and concatenate the 0, like this:

Public Function number(num) As String
  if len(num) = 1 then
     number = "0" & num
  Else
     number = num
  End if
End Function

though it would seem easier to just use Format(num, "00")

answered Jul 10, 2017 at 11:16

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, it worked for me This was just an example for what i wanted to code as the original code has loads of confidential data.

ainwood · Accepted Answer · 2017-07-10 11:15:23Z

2

Public function number(num as string) as string
If len(num) = 1 then
    Number ="0" & num
Else 
    Number = num
End if
End function

You need to have code in the function that sets a value to the name of the function - the return value.

answered Jul 10, 2017 at 11:15

ainwood

1,0487 silver badges20 bronze badges