Urgent:Trim, Len functions does not work well in VBA Code

[krisrajz]

Why do functions like Trim, Len etc., does not work in VBA modules?
Referece list were still valid.

Thank you.

 

[Richie(UK)]

Cross-posting

For reference, this query has also been posted here:

http://www.ozgrid.com/forum/showthread.php?p=105535#post105535

 

[Scott Huish]

Re: Urgent:Trim, Len functions does not work well in VBA Cod

They do work in VBA.
Can you please post a sample of what is giving you problems?

 

[krisrajz]

Re: Urgent:Trim, Len functions does not work well in VBA Cod

Call to those functions triggers invalid argument error.But I've checked the arguments (for datatype, value etc). There was nothing wrong.

RAJ

 

[Scott Huish]

Re: Urgent:Trim, Len functions does not work well in VBA Cod

Someone might be able to help you if you post the code that is giving you trouble and maybe an explanation of what you are trying to do.

 

[parry]

Re: Urgent:Trim, Len functions does not work well in VBA Cod

Hi as noted its hard to tell you what your doing wrong unless you post your code. See the VBA Help topic 'Using Microsoft Excel Worksheet Functions in Visual Basic' reagrding using functions.

Briefly, there are 2 types of functions available to you in code - those that are native to VBA and some Worksheet functions - see the VBA help topic 'List of Worksheet Functions Available to Visual Basic'.

As an example, if you look in the help under the worksheet functions available you will see that Len is not available while Trim is. Therefore if you try and use the Len worksheet function it will fail. However, if there is no worksheet function it often is because there is an equivalent native VBA function that does the same thing. Look in VBA help for the word Len and you will see there is a Len function. Sometimes, Excel has both - there is a worksheet function Trim as well as a VBA function Trim.

Heres an example using these versions. Note that when you refer to a cell reference you need to use Range rather than just the address...

Sub TestFunctions()
Dim WF_LEN As Integer, VBA_LEN As Integer
Dim WF_TRIM As String, VBA_TRIM As String
WF_LEN = Application.WorksheetFunction.Len(Range("a1")) 'This will produce an error saying this is not supported
VBA_LEN = Len(Range("b1")) 'this will work
MsgBox VBA_LEN
WF_TRIM = Application.WorksheetFunction.Trim(Range("a1")) 'this will work
VBA_TRIM = Trim(Range("b1")) 'this will work
Range("a1").Value = WF_TRIM
Range("b1").Value = VBA_TRIM

End Sub

 

[mattrindy]

I'm having a similar problem

worksheetfunction.LEN is unavailable in my module.

worksheetfunction.SUM works

worksheetfunction.COUNT works

another example:

Sub LEN()
msgbox worksheetfunction.Len(ActiveCell)
End Sub

this wont work. It doesn't even list LEN as a function in VBA.

 

[shg]

Len is a native VBA function.

MsgBox Len(ActiveCell.Value)