How to solve error on VBA function error?

Question

I don,t know how to fix the error on my VBA, for a empety cell, to return empety value.

In my table, i have some values, that i whant to replace. Others values are correct and if is not mentioned on Case returns the value of the cell (strSize). But some cells are empety, and if is empety have to remain empety.

All values are returned according the case below, but when comes with a empety cell, returns #Error

Public Function fncRetornaSize(strSize As String) As String
Select Case strSize
   Case "": fncRetornaSize = ""
   Case "3,5": fncRetornaSize = "36"
   Case "4": fncRetornaSize = "36,5"
   Case "4,5": fncRetornaSize = "37"
   Case "5": fncRetornaSize = "38"
   Case "5,5": fncRetornaSize = "38,5"
   Case "6": fncRetornaSize = "39"
   Case "6,5": fncRetornaSize = "40"
   Case "7": fncRetornaSize = "40,5"
   Case "7,5": fncRetornaSize = "41"
   Case "8": fncRetornaSize = "42"
   Case "8,5": fncRetornaSize = "42,5"
   Case "9": fncRetornaSize = "43"
   Case "9,5": fncRetornaSize = "44"
   Case "10": fncRetornaSize = "44,5"
   Case "10,5": fncRetornaSize = "45"
   Case "11": fncRetornaSize = "46"
   Case "11,5": fncRetornaSize = "46,5"
   Case Else
    fncRetornaSize = strSize
End Select
End Function

Can someone help me?

Erik A · Accepted Answer · 2018-10-29 13:19:08Z

1

It's not the VBA function causing the error, but the way you implement it. A string can never be Null, and your function takes a string as a parameter. Thus if the argument is null, it causes an error.

An easy fix is to make the function take a variant as the argument:

Public Function fncRetornaSize(strSize As Variant) As Variant

You can leave the rest of your function unchanged.

Alternatively, you can use IIF(IsNull( to avoid the error:

IIF(IsNull(SomeVariable), Null, fncRetornaSize(SomeVariable))

answered Oct 29, 2018 at 13:19

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

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

Collectives™ on Stack Overflow

How to solve error on VBA function error?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related