Excel VBA question

[vestax20]

Hi, Using Excel XP VBA

I just want to know if its possible to have a sub procedure receiving lets say six arguments. These are arguments to perform a search on a list. These six arguments may or may not be all there. For example a person may want to search by name and leave the other arguments blank. Is it possible for a procedure to take the arguments that dont have blanks and have defaults for the arguments with blanks???


Man I dont think this is very clear. What I want to do is develope search form that searches a list inside an excel worksheet with different criteria (5 in all). I have never done this before and I`m looking for a way to program this. What I though of doing and hope to find a better was was to pass the first filter on the list and copy the data to another worksheet. After that the other four arguments do the same one by one to the remaining list. Is this understandable? Did anybody do something like this before??

Thx for the help

Simon

 

[SkipVought]

Hi,

You can use optional arguments.

Sub OptionalArgs(strState As String, Optional strCountry As String = "USA")
. . . 
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[vestax20]

thx for the reply skip. I`ll try that


And for the search engine. Any tips on how to create an efficient one?


Thx

 

[vestax20]

Can anybody here give me any tips on creating a search enfine? I have a list inside a worksheet and I want to search through it with a possibility of five different criteria on a userform.

Is there anything that could help me?? I dont want to program each possibility but have something more dynamic..

Thx

 

[SkipVought]

Sorry I missed the second question,

How 'bout using AutoFilter. Set the condition on each of the 5 columns.

Or, a bit more involved, set you list up as a Named table.
1. Use Data/Get External Data and format a query with your 5 criteria.
2. then macro record editing your query
3. modify the recorded code to accept your 5 values

;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[vestax20]

Hi Skip I like where your heading and sory for the delayed response. Do you think I could get this to work?

The thing is that my table increases in size with every patient entered. I have a named range which automatically adjusts to the new additions.

Remenber that my search values are gotten from a userform. The user searches with a form. Do you think you caould give me a deeper explaination of what I have to to to get this working please???

I really appreciate the help.

 

[vestax20]

and how do I use data/get external data if the data is in the same workbook???

Thx again

 

[xlbo]

This is what I get when I recorded doing it - I have a range named "MyRange"

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=W:\Knowledge\Geoff B\Excel\Development\Query Same WB Ex.xls;DefaultDir=W:\Knowledge\Geoff B\Excel\Developme" _
        ), Array("nt;DriverId=790;MaxBufferSize=2048;PageTimeout=5;")), Destination:= _
        Range("F1"))
        .CommandText = Array( _
        "SELECT myRange.Area, myRange.Value" & Chr(13) & "" & Chr(10) & "FROM `W:\Knowledge\Geoff B\Excel\Development\Query Same WB Ex`.myRange myRange" & Chr(13) & "" & Chr(10) & "WHERE (myRange.Value>20)" & Chr(13) & "" & Chr(10) & "ORDER BY myRange.Area" _
        )
        .Name = "mQuery"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

What I would do is assign the SQL string to a variable like

mSQL = "SELECT myRange.Area, myRange.Value FROM `W:\Knowledge\Geoff B\Excel\Development\Query Same WB Ex`.myRange myRange"

then use a select case statement to check which options have been selected

If Textbox1.text = "" then
WhereClause1 = ""
else
WhereClause1 = "myRange.Fieldname1 = " & textbox1.text
end if

Do the same for all the options and string 'em together like

mSQL = mSQL & "WHERE " & WhereClause1 & WhereClause2
etc etc

You'd need some kinda logic to determine where the ANDs would need to go but shouldn't be too hard. In effect, you would be generating a dynamic SQL query over a range name in the workbook

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vestax20]

Man thx Geof but this seems very complicated to me.

I thought it would be more simple.


Wouldn't there be a more simple way to develope a search engine??

I aggree that sql would have to be the most efficien way but its also very dificult to program. Is there any other way to do this??

Thx a lot for the response and I hope you could help me out. I really need to find a solution to this problem. Thx


Simon

 

[vestax20]

Hi, simple question. Working woth Excel XP VBA


I have a number comprised of 4 characters and 8 digits ex

aaaa44044444

the seventh digit corresponds to the first number of the month part of a date. I want to validate this number. It cannot be anything but one or two or five or six. You may be wondering why the five and six. Well its the way to differenciate male or female. A normal date corresponds to a male ID and a 5 or 6 date which corresponds to 0 and 1 are femail IDS.

Anyway what I want to do is put 0,1,5,6 in a list in order to validate later. Any ideas? I know in C++ you could use define. Thx for the help.