Tab In VBA Excel

[d_raghu9]

Hi there

I am working on VBA and i am struck at TAB

i have a function which takes 3 arguments

writeto_file(filename as String, Sep as String, flag as boolean)

i am able to send the first and third arguments right but for the second argument i would like to send tab seperator as argument which i am not able to :-(


this "sep" argument is used as a delimeter when writing to a file.

I am able to pass "," or "|" as arguments but i am not able to pass tab and i dont know how to pass tab.

Any help would be greately appreciated.

Thank You
Raghu

[acw]

Raghu

Try using the ascii code. If you are using it in VBA then


chr(9)


If in a spreadsheet then

char(9)



Tony

[d_raghu9]

thank you for the reply

but unfortunately i did not get what you are saying.

could you put it in as an example please

many thanks

[acw]

Hi

Assuming that you are running this from a sub then


myvar = writeto_file("NameOfFile", chr(9), TRUE)


As it is a function, it should return something so you can check what is in myvar at completion.


Tony

[TonyJollans]

If this is all within VBA, use vbTab ...
writeto_file filename, vbTab, flag
A better way to do something like this might be to use an enumeration ...
Enum mySeparator
mysepTab
mySepComma
mysepPipe
mySepSpace
mySepOther
End Enum

Function TestSep(argFileName As String, _
argSep As mySeparator, _
argFlag As Boolean, _
Optional argSepChar As String)

Dim Separator As String

Select Case argSep
Case mysepTab: Separator = vbTab
Case mySepComma: Separator = ","
Case mysepPipe: Separator = "|"
Case mySepSpace: Separator = " "
Case mysepother:
If IsMissing(argSepChar) Then
' Use a defualt character
' Or issue an error message
Else
Separator = argSepChar
End If
End Select

' Rest of your code

End Function

[Cyberdude]

Tony, enumeration is a new concept to me. I don't quite grasp what it's used for. What is the "Enum" statement doing? In your example, you don't seem to be assigning any value to the identifiers in the Enum statement. Can you expand on this a tad? :dunno

[TonyJollans]

Hi Sid,

Enumerations are really a special type of constant. They let you define your own constants like the built in ones - like wdCharacter, wdForward, etc. They are useful in Function definitions because the editor provides prompts just like it does the built-in ones. Used sensibly they should make code easier to understand and work with.

They are Long data types and you can assign any value you like to each but, if you don't, VBA will assign default values (0,1,2,3, ...). In this case (as in general) I don't care what values the constants have so I just let VBA handle the complication.