excel-vba

VBA, performance, object hierarchy, type structure

I have a some type hierarchy: type A, type B contains field of type A array, type C contains field of type B... (added: I mean type structure, not classes!) I can't use classes by performance reason: type objects copying and creating more fast than the same for classes. But now I need substitute lowest type and than use both types, ol...

How to find a matching row in Excel?

Given A search key --------------------------- | | A | B | C | D | --------------------------- | 1 | 01 | 2 | 4 | TextA | --------------------------- An Excel sheet ------------------------------ | | A | B | C | D | E | ------------------------------ | 1 | 03 | 5 | C | TextZ | | ------------------------------ | 2 |...

MS Access Auto Link Excel Spreadsheets

I have a directory structure where I am managing the requirements of a system with each component of that system having its own directory. the requirements of each component are stored in a excel workbook that has multiple worksheets(# of worksheets are static). I am currently using access as a central location to view the information ...

validation rule Excel VBA

Hi, I have two columns in a spreadsheet called A1 and A2. The data entered can only be either "Y" or "N". However, A1 and A2 cannot have the same entry for the same record. For example, if the value of A1 is "Y" in record#3, then the value of A2 in record#3 must be "N". and cannot be "Y". Also, if A1 in record#3 is "N", the A2 must be "...

vba code formatter

Hi all, Does someone know a free and good code formatter for vba macros? Thanks ...

INSERT data from Excel into SQL DB

I have created an Excel Sheet that does some lookups to format data that needs to be inserted into another table. This Excel Workbook needs to be given to some users that are free to add some new rows, and then need to be able to hit an "Insert Into Database" button and have the records transformed and inserted as new records into a SQL ...

In Visual Basic for Excel 2007, how do I select rows that contain a certain pattern?

I want to write a program in Visual Basic where I look at Column L of a worksheet and search for cells in Column L that contain "123." I then want to select the rows that contain "123" in Column L, copy them, and paste them into a new worksheet. How would I do this? I created a macro, but I'm not sure how to change it so that I can find ...

protecting cells in excel from user but user can allow edit some cells but not all.

i have written a vba code in excel which is accepting data from the user and do some calculations then ofter it will show the result in a same sheet,but i want to restrict the user to edit the cells he can edit cells which are not dealing with vba but he cannot edit the cells those are used by the vba. ...

How to access a constant defined in another Excel workbook?

Question How do I access a public constant defined in another Excel workbook? Setup Workbook.xls My VBA code runs in the current Excel workbook called "MyWorkbook.xls". It has some VBA code in it which reads a file name --"OtherWorkbook.xls in this case-- from cell "A1". Then, the code should read a constant defined in that other wor...

How to loop through Controls and retrieve the names in a list or collection from a form based on SQL Server 2007 in IE7

I have built a macro that formats an already generated SQL Server report. However I was wondering if I could make this even faster by some way bringing up IE first without having to manually do it, fill in the values for the drop down lists, run the report, and choose to export it as an excel file saving it with a dynamic name. However t...

Required field handling Excel VBA

Hi everyone, I have an application I have created in Excel VBA. This application requires a lot of entries from the user. All the fields/cells are required to have values before submission. What will be the best way to check that the users entered values in every single field/cell. I could do simple IF satatements, but that will take for...

excel VBA? How do you loop through an IE CheckboxList and check certain items

I was wondering what would be the code to loop through an IE CheckboxList and check certain Items in the list. Thanks. ...

oAuth client in Office VBA what is difficult ?

I cannot find any oAuth sample code and saw others were desperately searching for it, what's the difficult part to implement an oAuth client in VBA ? ...

Excel Macro: Select cells on condition and use as data validation list

Hi, I'd like to select all cells (except the first one) from column A of sheet B that are not empty, and use it as the list for data validation on a range of sheet A. I already have code to add the validation: Cells.SpecialCells(xlCellTypeFormulas).Offset(0, 1).Select With Selection.Validation .Delete .Add Type:=xlValidateList,...

Checking Excel 2003 data validation

The following bit of VBA will highlight any cells in a sheet with data validation errors: Sub CheckValidation(sht As Worksheet) Dim cell As Range Dim rngDV As Range Dim dvError As Boolean On Error Resume Next Set rngDV = sht.UsedRange.SpecialCells(xlCellTypeAllValidation) On Error GoTo 0 If rngDV Is Nothing Then sht.ClearCircles E...

Using Excel Solver in VB with no cell references?

Hello there, I am writing some visual basic code to solve for a solution. In the end I want it to optimize the solution through iterative method which is exactly what the Excel solver does. However I need it in pure VB. The Excel solver relies on cell references which does not work for me :( My problem has 6 parameters and 1 value to m...

Excel VBA - Using Ranges as Optional parameters for functions?

I'd like to write a VBA function that has a Range as an optional parameter. For instance something like: Public Function testfunc(S As String, Optional R As Range) As String testfunc = S For Each cell In R testfunc = testfunc + cell Next cell End Function I tried the function above, but I get a #VALUE! error. I also tried wrapping the...

How do I copy Data from a excel in to word and keep its formatting

Hi, I have been trying to copy text from excel in to word, and retain the bold formatting on the text, but not the cell. When I try manually copy and paste over a cell the text is copied but not the cell itself. When I try repeat this using vb (as shown below) the cell is also copied over With wrdDoc .Content.Font.Name = "Times Ne...

Add/Delete optional personal form in VBA

What is simplest/best way to achieve functionality in vba in Excel: - button "add person" which will add 4 rows in which user specifies name..., also " - button "delete person" appearing in new added entry - to delete it What is a workflow for doing that? Should I record that first? Should I write VBA only? How to keep the part that c...

RightNow API and Excel

All, Does anyone know how to query a RightNow database from Excel? I'd like to be able to query the RightNow database from an existing Excel file that I have (that also connects to other sources). It looks like ODBC won't work as it doesn't connect to the production database. There is an XML API but I'm not sure how to implement that in...