Excel macro to change the field data type string to long

Question

I have a function which returns value as string.

Function Trimcell(cellvalue As varnant) As String
    Trimcell = Replace(CStr(cellvalue), " ", "")
End Function

I want to change the data type string to long . Any help.

What have u tried? All it should take is to change the return type of the function. — dotNET, Apr 21 '17 at 03:33
I have a function .. Function Trimcell(cellvalue as varnant) as string. Trimcell = Replace (CStr(cellvalue)," ","") End function — Smruti R Mohanty, Apr 21 '17 at 03:46
I need the function should return Long instead of String. If I change string to long in the return data type in the function, throwing error. — Smruti R Mohanty, Apr 21 '17 at 03:50

score 0 · Answer 1 · answered Apr 21 '17 at 05:42

0

Change your function to this:

Function Trimcell(cellvalue As varnant) As Long
    Trimcell = Val(Replace(CStr(cellvalue), " ", ""))
End Function

answered Apr 21 '17 at 05:42

dotNET

33,414
24
162
251

score 0 · Answer 2 · answered Apr 21 '17 at 05:51

You have a spelling error - varnant instead of Variant.

A better option than using Replace is to use Val which removes blanks, tabs, and linefeed characters from a string and returns a Double. It also stops reading the string at the first non-numeric character apart from period (.) which it recognises as the decimal separator.

As you have declared cellvalue As Variant you shouldn't need CStr either.

Function Trimcell2(cellvalue As Variant) As Long

    Trimcell = Val(cellvalue)
End Function

Excel macro to change the field data type string to long

2 Answers2