TypeName function - why is it necessary?

[ORoxo]

Guys, why doesn't this code work without the TypeName thing?

Sub num()
Dim FormulaTest As Variant

FormulaTest = Teste.Range("A1:B2").HasFormula

If [B][COLOR=#ff0000]TypeName[/COLOR][/B](FormulaTest) = "Null" Then
    MsgBox "Mixed"

Else

MsgBox FormulaTest

End If


End Sub

PS: I have text in A1 and a formula in B2

What does this function do really?

Thanks,
ORoxo

 

[rlv01]

It will work without TypeName. You just have to test for the Null case

    If IsNull(FormulaTest) Then
        Debug.Print "Some, but not all, cells in Range A1:B2 contain formulas"
    ElseIf FormulaTest Then
        Debug.Print "All cells in in Range A1:B2 contain formulas"
    Else
        Debug.Print "No cells in Range A1:B2 contain formulas"
    End If

 

[ORoxo]

It will work without TypeName. You just have to test for the Null case

    If IsNull(FormulaTest) Then
        Debug.Print "Some, but not all, cells in Range A1:B2 contain formulas"
    ElseIf FormulaTest Then
        Debug.Print "All cells in in Range A1:B2 contain formulas"
    Else
        Debug.Print "No cells in Range A1:B2 contain formulas"
    End If

What does it do exactly though, rlv?

 

[rlv01]

It is a function that will return the data type of a variable

So:

Dim I as Integer


Debug.print TypeName(I) ' -> "Integer"

https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/typename-function

 

[Domenic]

A common use would be to test whether a sheet is active, and whether the active sheet is indeed a worksheet. So, for example, one would test for an active worksheet like this...

If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

If no worksheet is active, TypeName would return the keyword "Nothing" and the If statement would be evaluated as False, and the sub is exited. Otherwise, it would return "Worksheet" and the If statement would be evaluated as True, and continue with any remaining code.

Hope this helps!

 

[ORoxo]

A common use would be to test whether a sheet is active, and whether the active sheet is indeed a worksheet. So, for example, one would test for an active worksheet like this...

If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

If no worksheet is active, TypeName would return the keyword "Nothing" and the If statement would be evaluated as False, and the sub is exited. Otherwise, it would return "Worksheet" and the If statement would be evaluated as True, and continue with any remaining code.

Hope this helps!

It is a function that will return the data type of a variable

So:

Dim I as Integer


Debug.print TypeName(I) ' -> "Integer"

https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/typename-function

Thank you both. I understood the concept but have yet to understand its practical benefit. Probably as I study more about VBA I will understand it.

 

[Rick Rothstein]

Thank you both. I understood the concept but have yet to understand its practical benefit. Probably as I study more about VBA I will understand it.

Its main benefit is to tell you what kind of value is being held by a Variant variable. Since a Variant variable can hold any kind of value, it allows you the ability to write a generic procedure that can do different things depending on the kind of value that is held in a Variant variable. For a totally made-up, nonsensical example, let's say you wanted to write a single function that would return the square of any Integer value, the square root of any Long value, a greeting for any String variable, and a warning for any array passed into it.

Function MadeUpExample(Var As Variant) As Variant
  Select Case TypeName(Var)
    Case "Integer"
      MadeUpExample = Var ^ 2
    Case "Long"
      MadeUpExample = Sqr(Var)
    Case "String"
      MadeUpExample = "Your Variant variable was """ & Var & """."
    Case Else
      If TypeName(Var) Like "*()" Then
        MadeUpExample = "You passed in an array"
      Else
        MadeUpExample = "You passed in an something other than an array"
      End If
  End Select
End Function

and here is a macro that you can use to "test" it (returns the square of 123, the square root of 987654321 and the last two messages are self-explanatory)...

Sub Test()
  MsgBox MadeUpExample(123)
  MsgBox MadeUpExample(987654321)
  MsgBox MadeUpExample("Test")
  MsgBox MadeUpExample(Split("1 2 3"))
End Sub

 

[RoryA]

It’s very useful when looping through controls on a user form to process controls of specific types, such as clearing all textboxes or setting checkboxes to True/False.

 

[Domenic]

Rory and Rick have given you some great examples. Unfortunately, I don't think I did such a good job, so I'll try to expand on my suggestion and provide you with what I hope is a better example.

Let's say that every two weeks you get a workbook that contains payroll data, and that the sheet name is "Payroll", and cell A1 contains the label "Salaries". And let's assume that each time you want to run a macro that summarizes that data. When running the macro you'll want to make sure that the proper worksheet is active. So you'll first check that you have an active worksheet, then you'll check that the active sheet is named "Payroll", and then you'll check that cell A1 contains "Salaries". If everything checks out, then you can proceed with the rest of macro. Otherwise, a message is displayed to notify you that the proper worksheet isn't active, and it exits the sub. So the code would look something like this...

[COLOR=darkblue]Sub[/COLOR] test()

    [COLOR=darkblue]Dim[/COLOR] bAbort [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    
    bAbort = [COLOR=darkblue]True[/COLOR]


    [COLOR=darkblue]If[/COLOR] TypeName(ActiveSheet) = "Worksheet" [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] ActiveSheet.Name = "Payroll" And Range("A1").Value = "Salaries" [COLOR=darkblue]Then[/COLOR]
            bAbort = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] bAbort [COLOR=darkblue]Then[/COLOR]
        MsgBox "Please make sure that the Payroll worksheet" & vbCrLf & _
            "is active, and try again!", vbExclamation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=green]'etc[/COLOR]
    '
    '
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]