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.

learn more… | top users | synonyms

18
votes
5answers
6k 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 ...
14
votes
1answer
48k views

In Excel 2013, where are Visual Basic, VBA, Macros, AddIn Tools?

In Excel 2013, where are options and tools such as Visual Basic (VBA), Macros and AddIn tools? Are they supported?
11
votes
4answers
2k 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 ...
8
votes
4answers
7k 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)?
8
votes
1answer
18k 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?
8
votes
4answers
9k 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 ...
7
votes
5answers
30k 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.
7
votes
2answers
28k 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 ...
7
votes
3answers
17k 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': ...
7
votes
2answers
3k 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
3answers
25k 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 ...
6
votes
4answers
47k 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: ...
6
votes
3answers
3k 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 ...
6
votes
1answer
416 views

Word 2007 macro gone

I have programmed a little macro for text search in Word 2007. I tried to install it on other accounts in our company with very strange results. On two accounts it worked like it should. On other two ...
5
votes
5answers
2k views

Excel VBA program only running at 25% speed

I have a VBA program running on my quad-core Acer Aspire S3 Ultrabook. The trouble is, it only uses 25% of the CPU (the other processes combined use ~1%). The laptop runs Windows 8.0. The Excel ...
5
votes
4answers
482 views

How can I record a transition from a 0 to 1 in a column in Excel?

I need to record the number of transitions from a down state (0) to up state (1) in a single column in excel and I am wondering whether there is some kind of logic like this available in Excel or ...
5
votes
5answers
9k 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 ...
5
votes
3answers
8k 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 ...
5
votes
2answers
189 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 it). I'm ...
5
votes
4answers
11k views

Excel VBA Function runtime error 1004: Application-defined or object-defined error

I'm trying to learn functions for the purpose of simplifying and reusing code whenever necessary. I began by turning something I use pretty often into a function: Returning the integer value of the ...
5
votes
2answers
5k 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 ...
5
votes
1answer
2k 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 ...
5
votes
1answer
3k views

How to filter source sheet in Excel pivot table drill down?

I record my expenses in an Excel spreadsheet. In a second sheet I have a pivot table that allows me to group my expenses by month and by category to see the totals. If I double click on a cell, a ...
5
votes
1answer
3k views

How to auto advance a PowerPoint slide after an exit animation is over?

PowerPoint entrance animation set up with "Start: With Previous" starts right when a new slide is advanced. However, if you set up an exit animation in the same way, it doesn't start with a slide ...
4
votes
3answers
26k views

Any Excel function that will reverse a string?

Are there any built-in Excel functions that will reverse a string?
4
votes
3answers
1k views

Calculating the average of variably sized blocks of numbers within a column?

In column A I have a set of numbers (over 1,000), and I want to get an average of a block of them at a time, (e.g., 10, so A1:A10 then A11:A20 and so on) and write these averages into column B, such ...
4
votes
9answers
41k 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 ...
4
votes
1answer
3k 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? ...
4
votes
2answers
4k views

A thousand JPEG images in a Word document: how can I format them in the same way?

Please suppose you have a Microsoft Word 2007 document: you have just inserted in it about a thousand of JPEG images. With the first image, you do right-click, then FORMAT PICTURE, then option ...
4
votes
1answer
27k 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)?
4
votes
3answers
5k views

How can I split Excel data from 1 column into 8 columns while keeping the data in a specific order?

I need to maintain the order of the data. So to explain I need to move the data from cell a1 to b1, a2 to c1, a3 to d1, a4 to e1, a5 to f1, a6 to g1, a7 to h1, a8 to i1, a9 to B2 a10 to C2 a11 to ...
4
votes
1answer
8k 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 ...
4
votes
1answer
1k views

How do I create an Excel Macro to output key/value pairs to file?

I have two columns as key/value pairs and want to export them to a file in the structure key=value (to create a properties file). I tried this script: Sub Properties() Dim FileName As String, i As ...
4
votes
1answer
451 views

Excel 2003 macro doesn't see named cell

I'm trying to clean up an excel macro where I was referencing cells directly ("LoopCount = Range("E4").Value") instead of using named cells ("LoopCount = Cell_LoopCount"). So I named cell E4 ...
4
votes
1answer
2k views

Add/Subtract button Excel VBA

Here is an example of my project in this picture I like collecting, so I am making an inventory sheet in Excel. I want to make a way that I can click a button and it affects the next, or previous ...
4
votes
5answers
8k 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 ...
4
votes
2answers
504 views

Counting pages within Word [.doc] documents

I'm trying to find a way of listing the number of pages within multiple word documents, all saved within the same folder. Is this possible using VBA? I am using: Windows 7 and Microsoft Office 2007 ...
4
votes
1answer
2k 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 ...
4
votes
1answer
185 views

Excel “super duper” vlookup formula needed

If I have the following data: 123, thomas 123, gordon 123, smith 334, joey 334, nancy 3452, angela 3452, liza I want a formula to produce the following results: 123, thomas, gordon, smith 334, ...
4
votes
1answer
10k views

Find and replace multiple values within selected cells

I want to run a find and replace on multiple values within a range in excel with values from 2 columns: A with the original word; B with the translation. I already found the VBA code to get this ...
4
votes
1answer
2k views

Excel 2010 Macro “Command could not be completed by using the range specified”

Running a macro on an Excel spreadsheet, I get the Run-time error: 1004 Command could not be completed by using the range specified. Selected Debug and the highlighted code is: ...
4
votes
2answers
801 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 ...
4
votes
1answer
3k views

Can I remove images from an Excel spreadsheet by name?

I have a series of large Microsoft Excel 2010 spreadsheets with images in them, and I need to remove 2/3 of those images. All the images I need to remove contain the same string of characters in the ...
4
votes
1answer
450 views

Read mail attached excel files

Is there an easy way to data read excel files that are attached in emails in Outlook? Im sure this is scriptable, but I dont particularly want to write it. Is there a program out there that can ...
4
votes
2answers
2k views

Excel - concatenating cells where reference number matches [duplicate]

I have a worksheet with many rows. Some of the rows share reference numbers (contained in, say, column A). What I'd like to do is, where a row shares a reference number with another row, I'd like to ...
4
votes
1answer
700 views

How do I package an Outlook 2010 macro for other users?

What is the best means available from within the MS Office 2010 suite to package up macro code written for Outlook, so that I can make it available to my coworkers and make it easy for them to install ...
3
votes
3answers
204 views

Filling empty spaces in a column with a series in Microsoft Excel

I have a list like this: A B Student ID Student Name 20496836 Barnes, Troy 28682693 Bennett, Shirley Edison, Annie 28395839 Hawthorne, Pierce ...
3
votes
3answers
913 views

Is there a way to set the name box of a cell programmatically in Excel

I would like to programmatically set the name box of a cell in a worksheet I'm working on, either through a formula or through a macro. The end point is that I don't want to set it by hand. In my ...
3
votes
3answers
30k 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 ...
3
votes
2answers
723 views

How can I keep every fifth row in Excel

I have a data set in excel with 3084 rows. I need to delete 4 rows, then keep one: deleting Tuesday, Wednesday, Thursday, Friday but keeping Monday to create a weekly rather than daily data set. ...