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]
13
votes
4answers
1k 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 ...
10
votes
4answers
774 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
2k 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
3answers
3k 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 ...
6
votes
6answers
6k 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
2k 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
328 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
5k 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
2answers
4k 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
1answer
7k 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?
5
votes
3answers
20k views
Select Entire Column Minus Header Row in 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.
5
votes
2answers
8k 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 ...
5
votes
4answers
4k 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
6k 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': ...
5
votes
1answer
1k 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
621 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
978 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
1answer
947 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
12k 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
1answer
239 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
158 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
8k 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
2answers
4k 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 ...
4
votes
3answers
13k 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:
...
4
votes
1answer
753 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
916 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
246 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
378 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 ...
3
votes
3answers
13k views
Any Excel function that will reverse a string?
Are there any built-in Excel functions that will reverse a string?
3
votes
3answers
364 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
3k 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 ...
3
votes
2answers
217 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
1k 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
11k 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
3answers
2k 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 ...
3
votes
6answers
2k 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
516 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
1k 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 ...
3
votes
1answer
247 views
“The visual basic for applications (vba) macros in this workbook are corrupted and have been deleted”
Recently, I found an Excel 2007 Macro Enable Spreadsheet are keep getting the following error message:
The visual basic for applications (VBA) macros for this workbook are
corrupted and has ...
3
votes
1answer
802 views
Show Excel column filter information in cells
We have a sheet with a huge number of columns and filtering is often used to navigate to the correct data. The problem is that sometimes its not obvious that the filter has been applied , the visual ...
3
votes
1answer
867 views
Random Sampling in Excel
I have an Excel sheet as follows:
NO NAME AMOUNT
1 A 50
1 B 50
2 A 100
2 C 100
3 D 70
3 B 70
4 A ...
3
votes
2answers
3k views
Excel Name.RefersToRange: how to check if Name object refers to a range?
The Microsoft documentation says about RefersToRange:
If the Name object doesn't refer to a range (for example, if it refers
to a constant or a formula), this property fails.
I'm trying to ...
3
votes
3answers
691 views
How can I remove the space between dollar sign and value in Excel cell?
I have some cells labeled as euros and some as dollars.
For some reason the dollar sign is left-justified with a space between it and the value.
The cell is right justified.
How can I get the ...
3
votes
1answer
2k views
How do I create a VBA macro that will copy data from an entry sheet, into a summary sheet by date
I'm trying to create a macro that will copy data from a data entry sheet into a summary sheet. The entry sheet is going to be cleared daily so I can't use a formula just to reference it. I want the ...
3
votes
2answers
263 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 ...
3
votes
2answers
193 views
Edit date field code on multiple Word Documents
I have multiple word documents that have a date field code displaying the current date. I need a way to modify each document so that it shows the CreateDate field instead of Date.
The format for each ...
3
votes
0answers
162 views
Why would an Excel 2010 spreadsheet with VBA lag when opened with other large excel spreadsheets already opened
I've got a relatively small (~100K) Excel 2010 spreadsheet that (via VBA) when opened, closes all worksheets except the start page, checks the user name and cross checks it with a SQL table, bringing ...
3
votes
0answers
169 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
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 ...
