Is it possible to declare a user input string to be a variable in VBA?

Question

In VBA for Excel, is it possible to declare user input or cell value as a variable?

I am creating a spreadsheet where there is a long list (~150) of variable names and a user will enter values in the column adjacent to the name. I am looking for a way--if there is one--for the code to read in and declare each of those variables from the Range and then assign the respective user-input value without having to declare each variable name in the code manually.

Is this "possible"?

Joel Spolsky · Accepted Answer · 2018-03-06 20:58:59Z

3

One easy shortcut is to use the CreateNames method on the Range object. Although this doesn't create a VBA variable, it does create a new name on the worksheet which can be accessed through Evaluate or square brackets.

Example:

If the selection contains

  one    1  
  two    2  
  three  3

the following VBA code

  Selection.CreateNames Left:=True
  MsgBox [one]

will display 1. You can also use Evaluate("one") instead of [one] if you have one in a string variable.

answered Mar 6, 2018 at 20:58

Joel Spolsky

33.8k18 gold badges91 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

EngineeringJunkie Over a year ago

Thanks, this nearly accomplishes my goal. I see it does assign the name in the spreadsheet under Name Manager. But I must be missing something (probably obvious)... I get a run-time error 13 "type mismatch" on the MsgBox output when attempting either syntax. I tried with and without the Explicit option... same error. What am I missing?

Joel Spolsky Over a year ago

That could mean anything but probably means that the name is not defined. This is probably an interaction with something else in your worksheet, or maybe the active worksheet is not what you think it is.

EngineeringJunkie Over a year ago

I found the problem: I was applying it to a range that had 3 columns (name column, value column, units column) and hence the value assigned to the name was an array of the value and units. What I have (and does not work), it thus: A named ranged "Foo" like this: one 1 ft two 2 ft^2 three 3 ft^3 ...and some code like this: Sub Testing() Dim arr As Variant Set arr = Range("Foo") arr.CreateNames Left:=True MsgBox [one] End Sub

EngineeringJunkie Over a year ago

My apologies for the messy reply--I am inexperienced in the formatting technique here.

EngineeringJunkie Over a year ago

Just as confirmation, your method does work for my original question with only minor spreadsheet adjustments and can be used in conjunction with other named arrays and such. Something I found to watch out for is--of course--to avoid names that already exist in VBA; these cause inconsistent handling with code compiling or running.

Collectives™ on Stack Overflow

Is it possible to declare a user input string to be a variable in VBA?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related