Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Use Of Square Brackets In Vba Excel 2003

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

can someone help me. My code works great on my computer but when I bring it to another computer it gives me errors. I think it has to do with my use of square brackets to reference cells. the error that comes up is

compile error:
can't find project or library

What I'm doin here is going to my template sheet then getting the info from different cells and then later using it somewhere else. in place of using
range and selecting cell I am using square brackets to select a cell. it worked fine on my computer and was a lot less writing
here is a small part of my code

Sub SheetSetup()
         Sheets("Template").Select' go to template sheet
         NumberofRowsToSetup = [b15] + 8 
         '''' NumberofRowsToSetup = what ever number is in B15 on the sheet+8
         Numberofdropdown = [b16]
         NumberofDateSetup = [a16]
         NumberofFieldSetup = [o16]
Call ColunmWidth

I'm new at excel an using square brackets in this way might be incorrect, but the thing I don't understand is it work awesome on the computer I created the program on

thanks for any help you give me


View Answers     

Similar Excel Tutorials

Increment a Date By X Days for Each Row in Excel
How to quickly increment a date by a certain number of days for each entry. Type the first date in a cell. Make sur ...
Scale Data for Printing in Excel - Fit more onto a Printed Page
This tip shows you how to fit more Excel data onto a page for printing. This does not change the size, shape, or lo ...
Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
Prevent Errors From Appearing in Excel
How to prevent errors from appearing in formulas in Excel. This is especially helpful for the Vlookup function. Sec ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Open any Program from Excel
- This free excel macro allows you to open any program on your computer from excel. You can open a media player, file vie
Open Microsoft Outlook from Excel
- This free macro will open the Microsoft Outlook program on your computer. You do need to have this program first. This

Similar Topics


I have a column with a series of sentences generated from a server. After the word "Problem" it then gives an error code between square brackets, i.e.

"Problem in Error [55143] please check in [ABCD]"

The error codes between square brackets are of diff lengths, and I need to export into a diff column the value between the square brackets AFTER the word "Error" (not the latter one). Is this possible please?

I am using the following formula which searches for instances of [square brackets] around text in column A and returns the same text but with <angular brackets> around it:

=IF(AND(SEARCH("[",A1),SEARCH("]",A1)),LEFT(A1, SEARCH("[", A1)-1) & "<" & MID(A1, SEARCH("[", A1)+1, SEARCH("]", A1) - (SEARCH("[", A1)+1)) & MID(A1, SEARCH("[", A1)+1, SEARCH("]", A1) - (SEARCH("[", A1)+1)) &">" & RIGHT(A1, LEN(A1)-SEARCH("]", A1)),A1)

This formula works fine, when there is an instance of [square brackets] in the cell in column A.
However, if the cell doesn't contain square brackets, the formula returns #VALUE!; clearly there is an issue with the underlying IF function.

Can anyone offer any solution for this? Any help would be very much appreciated!

I have a column full of strings of data. Some of the data is contained within [square brackets].

"For example, [this] could be [one of] my cell's [contents]."

What I would like to do is have and instances of apostrophes: ' be replaced with a backslash and apostrophe: \' , but only if the apostrophe appears within [square brackets].

"For example, [this cell's] apostrophes [should] be replaced if within [brackets], but shouldn't be replaced if [not within] brackets"

The result would look like this:

"For example, [this cell\'s] apostrophes [should] be replaced if within [brackets], but shouldn't be replaced if [not within] brackets"

Any ideas?

Note: there could be any number of [apostro'phes] [withi'n any nu'mber] of [brack'ets]

Hi there

I've just imported a huge column of chemicals and their registartion numbers. The registration numbers are always in square brackets []. Problem is that both the name and the registration number of each chemical are in the same cell.

Usually this problem would be easy by using the Data>Text to Column option and choosing the opening square bracket as the delimeter

But the problem is that the square brackets appear in the name of some of the chemicals as well.

The only thing common to the registration numbers is that they are the LAST pieces of information in the cell.

Is there a macro which will find the first opening square bracket from the RIGHT and move all the contents after that bracket into the next cell. Or is there a better approach to this problem


[2,3'-Bis[[(2-hydroxyphenyl)methylene]amino]but-2-enedinitrilato(2-)-N2,N3,O2,O3]nickel [64696-98-6]

I want 64696-98-6 to appear in the 2nd column while the the rest remains in the first column.

Any help will be appreciated.

Best regards


Thank goodness i had a coffee this morning already :/

Ok, i have a piece of software that allows me to build queries. Problem is that it doesn't help you much with the syntax, and just 1 thing out of place, or 1 missing bracket etc, and the thing wont work. It also wont tell you what's wrong with the syntax, just that the entire thing is incorrect. I'm putting together a little spreadsheet that'll allow me to do some simple checks, like check that the total number of brackets add up.

The next thing is the tricky bit. I'd like to try & split out unnecessary brackets. (might need some code for this one!) For eg, here's some simple syntax:


([Customer.Change_Of_Address_Date] >= 19032009) AND (([Customer.VF_Exclusion_UK_Email_NH] = "0"))

Note the 2 unnecessary brackets around the 2nd field (a field is always distinguished by square brackets)

Is this possible or is it past the remit of XL?



Does anybody have a UDF handy to extract the text contained between two square brackets i.e.

'='F:\WIP\2007 Files\[Cse5.xls]Schedule 1 Calc'!$D11

should give


thanks in advance


I am looking for a piece of VBA code that will search for a specific character and then look at the character before and after this specific character for another character....

For example....
Cell contents = worldwideweb[.]co[.]uk
I want the VB code to search for the full stop and then look before and after the full stop for the square brackets. If the square brackets do not exist then replace the full stop with '[.]' but if the square brackets do exist then leave the URL alone.

Thank you.

Is it possible to format a text cell so that the user can just type in
a text reference from a catalogue - i.e., letters of the alphabet -
but that Excel then puts in square brackets?

i.e., rather than typing in [A1], the user types in A1 and Excel shows
the cell contents as [A1}.


I am currently translating a client's Excel VBA into TSQL in Server 2005.

In the VBA it makes cell references using the style [cellname].

I've never seen that before.

If you set a break point and hover the mouse over one of these references you don't get to see the value of the cell (because the value is held in a cell not a variable).

I'd be grateful for any tips on working with this style of referencing (do, dont's, piftalls and advantages). Couldn't find any references to this method in the online Help.

I tried replacing all those style of references with Range("cellname") types references and found the application no longer worked correctly.

Do the square brackets do something extra other than reference a cell's value I should know about?


I have an Excel spreadsheet where cell A1 contains the following string of text:

[text] more text (even more text)

I want cell B1 to contain:


and cell C1 to contain:

(even more text)

Repeat for the rest of the rows.

Basically, for column B I need some formula to parse column A text, search for text inside square brackets and copy it along with the brackets. Same thing for column C, except the text in this column has to be the one inside curved brackets, along with the brackets, of course.

I'm an Excel noob. Cheers.

XPHomeSP3, Excel 97, VS2005,C++

Hi, I have a need to communicate from my c++ dll to Excel.
I can Poke & Request data ok however when I try to run a macro it does not run..

Using Application "Excel", topic "System" or topic "C:\test.xls" and DdeClientTransaction with XTYP_EXECUTE i can submit a DDE command that has no parameters such as [QUIT] but any commands (including RUN) that have paramters in brackets does not run.. eg RUN("MyMacro").

By removing the square brackets i can see the command gets written to the active cell as RUN"MyMacro" ie. the round brackets have been stripped.

Debugging the text from the c++ code i can see the brackets are delivered to the DdeClientTransaction... but seem to be stripped somewhere.

All the references/examples make no mention of not having the brackets or issues in transporting them..
I have tried both unicode and ascii versions of the DDE calls in c++...

Does anyone have some clues or working examples (from c++).

Thanks if you can share some info.. (thanks for reading too



On my sheet is a square of -let's say- 10 x 10 cm. When I print this square to my printer the square measures about 9.9 x 10.8... (I have tried this on two printers)

How can I make sure my square on screen comes out of the printer as a square. ??

I don't really mind about the dimensions of the square, but it has to be a square

Thanx in advance.


Is it possible to write a macros to solve the following issue:

In a cell, there is a sentence containing the open square brackets character: "["

For the next space " " that is found after the "[" character, this space should be replaced with a close square brackets character "]".

The space to be replaced would be an undefined number of characters after the "[".

Only the next space following the "[" character should be replaced with "]". All other spaces should be left as they are.

I hope I explained that OK.

Hi, i want to lookup some strings contains square bracket like "aaa[bbc]", but cannot. I find that even if i F5 to search in excel sheet aaa[bbc], i cannot find anything aslo. Can anyone helps? Thanks


(I'm using Excel 97.)

I'm running into a problem adding a sheet to a workbook that was created by downloading a file off the internet.

The steps:
1. Use a "Export to CSV" feature on a webpage w/tabular data.
2. Choose "Open" with Excel.
3. Now I have a new workbook with one worksheet. I want to add another worksheet (via an add-in).
4. I tried Sheets.Add, but I get an "Application-defined Error"

I think the problem is the downloaded worksheet is automatically named something like report]_2008_03[1].csv. I've manually removed the brackets and then run the add-in and all is fine.

Is there a way I can automatically rename the sheet? I've tried simply Sheets(1).Name = "New Name" with no such luck either.

Thanks in advance.

Hello everybody,

I've got a problem that goes as follows;

I have three types of data;

I want to return dogs for all three i.e. remove either square brackets or speech marks.

Assuming that the data starts in cell A2 the formula I put together goes thus;


However instead of following the IF=FALSE path into the next IF statement the formula returns #VALUE! if the data does not contain [.

What have I done wrong?

Thanks in advance


I'm currently writing some Access queries in SQL view. The queries contain plenty of Joins that the design view cannot represent, such as:

    SELECT DISTINCT tbl_Res_Audit.nBookingID, dteEditDateAndTime, nUserID
    FROM tbl_RES_Audit
     ) AS ResAudit1
    ON ResAudit1.nBookingID = tbl_RES_RoomBookings.anBookingID

The problem is that Access insists on changing the queries - sometimes just when the database is closed and reopened.

It removes these sub-SELECT joins, puts square brackets in and in one case it even put a ; in halfway through the statement.

This basically breaks the queries - is there anyway of telling Access to leave the queries alone?
I just want it to leave the SQL as I entered it. I don't mind if it takes my formatting out and puts it all in one continuous line - just please, don't remove the JOINS or put square brackets in.

Really hope someone can help with this, cos it's doing my head in now.

Hi there everyone,

I have a workbook which contains links to named ranges that are in
external workbooks stored on the server. When the external workbook is
open the cells show the correct values. When the external workbooks
are closed the cells show #REF. The formulae are obviously correct so
why can excel only read from files that are open? Can anyone explain
this? Also - a small additional query - why is it that when excel
links to an external file it drops the square brackets round the file
name & path if the link is to a named range. The square brackets
appear if the link is to a cell address?

Thanks in Advance


At the end of one of my macros, I am deleting two source sheets of data and for some reason it's not working, I think it has to do with the () in the name of the sheet, I tried enclosing that portion in brackets but no luck...


Sheets("(AV-UPDATE-OH001) CURRENT PBUSE").Select

It errors out on that line every time, even when I try to surround the (AV-UPDATE-OH001) portion in brackets, and the parenthethes in brackets. Any suggestions, and maybe a wise word on what I did wrong? I'm trying to learn so one day I can give great advice rather than just ask for it

I have created a reference table that looks like this:

RFQ Purchased in Conversion Factor ft m 3.28083 m ft 0.3048 in ft 12 mm ft 304.8 mm m 1000 square foot square metre 0.09290304 square metre square foot 10.76391 lb. kg 0.4535924 kg lb. 2.20462 ft ft 1 m m 1 in in 1 mm mm 1 square foot square foot 1 square metre square metre 1 lb. lb. 1 kg kg 1 unit unit 1

What I want to do on another page is fill in values from the first two columns in side by side cells and have a third cell recite the value from the third column. I believe what I am looking for is a pivot table and the getpivotdata function but I am confused haow to go about using it.

I would like to search all of column A for text in cells in column B that is surrounded by [square brackets].

For example, I have in cell B1 the text "This is an [example] cell".

In cell A276 is the value "example"

...So in this case, the argument is true. If column A did not contain "example" anywhere, the argument is false

Using conditional formatting, I would like to highlight all the cells in column B that contain bracketed text that does NOT appear in column A at all. Note that cells in column B may contain more than one set of brackets.

Is this possible?

I have an update I am attempting to improve. Luckily, each row of data has a vector number that I can use as a reference in my data moves. The problem is, the source of our data sometimes puts brackets around the vector numbers.
For example:
Our table's vector numbers are like this: v41690974
Our source's vector numbers are like this: (v41690974)
The source though, doesn't always output their data like that, sometimes the brackets are not there. I can use VBA to remove the brackets, here is the code:


Sub Macro2()

   For Each c In Range("SourceCansim")
        If WorksheetFunction.IsText(c.Value) Then c.Value = Left(c.Value, Len(c.Value) - 1)
        If WorksheetFunction.IsText(c.Value) Then c.Value = Right(c.Value, Len(c.Value) - 1)
   Next c
End Sub

The problem is, if they don't have the brackets, the macros will remove the last number and the "v" from the Cansim number. Is there something I can do that checks to see if there are brackets? Then if so, remove brackets? If not, carry on to next line in VBA?

Hi @ all,

I use a macro for deleting a text between two brackets in a range("a1:a6000").

Please Login or Register  to view this content.

it works fine but only for the first pair of brackets:

something like "cars[ss,s,c]" becomes "cars"


auto[o,au]mo[o,au]bi[i,ay,ai]le becomes automo[o,au]bi[i,ay,ai]le

how can I modify the code to delete all the text between all the bracket pairs in a cell ?

Many thanks in advance for any help!

I have a sheet with some extensive macros which I created - it worked perfectly on my computer for about a year then all the sudden I get a "Compile Error - Can't find project or library" when trying to access the macro.

Sometimes it even goes into "recovery mode" where it crashes, restarts and puts a red exclamation mark on the sheet title. Then it disables all my forums.

I checked with other co-workers and the sheet works perfectly on all of their computers (I tried 3) but on mine only I get this error.

Any advice?

Hi all,

I wonder if someone could help me please?

I am entering a R1C1 formula into a cell and will want to copy it down but want to "fix" one of the cells (the equivalent of entering dollar signs before the rows and columns). Previously I used this by removing the square brackets but this isn't working and so I was hoping that someone could advise me as to what I must be doing wrong please?

My formula is:


With Sheets("Sheet 1")

     .Cells(13, 20).FormulaR1C1 = "=if(rc[-18]="""","""",rc[-18]*r[-4]c[-18])"

End With

It is the final cell I am trying to lock, r[-4]c[-18].

Many thanks in advance.