Using Excel functions within VBA to define string variables

[Scott JS]

I am trying to define two variables for use in concatened instructions. The variables are the 1st 3 letters of the worksheet and the overall worksheet name. The code is bombing at the first SET command when I try to define the first variable:

    Dim dn As String
    Dim wsn As String
    Set dn = Mid(CELL("filename"), Find("]", CELL("filename")) + 1, 3)
    Set wsn = Mid(CELL("filename"), Find("]", CELL("filename")) + 1, 20)

The error returned is:
Compile Error:
Sub or Function not defined.

Note: the function “=Mid(CELL("filename"), Find("]", CELL("filename")) + 1, 3)” returns the first 3 letters of the name of the worksheet in question

No doubt this is a simple syntax issue, but it escapes me.

 

[Tom Urtis]

String variables are not to be set.

Dim dn As String
Dim wsn As String
dn = Left(ActiveSheet.Name, 3)
wsn = ActiveSheet.Name


The above suggestion will do what you had in mind, assuming it's the active sheet. If it's not the active sheet you had in mind, you should use the CodeName property instead of the sheet tab name.

 

[sadia_tariq]

Hi,

I am facing similar kind of problem when i use Find(":",sText) in my code like this.......

sChunk = Mid(sText, Find(":", sText) + 1, Find(".", sText) - 1)


the error refers to:
Sub, Function, or Property not defined (Error 35)

Can anyone explain it?

 

[sadia_tariq]

I am using this syntax.....Find("S", sText, 1)

 

[VoG]

Try

instr(sText,"S")