CODEWITHSUNDEEP
ms-accessvba
user1504957
user1504957

Reputation: 13

How to solve error on VBA function error?

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?

Upvotes: 1

Views: 24

Answers (1)

Erik A
Erik A

Reputation: 32642

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))

Upvotes: 1

Related Questions