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... ...
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>' xmlns:xs='<http://www.w3.org/2001/XMLSchema>'><X>24.365</X><Y>78.63</Y></PointN> and get the X & Y values into two separate integer variables. I'm a newbie ...
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...
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...
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...
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 ...
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. ...
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...
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? ...
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...
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...
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...
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). ...
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...
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...
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 ...
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 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 ...
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. ...
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...