opposite of trim function

[Luis939]

I know the trim function will take off any leading or trailing empty spaces, but is there a way of testing 2 strings to see if they connect, without using the trim function, so if i have string1_ and _string1, is there anyway of showing that they are equal without using the trim function?? thanks!

 

[sfvb]

Why don't you want to use the trim function?
You could still do a comparison without actually trimming any spaces off.

   Dim string1   As String
   Dim string2   As String
   
   string1 = "  sdf"
   string2 = " sdf "

   If Trim(string1) = Trim(string2) Then
      MsgBox "This messages appears"
   End If

   If string1 = string2 Then
      MsgBox "This message won't appear"
   End If

 

[Luis939]

well to be completely honest, im just checking that worksheet names are correct within a spreadsheet, so if i know that a worksheet is going to be called worksheet1, i want to look for it in a workbook, but i cant just look at every worksheet, i have to explicitely search for them by putting its proper name in the code.

So since some worksheet names have an extra space before or after its name, it wont be able to work becuase in my code I say the way it should be, without spaces

 

[ETID]

Maybe this....




If Trim(worksheet_name) = my_string Then do this else do that

 

[Luis939]

nah its more complicated than that, it probably cant be done, see, lets say i have 8 worksheets but i only want to work with 4, they are called "ws1", "ws2", "ws3", "ws4" i have my macro specifically go to that worksheet, so ill say
workingsheet = "ws1"
activeworkbook.worksheets("ws1&quot....

now if the worksheet is spelled "ws1 ", then ill get an error becuase it isn't "ws1", so is there perhaps a way of saying compare the worksheet name in the workbook-"ws1 " with the name in my program "ws1" without worrying about getting an error.

 

[sfvb]

You could use the worksheet index instead of the name:

Dim idx           As Integer
Dim ws            As Worksheet
Dim sheetToFind   As String
   
sheetToFind = "Sheet2"
idx = -1
For Each ws In Worksheets
   If Trim(ws.Name) = sheetToFind Then
      idx = ws.Index
   End If
Next ws

If idx = -1 Then
   MsgBox "The worksheet was not found"
Else
   Sheets(idx).Select
End If

 

[Zathras]

Sounds like you want something like this:
[blue]

Option Explicit

Sub test()
  MsgBox ("Sheet 'ws1' real name = """ + RealSheetName("ws1") + """")
End Sub

Function RealSheetName(ASheetName As String) As String
Dim wksh As Worksheet
  For Each wksh In Worksheets
    If Trim(wksh.Name) = Trim(ASheetName) Then
      RealSheetName = wksh.Name
    End If
  Next wksh
End Function

[/color]

 

[Luis939]

well i made an error in the code i put it should read

workingsheet = "ws1"
activeworkbook.worksheets(workingsheet)....

but your code's helpful, i never thought of using the trim function before hand on all the worksheets thanks

 

[calculus]

While I usually use the Trim method as above, there is a method called string compare that may be more appropriate for you.

(The following is from the help files)

StrComp Function

Returns a Variant (Integer) indicating the result of a string comparison.

Syntax

StrComp(string1, string2[, compare])

The StrComp function syntax has these named arguments:

Part Description
string1 Required. Any valid string expression.
string2 Required. Any valid string expression.
compare Optional. Specifies the type of string comparison. If the compare argument is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison.

 

[TLady]

Here is how to check name for all sheets

Function WSheet_Name()

MainSheet = ThisWorkbook.Sheets("MainWSheet&quot.Name

For i = 1 To ThisWorkbook.Sheets.Count
xSheet = ThisWorkbook.Sheets(i).Name

If xSheet<>MainSheet
MsgBox &quot;You have unmatched WSheets&quot;

Next
End Function


TIA