Excel VBA Data-Types. In this example, we will declare three types of variables string, joining date and currency. Step 1) Like, in the previous tutorial, we will insert the commandButton1 in our Excel sheet. Step 2) In next step, right-click on the button and select View code. It will open the code window as shown below. Text Functions. Excel has many functions to offer when it comes to manipulating text strings. Join Strings. To join strings, use the & operator. To extract the leftmost characters from a string, use the LEFT function. To extract the rightmost characters from a string, use the RIGHT function.
I have spent hours on this and have found related questions but I have not found an answer that works, so I do not believe this is a duplicate.
I have a string in a cell that represents a simple formula a variable in it. That variable will be pre-defined. Then I need to evaluate the string. I cannot get the cell value to evaluate. The string itself evaluates in the immediate window but the exact same cell value string does not:
My best guess in the variable is not getting evaluated since I can replace the myVar with a 1 and it works. I've tried all manner of triple quoting and that doesn't work either. How can I accomplish this seemingly simple thing? I have tried wrapping in subs and functions like so:
What am I overlooking? Thanks.
fastzombiesfastzombies
2 Answers
Try this solution and revert back if it works for you.Press ALT + F11 to open VBA Editor. Insert a Module and paste the following UDF into it.
![Excel macro variables Excel macro variables](/uploads/1/2/4/8/124856076/392752475.png)
Now your expression in Excel cells should not hold any characters that are not part of the formula like & ' etc. It should hold plain formula with static name and keep the name constant thruout and case sensitive.
e.g. myvar + 3 here myvar is just static text,not declared anywhere.Use the function and pass the cell as range and variable value as double to it.No much validations are in place, passing multi dimensional array could result into an error here. See the below screenshot. Give it a try and check if this meets your requirement.
patkimpatkim3,46422 gold badges88 silver badges2525 bronze badges
I don't have enough cred on superuser to upvote, I just linked my stackoverflow account today. However, that answer was close enough to get unstuck. Here is what is working:
Now I think I have something to work with
If I find something need modifying I will update this answer. Thank you very much.
EDIT: I have made it a bit more universal:
Now use like so:
Hope this helps someone else.
fastzombiesfastzombies
Not the answer you're looking for? Browse other questions tagged microsoft-excelvba or ask your own question.
Looking for a way to achieve the following. Imagine I have 2 columns
I would like column C to contain the following: 'The next to die will be NAME Surname. Cheers!'
so to achieve this I would make a formula like
and then pull it down all my Rows
but this is a trivial example. sometimes I might have more than 20 columns, and the resulting string is way more complex. There must be a nicer way to do this?I am thinking like an equivilant to the c# function 'Format'e.g.
Any suggestions?
CrudlerCrudler
2 Answers
1st option:
2nd option:
(for hardcore users)
Create your own function:
To make it work you have to open VBA/Macros with ALT+ F11, then under ThisWorkbook insert a new module and paste the code.
Now, in any cell you can put
or whatever. Keep in mind that the string must go first and then the cell references.
This is valid:
This isn't:
jcbermujcbermu15.7k22 gold badges4545 silver badges5656 bronze badges
There isn't such a function in Excel, you can use
SUBSTITUTE
, but it still will be long:![Excel Excel](/uploads/1/2/4/8/124856076/625814172.png)
=SUBSTITUTE(SUBSTITUTE('The next to die will be {1} {2}','{1}',A2),'{2}',B2)
15.2k66 gold badges3535 silver badges5252 bronze badges