0

For various reasons, I need to concatenate a text of the form [NAME].Value by changing the value of NAME to an inputbox entry.

Something like this:

Sub example()

      data_in = InputBox("Give me something: ")
 
      mystring1 = "[" & data_in & "].Value"
 
      a = Evaluate(mystring1) 'I know this is wrong, but I don't know how to do so.

End Sub

I know it can be done in other ways, and the example in which I want to use this code is not exactly this one, and while it can be done in several ways here, in the original code it can only be done this way.

I want, based on the input in the imputbox, to concatenate the string in whatever way, and subsequently cast that string as code to store the value in another variable, to be used later in the code.

I am not able to get VBA to read the string text as code. I have seen that there is a way that consists of creating a macro from this first macro, execute it, and then delete the recently created macro. The problem with this solution is that doing that I can't save the variable when returning to the initial macro (I don't want to use global variables).

Surely there must be a way?

Thank you very much.

EDIT: The code above returns Error 2015

Improve this question
2
  • a = range(data_in).value? That should work if data_in is a cell reference Commented May 14, 2021 at 10:12
  • Yes, that's also a solution. As I comment below, I was getting unnecessarily complicated. Thanks @Andreas Commented May 14, 2021 at 10:13

2 Answers 2

Reset to default
1

In order to use a string as if it was code, you can use the evaluate function (exists in most languages)

The official documentation mentions this example:

[a1].Value = 25 
Evaluate("A1").Value = 25 
 
trigVariable = [SIN(45)] 
trigVariable = Evaluate("SIN(45)") 
 
Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).[A1] 
Set firstCellInSheet = _ 
    Workbooks("BOOK1.XLS").Sheets(4).Evaluate("A1")
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Yes, I just found that. I was on my way to answer my own question. Thanks!
0

I have figured out the easiest way to do it, sorry for posting the question so soon.

Thanks to @Andreas for the solution. I'll write it here in case than could be useful to someone.

Sub example()

      data_in = InputBox("Give me something: ")
 
      a = Range(data_in).Value
 
      Debug.Print a

End Sub

In the end the simplest thing is the last thing you try...

Improve this answer

3 Comments

Generally using evaluate syntaxes is bad. I don't know about VBA but other programming languages that could cause big issues and be used as an inject stuff
You should avoid the evaluate-function, just use the syntax as @Andreas wrote in his comments
@FunThomas I've changed the answer. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.