excel-vba

SQL concatenation inside VBA string

I'm using VBA excel 2003,SQL 2005 to make a sql query call and inside my sql statement I'm using '+' operator to concatenate two strings. dim query as string query = "Select distinct ', '+emailaddress1 " query = query & "from contact " would this work inside vba? My query returns too many records in excel but not in SQL? Please just...

Excel 2003 Get dropdown value in VBA and get the name of the dropdown...no where to be found?

I created a dropdown by dragging the combo box onto my sheet from the UserForm toolbar. I assigned some values to it from some cells in the book. Now I want some vba code to access the selected dropdown item's value in form of a string. My dropdown contains only text. Also how do I find the name of this newly created dropdown...it's n...

VBA in New Versions of Excel

Here is a question for my Father. He has been using VBA in Excel for more than two decades going from Excel 5 to this day where he is using Excel 2002. As he has used VBA in Excel for so long, making extensive use of the ability to record macros, he has continued to invest in Excel as it has been rather stable (in terms of its VBA func...

Returning user-defined types on Excel VBA

Hello there. I've been fiddling with Excel VBA for some time, but I never had any formal training whatsoever. I have the following piece of code on a XLSM module : Public Type ks_solution W As Integer NF As String ID As Integer End Type Public Sub MySub() //does some things MyKSSolution = MyFunction(params) End Sub ...

Downloading and Formatting Web Data with Excel VBA

Hi, I need help creating a VBA macro that downloads closing price data directly from Yahoo Finance's Historic Quotes website and imports the data into an Excel spreadsheet column. Background information: This is the link to Yahoo Finance's Historical Quote database - To download as a TXT file: http://ichart.finance.yahoo.com/table...

Creating and Naming Worksheet in Excel VBA

I have some very simple code that adds a new Worksheet, after the current worksheets, to an Excel document, and then changes its name to one entered in a text box on a userform. Works fine on a new workbook, however in a workbook that has a number of existing worksheets it creates the new worksheet, but does not rename it. This only hap...

locking all the cells of a worksheet Excel VBA

Hi All, I have an excel sheet which as a lot of fields to enter data in. Let's say the total fields I have is 20. Ten of them are locked by default whenever the user opens the workbook. Now, one of the fields is asking the user to enter a password. If the password was "AAA", then five fields (of the Ten locked ones) will be unlocked and...

Excel/VBA: How to paste SQL query with proper string formatting

Hi folks, I've been writing some pretty long SQL queries in notepad and then pasting them into my VBA code as-is and then formatting the multi-line string correctly each line at a time. For example... In my text editor, the query looks like this. SELECT a, b, c, ..., n FROM table1, table2, ...

VBA: How to change the value of another cell via a function?

I'm an Excel VBA newbie. How to change the value of the specified cell via a user-defined function? What's wrong with this code: Function Test(ByVal ACell As Range) As String ACell.Value = "This text is set by a function" Test := "Result" End Function My wish is ... when I type =Test(E6) in cell E1, Excel will display the specifi...

Excel VBA Maniplating a CSV with SQL

This line of VBA code downloads a CSV file from the web and dumps the data into a spreadsheet. Can someone please help me figure out how to tweak the SQL in this code in order to select the "Close" column in the CSV table and insert that column into the spreadsheet? Thank you! Sub test() Dim sqldata As QueryTable Set sqldata = Active...

How to replace every existing values of a column with formula?

My sheet currently has already data. Unfortunately, later I realize that all values in column E should be validated and changed as necessary by calling my own user-defined function and passing the values as the function parameter. For example, just says I have the following data in column E: E1: "This is a text in (E1)" E2: "This is a t...

vba button - find which was clicked

Hi, I have assigned macro to few buttons. How can I find out inside macro which button was clicked? I am doing as user form, where he can input peoples from family: name1: surname1: name2: surname2: |add next member| I wish button to appear always in last row of the last added person. For simpli...

Equivalent of VLookup in VB6

Dear All, I want to use the vlookup function in VB6. what is the code for it? I am currently using this code: WorkSheetName.Application.VLookup(20,WorkSheetName2.Range("A2:B20").Address,2,False) WorkSheetName2 contains the lookup table. WorkSheetName is the sheet that contains a list of values to be looked up for their corresponding v...

DataGrid control in Excel

Hello, I need to use DataGrid control in excel userForm but I can't found it, please help? I use excel 2007 & windows 7 I don't need any thing similar to DataGrid such as spreed sheet control or what ever. Thank you, ...

Transform Excel files to the existing database via the form in Access.

Hello, I has not a lot of experience in VBA programming. What I need - is the possibility to create a simple form to extract some Excel file to the defined table by adding the records. Could you please tell me what function or a set of functions can be used in this case: to open the Excel file, define the spreadsheet, extract data inse...

VBA: How to get the last used cell by VBA code when the last error occured in a Workbook/Worksheet?

I think the title says it all. Eventually, I want to move the cell to the location where the last error occured. Edit: Forgot to say that I'm using Excel 2003. ...

Visual Basic: How to declare an array of array ?

Hi there. I've googled this question several times, but I can't find something that works. I want to declare an array of arrays (arrArr) of unspecified length. Actually, I work with Excel: if the sheet exists, fetch its array and store it in the array of arrays. I've declared this arrArr like this, and the program basically works this ...

DoEvents doesn't do the events... Why?

I'm using DoEvents to force an update of a progress indicator in the status bar (or in some cell in the sheet) as in the example code below. But the screen doesn't refresh, or stops refreshing at some point. The task eventually completes but the progress bar is useless. Why won't DoEvents "do the events"? What else can I do to force a s...

Transfer data from Excel file to Access File using VB6

Dear All, I have an excel file (.xls) with data in the first Sheet that is named "Customers". I also have an Access.mdb database that contains a table called " CustomerDetails". I would like to transfer the data from the excel sheet into the Access database using VB6 code. How can I do that? Thanks in advance, ...

What is the difference between dim and set in vba

Pardon me as am a newbie in VBA, VB.NET. Sometimes I use Dim r as Range r = Range("A1") Other times I use Set r = Range("A1") What is the difference? And when should I use what? Can someone please point me to a tutorial on the difference? ...