how to dim and set an object at the same time?

[yomero]

Is there a way to dim and set an object in the same line? See example below"

Dim MyRange as range
set MyRange = range("a1")

Is there something like:

set MyRange = range("a1") as range

If there is, can it be made public, or static, or will it be always dim?

thanks

 

[nightcrawler23]

I m not sure if it happens ain VBA.
But in VB .Net a statement like this is valid

Dim a As Integer = 0

 

[yomero]

I just tired it, it doesn't work in VBA Excel.

I m not sure if it happens ain VBA.
But in VB .Net a statement like this is valid

Dim a As Integer = 0

 

[Michael M]

Hi Yomero.
From VBA Help

The Dim, Private, Public, ReDim, and Static statements only declare a variable that refers to an object. No actual object is referred to until you use the Set statement to assign a specific object.

The following example illustrates how Dim is used to declare an array with the type Form1. No instance of Form1 actually exists. Set then assigns references to new instances of Form1 to the myChildForms variable. Such code might be used to create child forms in an MDI application.

Dim myChildForms(1 to 4) As Form1
Set myChildForms(1) = New Form1
Set myChildForms(2) = New Form1
Set myChildForms(3) = New Form1
Set myChildForms(4) = New Form1

Hope that explains your request

 

[xenou]

It depends on what you want to do. Short answer is no - you can't do this in VBA as you can in other languages. But you can declare constants. But you can't declare constants for objects (such as ranges).

So this would work:
Const MyRange As String = "$A$1"

Then in your code you can use the address of the range as needed.

But in excel, other solutions are often available. For a range, a named range would work very well, for instance, with no coding required at all. You have options for public, private, and static variables (and constants). I'm sure there's plenty on the web if you want to find out more about these too - this affects scope, which determines whether the variable is visible only in the sub where it is declared or to other procedures, as well as the life of the variable.

 

[AlphaFrog]

As best I understand, you can't Dim and set the value at the same time with VBA. As in your question, you would have to use two lines to Dim and then set a variable. An exception to that would be when you create a constant e.g.
Const x As Integer = 10
...But you couldn't change the constant and you cant have a range as a constant.

You can reference Named ranges in VBA. This would be similar to a Public variable available to all procedures. Say you had a named range called MyRange, then you don't need to Dim anything to use it e.g.

x = Application.Sum(Range("MyRange"))

 

[Legacy 14611]

In fact you can do it with at least some objects.

I haven't checked out just how many though.

For example if you want to use the dictionary object in VBA, you can do it by either of:

Dim dic As Object
' and when you need it
Set dic = CreateObject("Scripting.Dictionary")
'etc with however you want to use the object dic

or just:

Dim dic As New Dictionary
'etc with however you want to use the object dic

although in the latter case you may need to get the code to refer (through Tools-->References in the code window menu) to Microsoft Scripting Runtime.

And likewise for some other objects.

Is this the sort of response for which you were looking?

 

[yomero]

I can think of useful ways to incorporate your replies into my projects.
Thanks for all your responses.

 

[JackDanIce]

Don't know if this helps, but in your original example code, you can change it to:

Dim myrange As Range: Set myrange = Range("A1")
myrange.Select
' test to see it works and it does