Open a .NET WPF window or Windows Form from VBA code.
Hi, Can anyone give me the syntax required to call a .NET assembly from VBA code. I want to open a WPF window or Windows form from an Access form. Malcolm ...
vba
Can a vba function in excel return a range?
I seem to be getting a type mismatch error when trying to do something like this: In new workbook: A1 B1 5 4 Function Test1() As Integer Dim rg As Range Set rg = Test2() Test1 = rg.Cells(1, 1).Value End Function Function Test2() As Range Dim rg As Range Set rg = Range("A1:B1") Test2 = rg End Function Adding =...
Is there a native excel class which allows a range to be copied and sorts/filters applied?
I have a range I'd like to arbitrarily sort and filter using vba. I don't, however, want it to affect the worksheet. I'd like to essentially copy the range into some native class that supports filtering and sorting (so i don't have to reinvent the wheel) and use that class to return a result to calling code. Are there any classes I can...
What will happen with Office VBA?
The company I work at is run on Excel sheets. Several of those sheets have some form of VBA code embedded. I'm doing some maintenance on them, but it feels really dated. What's gonna happen to Office VBA? Why hasn't Microsoft released an embedded .NET macro language for Office? ...
Select a set of cells in one column based on value in another
Hi, I have a big set of data in excel of the following form A B 1 stuff1 6 stuff2 3 stuff3 1 stuff4 1 stuff5 7 stuff6 3 stuff7 2 stuff8 . . . . . . 5 stuffn and what i would like is some vba code that will select all the cells in B that have a "1" in column A - I will be using this set to do some tasks in...
SharePoint Lists, GetListItems, XML, and VBA - I just want to crossreference!
I have a SharePoint list with, among other things, two columns that I care about; in Excel-ese, I want to match value X in column 1, and return the corresponding value in column 2. I'm able to use a variant of code at http://guruj.net/node/63 to retrieve the information (I think), so I think my problem focuses on navigating XML in VBA wi...
How do I avoid run-time error when a worksheet is protected in MS-Excel?
The code snippet below changes the data validation state of a cell and runs when the Excel-2003 worksheet is unprotected. However, when I protect the work sheet the macro doesn't run and raises a run-time error Run-time error '-2147417848 (80010108)': Method 'Add' of object 'Validation' failed I have tried wrapping the code wi...
How can I call ActivateKeyboardLayout from 64bit Windows Vista using VBA
Running VBA under XP I was able to call ActivateKeyboardLayout to switch my input language from English to another language. However, this no longer works under Vista64. Any suggestions or workarounds? The code that used to work under XP was similar to the following: Private Declare Function ActivateKeyboardLayout Lib "user32" ( _ ...
Programmatically set DLL search path in VBA macro
The problem I have a word template which uses VBA's Declare statement to link to a dll, whose path can be determined within the VBA macro I want to delploy this to the users %APPDATA%\Microsoft\Word\STARTUP directory I DON'T want to permanently change the user's PATH environment variable (temporarily would be OK, but this doesn't seem ...
How do I copy a range into a temp workbook and return a reference to it with a vba function?
I have the following which errors on the "rTemp.Value = vaTemp" line. What am I doing wrong here? Am I on the right track? Function CreateTempRange(rSource As range) As range ' Declarations Dim rTemp As range Dim vaTemp As Variant Dim wsTemp As Worksheet Dim wbTemp As Workbook ' Open temp worksheet Set wbTem...
Select a Node in a TreeView with VBA
I have a TreeView within a UserForm in Excel. When a Node is selected from the TreeView, a ListBox is populated with data. When an item in the ListBox is double-clicked, a separate UserForm is shown which allows the user do to stuff. Once the user returns back to the TreeView UserForm, I want the Node that was selected previously to be h...
copy Word document contents without using clipboard (VBA)
I was wondering how to avoid using Windows clipboard, when you want to "replicate" multiple sections of a Word document (using VBA in macros) Why to avoid? Because we're using Word on a server, in a multiuser environment (I know that it is officially frowned upon) Otherwise, this would be easily accomplished with Selection.Copy and Sel...
How do I copy a range in vba?
I'm trying this but its giving me a type-mismatch. Is there another way to do this? I'm assuming rSource is not coming from the active sheet. Also, I only need values. Edit: The rSource is expected to have rows and columns. It will also be a single continuous area. Edit: Once this operation is complete I should be able to filter each ...
TreeView populates on its own and crashes Access
Hello everybody. My problem is a tree view in my Access 2002 application. It is already populated, when I open the form. Which is strange, because the only code that adds the nodes is bound to a button. Also, only part of the data is shown and when I switch between tabs () the data inside the tree view duplicates/doubles. When I then...
Thread.Sleep() in VBA
Is there an equivalent to Thread.Sleep in access- VBA? ...
Trouble Creating Excel add-in from macro.
I've created two VBA macro functions that worked fine when used in the current workbook. I saved as .xla and imported as an add-in. I then found and activated the add-in through the menus in excel but when I use the functions I get the #NAME error and a description of "The formula contains unrecognized text". I'm stuck. ...
How come my Macros are triggered by events in other word documents.
I am executing code on the WindowSelectionChange event in Microsoft Word. How come when I open another document, that does not have this macro referenced in it, the code is still being called on WindowSelectionChange. I do not have the macro stored in the default template, it is stored in a template not referenced by my other word docu...
Excel Macro - Comma Separated Cells to Rows
Hi, I have have the following data in excel: a, b, c d e f, g h i with each row, representing a row and in one cell. I would like to convert it to: a b c d e f g h i I am using the following macro, but I can't get the autosize to do an insert, instead of overriding the cell values. Any help is appreciated. Sub SplitCells() ...
Why does the VBA Editor open on its own sometimes?
I've created a small script in Outlook 2003 VBA that watches for new appointments, and sets them to tentative and no reminder as I create them. However, I now find that seemingly at random, the VBA editor will open itself. It doesn't happen when I actually use the new script, but it did happen this morning when I un-hibernated my lapto...
Excel Macro - Comma Separated Cells to Rows Preserve/Aggregate Column
I had a similar question answered Here There is a slight twist to the scenario and hoping the macro can be changed slightly. Any help is appreciated. Based on this Data: <- A (Category) -> <- B (Items) -> 1 Cat1 a,b, c 2 Cat2 d 3 Cat3 e 4 Cat4 f, g I need...