VBA Application.Evaluate() with parameter values from "1" to "4"-CodePudding

So I have this test code below:

Public Sub runTest()

    Dim result As String
    
    result = Application.Evaluate("4")
    MsgBox "ss"
End Sub

Whenever I try to add numbers 1,2,3,4 as parameter to Application.Evaluate it fails with the following error:

I have the following Excel version: Microsoft® Excel® a Microsoft 365 MSO (2206 build version 16.0.15330.20260) 64 bit

What I could catch one thing more: if I seckthe runtime object of Application.Evaluate("4") it returns an OLE object(?) but Application.Evaluate("5") as Double.

Why? What's the point here?

--- UPDATE ---

I add this picture as well to see all the information I know of this thing:

CodePudding user response：

As explained in the documentation, the purpose of Application.Evaluate is to convert a Microsoft Excel name to an object or a value, not anything else. This comes with its rules, one of which is:

The following types of names in Microsoft Excel can be used with this method:

...

Form Control number. You can specify a number, as well as a name, to refer to Form Control on a worksheet. For example, for Label located on a worksheet (Developer tab - Insert - Form Controls - Label), both Evaluate("Label 1").Caption = "Hello" and Evaluate("1").Caption = "Hello" do the same.

You must have four Form controls on that worksheet, so Evaluate("1") - Evaluate("4") return them. Evaluate("5") doesn't, because you don't have a fifth Form control.