Function Val("1") not working

[mtheriault2000]

Simple code to transfer a string number to a number as long is not working.

Dim v as long
v= val("12")

 

[cstimart]

Here's what you want....

Dim v As Long
v = CLng("12")

 

[mikerickson]

That code works for me. What does "not working" look like? (I note that the spacing and capitalization in the OP indicate that the code was not copy pasted from a module. If it was copy/pasted... exactly where was it copied from?)

On the other hand TypeName(Val("12")) returns Double. So it may be the "automatic" conversion from Double to Long that is having trouble.

Does

Dim v As Long
v = CLng(Val("12"))

work?

 

[mikerickson]

Cstimart,
One big advantage of Val over CLng is that Val will accept any string without erroring

Val("cat") = 0
CLng("cat") is a type mismatch error.

This is useful when working with TextBoxes "I won't crash even if the silly user doesn't know how to enter a number."

 

[mtheriault2000]

Here's what you want....

Dim v As Long
v = CLng("12")

Yes It working Thanks

I have tried dim v as integer and i got the same error

Sub testval()
Dim v As Integer
v = Val("12")
End Sub

Error translation is:
French: "Erreur de compilation: Tableau attendu
English: Compilation error: Expected table

Any idea why I got this error?

 

[mtheriault2000]

Does

Dim v As Long
v = CLng(Val("12"))

work?[/QUOTE]

No. See next post for the error message

Strange

Martin

 

[xenou]

A strange case.
What if you dim v as Long?


Sub testval()
Dim v As Long
v = Val("12")
End Sub

 

[xenou]

You may be hooked into some unusual implementation of VBA. Or some conflict in a code library is over-riding the usual vba function.

Try:

Sub testval()
Dim v As Integer
v = VBA.Val("12")
End Sub

 

[Marcelo Branco]

Xenou,

Just curious. I had never seen this

v = VBA.Val("12")

Could you please explain?

M.

 

[xenou]

It's an old answer to a common problem. For some strange reason, Excel members are always posting threads where the Mid(), Left(), and Right() functions no longer work. And for some similar strange reason, VBA.Mid(), VBA.Left(), and VBA.Right() cures it.

In some cases someone has actually written their own Left() or Right() functions (!). But otherwise, there must be some way in which such functions are occasionally accessed from other code libraries (either addins, or other xll's or dll's that Excel is accessing in the current environment).

It's possible that you could also fix these errors by going into to "references" and making sure the VBA For Applications is suitably high on the priority list. In fact, all vba functions could be prefixed with their class library name:

VBA.Val()
VBA.Mid()
VBA.UCase()
VBA.CLng()
VBA.Round()

and so one...

By looking in the object browser (F2 in the VBE) you can see the VBA library more directly, and all of the functions it exposes.

That's about all I know, and I'm trying to be suitably vague as I'm not really the kind to have technical answers at my fingertips.

Cheers!
ξ