LEN function use in VBA

[compyr]

Hello all!

Can anyone explain the use of the LEN function in VBA code more in depth then the Help screens in Excel providing examples? I need to be able to create text files that have fixed lengths and am finding Help to be of little help. Or if anyone could point me in the direction of reference material pertaining to this topic that would be great also.

Thanks!

compyr

 

[Cbrine]

Compyr,


The LEN() function is pretty simple. It returns the lenght of a string.

Dim sTest as string

sTest = "Test"

Msgbox len(sTest)

The messagebox will return 4, which is the number of characters in sTest.

HTH.
Cal

Other resources

www.cpearson.com
www.xldennis.com
j-walk.com/ss/

 

[a7n9]

Hi,
If you check VBA help for len, it should give all the details, BTW I'm using Office 2003. I'll just copy that here:

Len Function


Returns a Long containing the number of characters in a string or the number of bytes required to store a variable.

Syntax

Len(string | varname)

The Len function syntax has these parts:

Part Description
string Any valid string expression. If string contains Null, Null is returned.
Varname Any valid variable name. If varname contains Null, Null is returned. If varname is a Variant, Len treats it the same as a String and always returns the number of characters it contains.

Remarks

One (and only one) of the two possible arguments must be specified. With user-defined types, Len returns the size as it will be written to the file.

Len Function Example
The first example uses Len to return the number of characters in a string or the number of bytes required to store a variable. The Type...End Type block defining CustomerRecord must be preceded by the keyword Private if it appears in a class module. In a standard module, a Type statement can be Public.

Type CustomerRecord ' Define user-defined type.
ID As Integer ' Place this definition in a
Name As String * 10 ' standard module.
Address As String * 30
End Type

Dim Customer As CustomerRecord ' Declare variables.
Dim MyInt As Integer, MyCur As Currency
Dim MyString, MyLen
MyString = "Hello World" ' Initialize variable.
MyLen = Len(MyInt) ' Returns 2.
MyLen = Len(Customer) ' Returns 42.
MyLen = Len(MyString) ' Returns 11.
MyLen = Len(MyCur) ' Returns 8.

 

[compyr]

Can I get an example of something like this:

Number Name Date

123456 Joe Doe 013105
789 Moe Joe 012805
1024 Boe Moe 020105


where each column has to be a fixed length as follows:

Number Field = 19
Name Field = 25
Date Field = 6

I hope I'm making myself clear Kinda new at this VBA stuff!

Thanks for the preceding help on the LEN function. I will re-read the help and try to add a wrinkle to my brain!

compyr

In retrospect, I may be asking about the wrong function for what I want to do.

 

[a7n9]

If you want to keep the legth fixed then you can use Data>Validation>Text length, you won't need VBA then.

BUt if you want to use VBA then you can use something like this:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
<SPAN style="color:#00007F">Dim</SPAN> myrange <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
<SPAN style="color:#007F00">'define the range where you want to check the lengths on</SPAN>
<SPAN style="color:#00007F">Set</SPAN> myrange = Range("A1:A10")
<SPAN style="color:#007F00">'will loop thru all the cells in that range</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> myrange
    <SPAN style="color:#00007F">If</SPAN> Len(c) <= 10 <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Unacceptable data format at cell number" & c.Address, vbCritical
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
MsgBox "Data is good", vbInformation
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[compyr]

a7n9

Will the Data>Validation>Text Length force my first column to 19 places or just cut off everything that is more then 19 places?

I need to save the file to a text file with fixed length fields so that fields 1,2,3 are a specific length regardles of data length in the column as the data can vary.

As I understand the VBA code it will just tell me if my data meets my requirements. I seem to recall somewhere in the forums something such as this:

F1=Str(Str-LEN,19) *This is probably horribly wrong.

Since I currently lack the correct vocabulary I will try to express it plainly:

Field 1 is = to the length of the string plus however many empty spaces needed to bring the total field length to 19.

As I said before, I may be trotting down the wrong path. Sorry for any confusion on my part! Thanks for the reply!

compyr