vba

Refresh Excel VBA Function Results

Does anyone know how I can get a user-defined function to re-evaluate itself (based on changed data in the spreadsheet)? I've tried F9 and Shift+F9, but those don't work. The only thing that seems to work is editing the cell with the function call and then pressing Enter. Any ideas? I seem to remember being able to do this... ...

How to parse XML in VBA

Hi, I work in VBA, and want to parse a string eg <PointN xsi:type='typens:PointN' xmlns:xsi='<http://www.w3.org/2001/XMLSchema-instance&gt;' xmlns:xs='<http://www.w3.org/2001/XMLSchema&gt;'&gt;&lt;X&gt;24.365&lt;/X&gt;&lt;Y&gt;78.63&lt;/Y&gt;&lt;/PointN&gt; and get the X & Y values into two separate integer variables. I'm a newbie ...

Word Automation: Write RTF text without going through clipboard

I am trying to replace the current selection in Word (2003/2007) by some RTF string stored in a variable. Here is the current code: Clipboard.SetText(strRTFString, TextDataFormat.Rtf) oWord.ActiveDocument.ActiveWindow.Selection.PasteAndFormat(0) Is there any way to do the same thing without going through the clipboard. Or is there an...

Alternating coloring groups of rows in Excel

Hi All, I have an Excel Spreadsheet like this id | data for id | more data for id id | data for id id | data for id | more data for id | even more data for id id | data for id | more data for id id | data for id id | data for id | more data for id Now I want to group the data of one id by alternating the background co...

What is the best way to package and distribute an Excel application.

I've writen an Excel-based, database reporting tool. Currentely, all the VBA code is associated with a single XLS file. The user generates the report by clicking a button on the toolbar. Unfortunately, unless the user has saved the file under another file name, all the reported data gets wiped-out. When I have created similar tools in W...

Excel: list ranges targeted by INDIRECT formulas

We have a few very large Excel workbooks (dozens of tabs, over a MB each, very complex calculations) with many dozens, perhaps hundreds of formulas that use the dreaded INDIRECT function. These formulas are spread out throughout the workbook, and target several tables of data to look-up for values. Now I need to move the ranges of data ...

Zoom for a windows form in C#

Is there an easy way to set the zoom level for a windows form in C#? In VBA there was a zoom property of the form. ...

good directory picker for visual basic macros in MS Outlook (2007)

I wrote a visual basic macro for archiving attachments for Outlook 2007, but did not find a totally satisfactory way for showing a directory picker from the outlook macro. Now, I don't know much about either Windows APIs or VB(A) programming, but the "standard" windows file dialog I see most often in Microsoft applications would seem lik...

Excel VBA: Alternative IDE

I am using excel 2003 and the build in IDE for programming VBA, but a lot of things bug me. Obviously, the build in nature of the VBA IDE is a big pro. Is there a (free?) alternative out there? ...

How do I read/write Person metadata from a Word doc stored in SharePoint using VBA or VSTO?

Scenerio: Document library in SharePoint with column x of "Person or Group" type. From within a VBA macro (or VSTO add-in) we're trying to access the MetaProperty on the document to set/get the user name. Any attempt to access the value via the ContentTypeProperties collection throws a Type MisMatch error (13). The Type property of th...

Best way to test a MS Access application?

With the code, forms and data inside the same database I am wondering what are the best practices to design a suite of tests for a Microsoft Access application (say for Access 2007). One of the main issues with testing forms is that only a few controls have a hwnd handle and other controls only get one they have focus, which makes autom...

Templates In VB

Hi all, I've got some VB code (actually VBA) which is basically the same except for the type on which it operates. Since I think the DRY principle is a good guiding principle for software development, I want to write one routine for all of the different types which need to be operated on. For example if I had two snippets of code like...

Count a list of cells with the same background color

Each cell contains some text and a background color. So I have some cells that are blue and some that are red. What function do I use to count the number of red cells? I have tried =COUNTIF(D3:D9,CELL("color",D3)) with no success (Where D3 is red). ...

Modifying a spreadsheet using a VB macro

I have two spreadsheets... when one gets modified in a certain way I want to have a macro run that modifies the second in an appropriate manner. I've already isolated the event I need to act on (the modification of any cell in a particular column), I just can't seem to find any concrete information on accessing and modifying another spr...

How do I create a status dialog box in Excel

I have created a database report generator in Excel. I am trying to create a dialog box that displays status information as the program runs. When I generate the report, although the dialog box appears, I cannot refresh/update the information it displays. Most of the time, the dialog box only partially appears. I have tried using the .r...

Hiding data points in Excel line charts

It is obviously possible to hide individual data points in an Excel line chart. Select a data point. Right click -> Format Data Point... Select Patterns Tab Set Line to None How do you accomplish the same thing in VBA? Intuition tells me there should be a property on the Point object (Chart.SeriesCollection().Points()) which deals ...

Visual studio automation: Enumerate opened windows upon solution loading.

How to enumerate opened code windows (i.e. those windows where you edit documents) upon solution loading using macros? As you probably know, MSVS remembers opened documents, i.e. when you load solution, IDE will load previously opened files. What I want to do is to perform some actions with those windows upon solution loading. I tried ...

How do I assign a keyboard shortcut to a VBA macro in Outlook 2007?

How can I assign a keyboard shortcut to a VBA macro in Outlook 2007? I know how to create a tool for the macro and put it in a menu with an underscore shortcut. This solution requires me to use a shortcut of ALT-{menu key}-{tool key}. What if I want to assign it to ALT-F12 or something like that? The Visual Basic Editor is assigned to ...

How do I keep RecipientTime when executing MailItem.Move in an Outlook macro?

In an Outlook 2003 macro; Is there a way to move a message between folders without changing the ReceivedTime-header? I currently use the method MailItem.Move. But that automatically sets the ReceivedTime property to the current time, which isn't really what I want. ...

Function Overloading in Excel VBA

I'm using Excel VBA to a write a UDF. I would like to overload my own UDF with a couple of different versions so that different arguments will call different functions. As VBA doesn't seem to support this, could anyone suggest a good, non-messy way of achieving the same goal? Should I be using Optional arguments or is there a better wa...