pasting varible number of chars into a bookmark

User avatar
stuck
Panoramic Lounger
Posts: 8127
Joined: 25 Jan 2010, 09:09
Location: retirement

pasting varible number of chars into a bookmark

Post by stuck »

In Excel the formula:

Code: Select all

=MID(A1,FIND(" ",A1, 1)+1,LEN(A1))
extracts the bit of the cell contents I want. It's probably not the most elegant way to extract a variable number of chars after a space (and that might be part of my problem) but it works.

I tried to use this in Word VBA, so that what I paste into my bookmark is just the bit of cell A1 that I want:

Code: Select all

BMRange.Text = Mid(xlWsh.Range("A1").Text, Find(" ", xlWsh.Range("A1").Text, 1) + 1, Len(xlWsh.Range("A1").Text))
but I get a compile error (sub or function not defined) at the point where Find is used inside Mid.

Obviously I'd like a fix for this particular issue but can anyone also give me pointers to more general guidance on how to make Excel formulae VBA friendly?

Thanks,

Ken

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: pasting varible number of chars into a bookmark

Post by HansV »

In Excel VBA, you can execute many (but not all) worksheet functions in VBA by using the Application.WorksheetFunction object, but this is obviously not available in Word VBA (at least not by default).
Some worksheet functions such as LEFT, MID and RIGHT are almost equivalent to VBA functions of the same name, but others are different. The Find method in Word searches for text in a document, not in a string. The VBA function to search within a string is InStr, but its syntax is different.

BMRange.Text = Mid(xlWsh.Range("A1").Text, InStr(xlWsh.Range("A1").Text, " ") + 1)

We don't have to specify the length here. The Mid function in VBA returns the rest of the string if you omit the length.
Best wishes,
Hans

User avatar
stuck
Panoramic Lounger
Posts: 8127
Joined: 25 Jan 2010, 09:09
Location: retirement

Re: pasting varible number of chars into a bookmark

Post by stuck »

:thankyou: Hans, that helps. Another small step forward for me.

Ken