VBA Ignores Ampersand in String for contactenation

T

Thorben

New Member
Joined
Jul 29, 2010
Messages
12
Hi,

I dont know, if there is a solution to my problem

I have a long string ( a file path) which is contactentiated (via the ampersand) with some as strings (which should vary). This works perfect if i initialize the string in the vba code, but not at all when i try to initialize the long string via a Cell Range from the excel sheet

Below you find a simplified version of problem

Var2 does ignore the ampersand for contactenation, while Var does not. What can i do to make Var1 recognize the ampersand?

Code: 
Sub Stringcontact()

Dim var1 As String
Dim var2 As String


Dim datum_string As String
datum_string = "01/01/2010"

var1 = "Datum" & datum_string
var2 = Range("A1") 'Cell A1 is: Datum" & datum_string

MsgBox ("Var1: " & var1 & vbNewLine & "Var2: " & var2)

End Sub
Thank you
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Hi Thorben

I'm sorry, I can't understand what it is you are attempting to do. What do you actually have in cell A1?
 
Upvote 0
If you run the code with having Datum" & datum_string in Cell A1 you will get a message box that shows you that var2 ignores the ampersand and thus does not return the content of the datum_string variable.

But it would be great if it could do so. How can i make it work?
 
Upvote 0
Thorben

I'm a bit confused too.:eek:

I don't see how an ampersand would be ignored.

Are you using the ampersand for the concatenation or do you want an ampersand in the string resulting from the concatenation?

If you literally have the string 'Datum" & datum_string' in A1 then the & is just going to be seen as part of the string, it's not going to be interpreted as an operator.
 
Upvote 0
Hi Norie, thank you. Yes i would like the ampersand in the string 'Datum" & datum_string' to be interpreted as an operator.

My problem is:

My vba uses a lot file pathes in strings (global variables) like e.g.


file_path = S:\FB Depot\Static Data\NAV\Sparinvest\20100730\Data sparinvest\sparinvest prices 20100730.xlsx

However, these pathes are not static, the dates in it will change regulary, while the whole the file path may vary irragulary.

The users have no knowledge of VBA, so it would be great to have a cell like this in a excel sheet: S:\FB Depot\Static Data\NAV\Sparinvest\" & datum_string & "\Data sparinvest\sparinvest prices " & datum_string & ".xlsx

....which then goes into vba via file_path = Range("A1")

The advantages would be, that the users can change the file path in the excel path (without touching any vba code), and the vba sub would be able to work with it.

Additionally, the file_path variable will be initilaized with the path from the cell evertime the workbook is opened via a workbook_open() sub. - so the user does not have to initialize the file_path variable every time again manually-

Unfortunatly VBA wont interpret the ampersands as operators, so the whole thing would not work.

Any ideas?
 
Upvote 0
Well as far as I know this just isn't going to happen, the closest I can think of would be the Evaluate function.

I'm no expert with that but as far as I know it's for evaluating worksheet functions not code.

Why can't you just let the user put the required data in a cell and then use that to build the path?

Perhaps you could even use a named cell.
 
Upvote 0
In this line, do you want a quote instead of the apostrophy?

var2 = Range("A1") 'Cell A1 is: Datum" & datum_string
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Loop through rows making each line 80 Characters long
Replies
4
Views
278
Dave01
D
R
  • Question
VBA Help: Pull from Sheet1 to Sheet2 under Conditions
Replies
0
Views
302
ragnar12
R
B
  • Question
VBA how to evaluate the value of a variable from a text
Replies
3
Views
651
Fluff
Fluff
W
  • Question
VBA InputBox without entry quitting Sub/Macro
Replies
1
Views
119
Micron
M
S
  • Solved
Need macro to only close workbook at the end if it wasn't already open when Workbook Open Event runs
Replies
5
Views
199
sspatriots
S

Forum statistics

Threads
1,215,237
Messages
6,123,803
Members
449,127
Latest member
Cyko

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top