Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [vba]

Visual Basic for Applications; a subset of Visual Basic used in all Microsoft Office applications. Consider using this tag together with the tag for the specific application in question. Good questions on VBA generally contain some code and asks for solution of specific issues. Questions asking for entire solutions (please give me the code / how to do this and this with VBA) are off topic and will be closed as too broad.

37
votes
1answer
247k views

How to bypass the VBA project password from Excel

I need help with password retrieval or bypassing the password on a project that was done at work. The author has used MS Excel to do the project, and it's in the client's server. The server that we ...
35
votes
17answers
226k views

Copy Excel worksheet and maintain relative cell reference in formulas

Another copy problem in Excel: How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes =[...
31
votes
9answers
9k views

Limit Excel CPU Usage

Is there a way to limit how much CPU usage excel has access to when running? I have a VBA script that calculates a bunch of giant array formulas. The entire calculation takes maybe 20 minutes using ...
27
votes
9answers
34k views

How can I use custom colours in the Microsoft VBA editor?

I'm working on a few projects for work using Excel and VBA, so I have no choice but to use the Microsoft Visual Basic editor. I normally wouldn't mind, but I can't figure out how to set a custom ...
26
votes
5answers
117k views

Can I make an Excel VBA macro available to all workbooks?

Is it possible to write a VBA script in one workbook, and use it in other workbooks in the machine? I guess I'm looking for a way to write a macro package. EDIT Following the instruction in the link ...
24
votes
4answers
39k views

Open Multiple Instances of Excel without PERSONAL.xlsb Lock Message

Whenever I open more than I instance of Excel, I get the following message: PERSONAL.xlsb is locked for editing Q: How can I disable or hide this message? Alternatively, if someone could ...
21
votes
1answer
92k views

In Excel 2013, where are Visual Basic (VBA), Macros and AddIn tools?

In Excel 2013, where are options and tools such as Visual Basic (VBA), Macros and AddIn tools? Are they supported?
19
votes
2answers
9k views

How do I add VBA in MS Office?

I've seen posts showing answers which use VBA (Visual Basic for Applications) code/macros, however, I've also noted additional comments afterwards about how to use the VBA (how to implement and ...
15
votes
2answers
70k views

Any Excel function that will reverse a string?

Are there any built-in Excel functions that will reverse a string?
14
votes
1answer
35k views

Recover sheet protection password in Excel

I have an Excel workbook. Two years before I set password protection option for one of the sheets. Now I have forgotten the password. Is there any way to recover the password of that Excel sheet?
13
votes
1answer
9k views

How to filter Outlook email using a Rule with a VBA script?

Certain things are not possible using the default e-mail rules like searching subjects or message bodies with wildcards. You can do multiple words but it's not the same if you need the words one ...
12
votes
3answers
27k views

How to inspect / view an Excel macro before “enabling macros” for a sheet?

I'm receiving occasionally an xls file that contains a macro which I should use. If I don't have complete trust in the sender, I wish to look at the macro's source in order to verify that it indeed ...
12
votes
4answers
3k views

How to programmatically iterate through subscripts,superscripts and equations found in a Word document

I have a few Word documents, each containing a few hundred pages of scientific data which includes: Chemical formulae (H2SO4 with all proper subscripts & superscripts) Scientific numbers (...
12
votes
1answer
18k views

Which Excel objects are zero-based and which are one-based?

Using VBA to access the first sheet in a worksheet is Worksheets(1). The first item in a ListBox is myListBox.List(0). I heard that Collections are 1-based but I don't know what they are. VBA arrays ...
12
votes
2answers
97k views

How do I use a Private Sub in Excel?

I'm want to use the Private Sub functions provided here. Usually a put VBA code in the Visual Basic Editor, I then close the editor and I run the code using "Run this Macro" on the Developer tab. But ...
11
votes
4answers
47k views

Where to find a VBA function's source in Excel

I have a workbook that uses a certain vba function which doesn't appear in the Insert | Insert Function... dialog box list of available functions. How do I find it's source (to see what it does)?
11
votes
5answers
21k views

Set Excel to search within entire workbook by default

In Microsoft Excel, when I search using CTRL+F, by default it only searches within the current worksheet. How can I set it to search within the entire workbook by default? I have Office 2010. Is ...
10
votes
6answers
205k views

Adding an element to the end of an array

I would like to add a value to the end of a VBA array. How can I do this? I was not able to find a simple example online. Here's some pseudocode showing what I would like to be able to do. Public ...
10
votes
5answers
174k views

How to combine values from multiple rows into a single row in Excel?

I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this: ...
8
votes
3answers
250k views

Searching for a value in Excel, returning yes or no

I have one workbook, with two separate worksheets. I want to know if the values that appear in worksheet B also appear in worksheet A, if so, I want to return a "YES". If not, I want to return a "NO". ...
8
votes
2answers
6k views

Custom user function without using VBA

Is it possible to create a custom user function in Excel without using VBA? My problem is I have a long string of standard excel functions strung together to make one very long function. This function ...
8
votes
5answers
63k views

Select an entire column minus header row in an Excel macro

How would I access a range that corresponds to an entire column starting at row 2 (there is a header row)? The end of the selection should be the last non-empty cell in the column.
8
votes
3answers
49k views

Import json data into Excel

I have a text file in json format and want to read it into Excel. A very simplified example of the json file has the following structure: { [ { 'a': 10, 'b': 20 }, { 'a': 20, 'b': 22 }, { 'a': ...
8
votes
3answers
12k views

How do I download a stock price in Excel 2013? Is there an equivalent to Google Doc's GoogleFinance spreadsheet function?

In a Google Docs spreadsheet, I can use this cell formula: =GoogleFinance("GOOG", "price") to download the latest price of a stock. Is there an equivalent function in Excel 2013? Earlier versions ...
7
votes
1answer
12k views

How to replace text in the body of an incoming message with a hyperlink in Outlook 2010?

Does anyone know of a way or a program that will read a received email and recognize a static format of text (in my case it will be "#" followed by 6 integers), and make that a hyperlink to a webpage? ...
7
votes
8answers
145k views

Excel VBA “Unselect” wanted

I would like the final appearance after my VBA has finished running to be empty of selection -- to have no cell or range on any sheet colored (if it was range selected) or in a bold line box (anything ...
7
votes
5answers
39k views

How do I install Visual Basic for Applications 2013 in Office 365?

I've recently taken out a subscription to Office 365 and have downloaded the desktop office programs. However, I'm trying to install an EPM excel addin and it's complaining that it needs "Microsoft ...
7
votes
2answers
7k views

Delete ALL custom cell styles EXCEL

Is it possible to delete ALL the custom/created cell styles in a workbook ? just leaving the default styles. Without having to delete them all one by time.
7
votes
3answers
14k views

Using an external text editor to edit VBA code

I have inherited a lot of VBA code to maintain and so far I am using the VBE. Sometimes is quite hard to do some tasks, like advanced text search, so usually I export the module, edit the content with ...
7
votes
1answer
22k views

Word: Resize Image by Percent - Macro

I routinely paste many screen captures into Microsoft Word 2007 and then have to manually resize them to around 75%, which becomes very tedious. The screen captures are all different sizes. I've ...
7
votes
2answers
1k views

How to change Control-Y behavior in Excel-VBA's IDE

Excel VBA's IDE register's a Control-y as "cut this line of code". By contrast, redo-ing is accomplished by Alt-e, R. Is there any way to change this behavior and make Control-y be the much more ...
6
votes
5answers
14k views

How to return everything between two characters in Excel?

I'm a programmer with lots of languages, but somehow I've never learned Excel macro or formula language. I'm sure there must be a simple way to do this, but I'm stumped. I have a column of file names ...
6
votes
3answers
43k views

How can I find out which parts of an Excel workbook are the biggest in size?

I have an Excel .xlsx that weights 47MB. I'd like to know which parts of the workbook are the heaviest and work them, since I haven't made much progress manually trying to identify and reduce the ...
6
votes
4answers
107k views

How do I move the selection down one row in Excel 2007?

Is there a way to move a selected range to the next row? Let's say I have "E9:H9" selected and want the selection to move to "E10:H10". Is there a shortcut for it? The selection should not be ...
6
votes
3answers
349 views

Count unique value ignoring Text in range

123 562 My Data 548 K166 You 1548 123 562 100 My Data 100 I want to count unique values by ignoring text in the above shown list. For this I've tried an array formula, {=SUM(1/Countif(,,,,))} but it'...
6
votes
2answers
6k views

How do I get the last worksheet in Excel VBA?

I have a sheet called "End" that needs to be moved to be the last of all available sheets. I need to do this using VBA. I have a sort routine that works, I just need the syntax for the move. Here is ...
6
votes
3answers
23k views

How to add custom styles to navigation pane or change them with VBA

I have a Word2010 document that was created by a tool from Axure. Each heading is formated with a custom style ("AxureHeading1", "AxureHeading2", "AxureHeading3"). I was able to create a table of ...
6
votes
3answers
20k views

Use a Trendline Formula to Get Values for Any Given X with Excel

Is there an easy way to apply the trend line formula from a chart to any given X value in Excel? For example, I want to get the Y value for a given X = $2,006.00. I've already taken the formula and ...
6
votes
1answer
62k views

Inserting a (jpg) picture into a cell in Microsoft Excel 2007

Is it possible to insert a jpg image inside a cell, or do Excel cells only display alphanumeric characters (or references)?
6
votes
2answers
34k views

Change VBA Editor Color Scheme to something darker

Is there a way to change the color scheme in the VBA editor that comes with Microsoft Office? I have been spending several hours lately working with VBA in Excel, and the bright white background is ...
6
votes
2answers
7k views

What would an Outlook 2007 macro to automate Paste Special - Unformatted Text look like?

I'd like to assign a macro in Outlook 2007 to a Toolbar button that would execute the equivalent of these clicks when there is formatted text in the Windows clipboard: Click the Paste icon by the ...
6
votes
2answers
1k views

How to find and replace the character “*” in excel text values in cell, but NOT formulas

Very similar to this question: How to find and replace the character "*" in Excel But I need to leave formulas untouched. I've got about 50+ sheets that have two types of cells with "*" ...
6
votes
2answers
7k views

Using English macros in French version Excel

I am currently in France and am using a computer on which all Office programs are in French. I need to use a Macro which has been written in English VBA, but it fails. I assume the issue a language ...
6
votes
2answers
6k views

Create new folder for new sender name and move message into new folder

Background I'd like to have Outlook 2010 automatically move e-mails into folders designated by the person's name. For example: Click Rules Click Manage Rules & Alerts Click New Rule Select "Move ...
6
votes
3answers
6k views

Excel: Update a value in a named range

I've got some cells in an Excel doc populated from a named range (data/validation/list source=MyNamedRange): Here, the A1:A3 range is named Foobar B5:B6 is under data validation with it's source set ...
6
votes
1answer
2k views

Remove multiple 'ThisWorkbook' modules from Excel VBA editor

Within the VBA editor, I have found duplicate instances of the 'ThisWorkbook' module seen in this screenshot: I would like to delete these extra instances, but do not know how. ThisWorkbook1 is now ...
6
votes
3answers
5k views

Can I customise the new Timeline slicer in Excel 2013?

Excel 2013 introduces timeline slicers for the first time. These allow you to filter pivot tables easily by selecting time ranges - either by year, quarter, month or day. It's nearly a useful tool, ...
6
votes
2answers
7k views

VBA Runtime error 1004: Method 'OpenText' of object 'Workbooks' failed

After an upgrade to Excel 2010 (from 2003), a script used for standardizing data from Access import has stopped working. It returns an error: "Run-time error '1004': Method 'OpenText' of object '...
6
votes
1answer
1k views

How to create a Call Diagram for Excel macros?

I have an Excel file with multiple VBA-Macros. For documentation purposes I want to create a UML Call Diagram, or similar that represents which macro calls which other one. Is there a way to ...
6
votes
1answer
4k views

How to use the autocomplete feature for VBA function in Excel 2007 with Excel Add-In

(cloning from question on SO) I created a function in VBA. I want Excel 2007 to show the Autocomplete when writing this function in the cell's Excel. Detail as How to use the autocomplete feature for ...