VBA stands for Visual Basic for Applications, a subset of Visual Basic used in all Microsoft Office Applications. Consider tagging your question with the application as well as [VBA]

learn more… | top users | synonyms

16
votes
5answers
3k 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 ...
11
votes
4answers
1k 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
4k 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
4answers
6k 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
1answer
25k 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?
7
votes
2answers
18k 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 ...
6
votes
4answers
24k 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.
6
votes
1answer
13k 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?
6
votes
4answers
33k 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
6answers
8k views

Good books for learning Excel VBA [closed]

I want to learn how to use VBA. I spend around 2~3 hours making a report every week using Excel 2007 and I think I could benefit from knowing VBA well. I already know programming and as such know a ...
6
votes
3answers
2k 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
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
2answers
11k 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': ...
6
votes
1answer
375 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
8k 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
6k 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
4answers
8k 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 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 ...
4
votes
3answers
921 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
1answer
2k 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
18k views

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

Is it possible to insert a jpg image inside a cell, or do Excel cells only display alphanumeric characters (or references)?
4
votes
3answers
3k 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
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
391 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
2answers
200 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
176 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
2answers
16k 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 ...
4
votes
1answer
1k 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
1answer
993 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 ...
4
votes
1answer
2k 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
343 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
1answer
106 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
2answers
850 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
2k 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 ...
3
votes
3answers
20k views

Any Excel function that will reverse a string?

Are there any built-in Excel functions that will reverse a string?
3
votes
7answers
19k 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 ...
3
votes
3answers
143 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
675 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
2answers
419 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. ...
3
votes
2answers
2k views

How to make an Excel userForm stay on top

I have a userForm and I would like for it to stay on top of my spreadsheet even when I click within the sheet. Is there some code or a setting that I can use to do this? If so where do I put it?
3
votes
3answers
18k views

How to execute a stored SQL procedure in Microsoft Excel?

How can I execute a stored procedure in Microsoft Excel and get all the data it returns?
3
votes
2answers
4k views

How do I use Excel 2010 Personal Macro Workbook?

I want all of my macros that I've written to always be available to me when I'm using MicroSoft Excel 2010. How can I do this?
3
votes
7answers
5k views

Advanced removal of blank rows in Excel

I have a serious problem with a huge Excel sheet which I need to remove blank rows from. My problem is that I cannot use the classic F5->Select blanks->delete, since some of the fields on not blank ...
3
votes
1answer
754 views

Change checkbox visibility not working

I have the following code hiding rows in a sheet, but the sheet is left with its dynamically created checkboxes still visible. Any ideas? Private Sub SpinButton1_Change() Application.ScreenUpdating ...
3
votes
2answers
927 views

Subscripts and superscripts in Excel

I have an XL sheet with chemical formulae - which is basically just letters with numeric subscripts and superscripts. I can save as an html page and get the <sub> and <sup> html elements, ...
3
votes
2answers
4k views

How to implement MATCH and INDEX functions together in Excel VBA?

I am trying to find the row number of the first cell that is less than zero. So, by browsing through the net I was able to come up with this formula which works perfectly fine: ...
3
votes
1answer
1k 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 ...
3
votes
2answers
2k views

Excel: conditionally format a cell using the format of another, content-matching cell

I have an Excel spreadsheet where I’d like to be able to create a “key” of formatted cells with unique values, and then in another sheet format cells using the key formatting. So for example, my key ...