Converting an object's name, passed as a string, back to an object

Question

I have a UDF with one required parameter (ByVal WrkshtCodeName): a worksheet's codename.  If a user inadvertently puts double quotes around the parameter, is there a way to convert the string (e.g., "Sheet1") back to an object (e.g., Sheet1) before my UDF proceeds with the rest of its statements?

Thanks in advance for any assistance.

EDIT:  I should have said "VBA function" rather than "UDF" . . . my apologies.

Answer 1

Hi,

We can convert string of Worksheet name to object because it's hard to convert string of Worksheet code name.

Here's some codes for you, please copy them to a Module.

Option Explicit

Public ws As Worksheet

Sub UDF(WrkshtName)

    If VarType(WrkshtName) = vbString Then

        Set ws = Sheets(WrkshtName)

    ElseIf VarType(WrkshtName) = vbObject Then

        Set ws = WrkshtName

    End If

End Sub

Sub TestForConvertingStringToOjbect()

    Call UDF("Sheet1") 'Here the parameter 'Sheet1' is a Worksheet name.

    MsgBox ws.CodeName, vbInformation, "String to Object"

End Sub

Sub TestForObject()

    Call UDF(Sheet1) 'Here the parameter 'Sheet1' is a code name of Worksheet.

    MsgBox ws.CodeName, vbInformation, "Direct Use of Ojbect"

End Sub

The  above two macros('TestForConvertingStringToOjbect' and 'TestForObject') will show you the result.

Hope this help you.

Answer 2

I have a UDF with one required parameter (ByVal WrkshtCodeName): a worksheet's codename.  If a user inadvertently puts double quotes around the parameter, is there a way to convert the string (e.g., "Sheet1") back to an object (e.g., Sheet1) before my UDF proceeds with the rest of its statements?

Thanks in advance for any assistance.

It's hard to imagine someone inadvertently adding double quotes since it would require them to use 3 double-quotest, but...

maybe, replace the leading and trailing double-quote, if any with nothing?  Something like

function myUDF(byval WSName as string)

    if left(WSName,1 )="""" then wsname=mid(wsname,2)

    if right(WSName,1)="""" then wsname=left(wsname,len(wsname)-1)

    ...

    end function

Answer 3

Thanks for your solution.  In my original post, I should have said "VBA function" rather than "UDF".  My apologies.

Unfortunately, your solution causes an error on the line:

     If Left(WSName,1 )="""" Then wsname=Mid(wsname,2) 

if a user forgets to include the double quotes.  And in my original post, I specifically showed the parameter's syntax (ByVal WrkshtCodeName) because my VBA function normally expects the codename to be inputted as an object (since a worksheet's codename is, by definition, an object).  But just in case a user inadvertently enclosed the codename in double quotes, I wanted to know if there was a way to convert a string parameter back to an object parameter.  Hence, the reason for my original post.

Thanks all the same.

Answer 4

Thanks for your solution.  In my original post, I should have said "VBA function" rather than "UDF".  My apologies.

As you rightfully pointed out in your first sentence, it's much easier to convert a worksheet's name (a string) to an object.  But  I really need a way to convert a worksheet's codename, that has been (inadvertently) inputted as a string, to an object.

Thanks again for your help.

Answer 5

Hi,

 

We can convert string of Worksheet name to object because it's hard to convert string of Worksheet code name.

Here's some codes for you, please copy them to a Module.

Option Explicit

Public ws As Worksheet

Sub UDF(WrkshtName)

    If VarType(WrkshtName) = vbString Then

        Set ws = Sheets(WrkshtName)

    ElseIf VarType(WrkshtName) = vbObject Then

        Set ws = WrkshtName

    End If

End Sub

Sub TestForConvertingStringToOjbect()

    Call UDF("Sheet1") 'Here the parameter 'Sheet1' is a Worksheet name.

    MsgBox ws.CodeName, vbInformation, "String to Object"

End Sub

Sub TestForObject()

    Call UDF(Sheet1) 'Here the parameter 'Sheet1' is a code name of Worksheet.

    MsgBox ws.CodeName, vbInformation, "Direct Use of Ojbect"

End Sub

The  above two macros('TestForConvertingStringToOjbect' and 'TestForObject') will show you the result.

Hope this help you.

A UDF in Excel has a very specific meaning.  It is a "user defined function" that one can use in an Excel formula as though it were a native function.  It is *not* a sub named UDF. ;(