How Can I Efficiently Search An Array?

How To Do This Efficiently?
In my interface I have just finished my 2d version. I am now implementing the 3d version.(same thing but in 3 dimensions) I will basically have to use almost the same forms as I did in my 2d application but with a little modifications. So what I did is I just created new forms and copied the controls over(the text boxes and everything) and I am using new global variables to hold the variables to prevent confusion. Now I have almost 30 forms (and global variables for each form). I am beginning to get the feeling that maybe I am doing this inefficiently.. Could anyone please let me know if there is an efficient way to do this.

---

Multidimen Array And Search In This Array
hi,
i had two question :

1.
i want to get 4 variables out of a oracle dB.
this variables (MOTORWAY_ENTRANCE_ID , MOTORWAY_ENTRANCE_NO , NAME and DISTRICT_ID ) should insert in a multidimensional array.
the select statement is the following and works :
my query = "select MOTORWAY_ENTRANCE_ID,MOTORWAY_ENTRANCE_NO,NAME,DISTRICT_ID from MOTORWAY_ENTRANCE where DISTRICT_ID LIKE '09%' AND VERSION_NO = '0' AND Version_END IS NULL"

How can I declare a multidimensional array and insert this variables into the new array ?

2.
the second step is to get all the Motorway_Entrance between two MOTORWAY_ENTRANCE_NO in the array above. for Example between
MOTORWAY_ENTRANCE_NO 20 and MOTORWAY_ENTRANCE_NO 50.
this search has to go in both direction ( up and down between the MOTORWAY_ENTRANCE_NO ).

how can i implement this search in Testpartner (VB )?

Thanks

---

Writing Efficiently From VBA To A Worksheet
Hi.
I’m trying to make a write operation to a worksheet as quick a possible.
I’ve made an array (one dimensional string, but I can use a Variant if necessary), and I want this to be written to a given row, staring in column A. One way to do it is too loop through the array using

Sheet(“sheet 1”).cells(row, i) = array(i)

in the loop. But as far as I know this is not the most efficient way of doing it.

I have also tried using

Sheets(“sheet 1”).range(<proper dimension>) = array

This works, but some of my array elements contain formulas, and these end up being written in the cell rather than the result of the formula (if array(0) contains (“=Sum(A1:A2)” then that text will appear in the cell rather then the sum of A1 and A2).

Anyone?

Thanks for your time
-Terry

---

Efficiently Writing To SQL Table Using VB6
Below is a sub that I am using to write data received from a modbus connection to a PLC. I am using the data to keep a history file in a SQL database. To be of any use the data must be gathered very frequently and written in to database very often. Is there a more efficient way of writing this data to SQL than the method below. It looks like my loop times are around 11 to 12 seconds and need to be 1 to 2.



Private Sub WriteDigitalDataToSQL()

Dim ConnectionOne As ADODB.Connection
Dim rstHistory As ADODB.Recordset
Dim ConnectionString As String
Dim SQL As String
Dim i As Integer
Dim DataType As String
Dim ServerName As String
Dim Database As String
Dim Count As Integer


ServerName = "10.1.1.2"
Database = "WIG"

Set ConnectionOne = New ADODB.Connection
ConnectionString = "driver={SQL Server};server=" & ServerName & ";database=" & Database & ";UID=sa;PWD="
ConnectionOne.Open ConnectionString
Set rstHistory = New ADODB.Recordset
rstHistory.CursorType = adOpenKeyset
rstHistory.LockType = adLockOptimistic

i = 0
Count = 0

If Mod1.Result = 0 Then

Do While i < 480

DataType = "Digitals"

If chkVbDigitalTable(i).Value = 1 Then
SQL = SQL + "Insert into history values (" & "'" & Now & "'" & "," & "'" & DataType & "'," & i & "," & VisualBasicReceiveDigitalsTable(i) & ")"
Count = Count + 1
End If

i = i + 1

Loop


rstHistory.Open SQL, ConnectionOne


ConnectionOne.Close

If rtbLog.Text = "" Then
rtbLog.Text = Now & ": " & "Added " & Count & " digital items to the history file"
Else
rtbLog.Text = rtbLog.Text & vbCrLf & Now & ": " & "Added " & Count & " digital items to the history file"
End If

Else

If rtbLog.Text = "" Then
rtbLog.Text = Now & ": No digital items written...loss of communication"
Else
rtbLog.Text = rtbLog.Text & vbCrLf & Now & ": No digital items written...loss of communication"
End If

End If


End Sub

---

Help With Deleting Efficiently From A Table.
Hi all
I have a program that utilises three tables. Table one is a master that is read from, table two is a temp table where only the required info is written to from the master (and data can be added to the records), and the third is for archiving. I am using an apache database with SQL. The fields from the tables are populated into an MSFlexgrid.
I basically have nine fields per record (so one row of a table). Each record can be updated during the course of it's life. The way I am updating a record (if for example I want to add the word "Completed" to a field within the record), is to delete it from the temp table, then re-insert it using info from the program:

Code:
checklist.Execute ("DELETE FROM checklisttemp_table WHERE (job_time = '" & opschecklist.Text2.Text & "' AND sys_name = '" & opschecklist.Text4.Text & "' AND job_name = '" & opschecklist.Text5.Text & "')")

checklist.Execute ("INSERT INTO checklisttemp_table (job_time, sys_name, job_name, job_status, sign_user, jobname_day)values ('" & edit.Label2.Caption & "', '" & edit.Label3.Caption & "', '" & edit.Label4.Caption & "', '" & edit.Text1.Text & "', '" & opschecklist.Text7.Text & "', '" & edit.Text2.Text & "')")
My problem is, I need a more efficient way of deleting the record before adding it again, as if I have two records that are simular, they will both be deleted.
Unless anyone can think of a better way to do this.
AArrgghh...help.......
Thanks
Lee123

---

Integer To Binary Efficiently
Hi,
I need to do the following as efficiently as possible:

Convert an integer to Binary and put the 1s & 0s into an array.

This is what I have so far:

Code:
' Convert decimal to binary and fill the array
intQuotient = MyInteger

Do While intQuotient > 0
intReminder = intQuotient Mod 2
intQuotient = intQuotient 2

BinaryArray(ctr) = intReminder
ctr = ctr + 1
Loop


How can I do this any faster?
Is there a function to convert Integer to Binary or something?

---

Getting COM Port Response Efficiently In Vb
I want to know how to catch response from COM port without delay and how to check which port is open

---

Getting Data From EXCEL, Efficiently
Hello all!

I have experienced a huge time delay in looping through cells in a worksheet to compare, manipulate, get... data.

So I am wondering what is the most efficient way to retrieve data from EXCEL to manipulate, store, compare, etc... without having to loop through the text?

Would it be to turn the spreadsheet into a recordset to compare to another recordset.

Essentially, that is what I am doing. I am trying to compare data from EXCEL to a recordset I retrieve from ACCESS.

Thanks for helping VB City!

---

Normalising A Database Efficiently.
Ive written some code to normalise a database but it seems to be slow. I've look for some examples on the web and not found any. There is a wizard in access and that seems to be able to work i out pretty quickly. Where am i going wrong ?
Im sure Its just bad programming technique and my lack of knowledge.
I have one large import table of round 30000 records. plus other to import later

tbl_import_temp
Title, Artist, Brand, Format, TitleID,AristID, BrandID, FormatID

My Nomal tables are ...

tbl_titles
TitleID, Title

tbl_Artist
ArtistID, Artist

and so on.....

here is my code......

Code:Function CheckNormalForm(FieldName, TableName)
        Dim RS As New ADODB.Recordset
        Dim I As Integer
        Dim strValue As String
        Dim arrGetRows()
   
        strSQL = "SELECT DISTINCT " & FieldName & " FROM tbl_Import_Temp"
        Set RS = cn.Execute(strSQL)
        arrGetRows = RS.GetRows
        RS.Close
        For I = 0 To UBound(arrGetRows, 2) ' cycle through distinct rows
            strValue1 = arrGetRows(0, I)
            strValue = Replace(arrGetRows(0, I), "'", "''")
            strSQL = "SELECT " & FieldName & "," & FieldName & "ID FROM " & TableName & " WHERE " & FieldName & " Like '" & strValue & "'"
            Set RS = cn.Execute(strSQL)
            If RS.EOF Then ' item did not exist in normal table
                RS.Close
                RS.Open TableName, cn, adOpenDynamic, adLockOptimistic, adCmdTable
                RS.AddNew ' add the item
                RS(FieldName) = strValue1
                RS.Update
            End If
            intID = RS(FieldName & "ID") ' get its ID
            RS.Close ' put the ID in the temp import table to create relationships later
            strSQL = "Update tbl_import_temp SET " & FieldName & "ID = " & intID & " WHERE " & FieldName & " Like '" & strValue & "'"
            cn.Execute strSQL
        Next
        Set RS = Nothing
End Function

can anyone give advice on how else to do this ? We import quite a lot of flat files into this database but once a few have been done the process gets slower and slower. I want to get this faster without using SQLServer. (im on access)

Thanks

---

Scanning Through Excel More Efficiently
Hey guys,

     I'm looking to see if there is a better way to scan through an excel sheet. Right now what I've been doing is:

Code:
For i = 1 To Worksheets(1).UsedRange.Columns.count
For j = 1 To Worksheets(1).UsedRange.Rows.count
If Worksheets(1).UsedRange.Cells(j, i).Text = "mytext" Then
count = count + 1
End If
Next j
Next i

This has proved pretty efficient for me, but there seems to always be someone who has a better way. Any takers?

 

---

Synchronize Multiple Databases Efficiently.
4 companies: One database each. (That means 4 DB's)

They all have a table full of vendor information.

I need to check the consistency of the data in the DB's. That is, I need to make sure that every record is present in every DB. While I would be able to do this by looping through each database, reading the primary key and connecteing to each other database to compare the keys, I was wondering if anyone had any insight on a 'better' way of doing this.

---

Efficiently Assemble Conn In Module Help
I have a connection string public in module. and thay are working fine, code is this:

VB Code:
'in moduleOption ExplicitPublic Const STRING_1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="Public Const STRING_2 = "datadbInventory2.mdb;Persist Security Info=False;Jet OLEDB:Database Password=123;"Public Const STRING_3 = "datacashRegister2.mdb;Persist Security Info=False;Jet OLEDB:Database Password=123;"Public userpath1 As StringPublic userpath2 As StringPublic Function strString1(strUser) As StringstrString1 = STRING_1 & strUser & STRING_2 End FunctionPublic Function String1(strUser) As StringString1 = STRING_1 & strUser & STRING_2 End FunctionPublic Function strString2(strUser) As StringstrString2 = STRING_1 & strUser & STRING_3End FunctionPublic Function String2(strUser) As StringString2 = STRING_1 & strUser & STRING_3End Function 'in formPrivate Sub Form_Load()Dim TmpVal As StringDim net As StringDim loca As String TmpVal = GetSetting("protect", "server", "optserver", "True") 'this is option value If TmpVal = "True" Then  net = "\" & GetSetting("protect", "CompName", "txtserver", "") 'this return a value "\toshiba1"  'userpath1 = String1(net)  userpath2 = strString1(net)  MsgBox userpath2Else  loca = GetSetting("protect", "LocalDrive", "txtclient", "") 'this return a value "C:"  'userpath1 = String2(loca)  userpath2 = strString2(loca)  MsgBox userpath2End IfEnd Sub


1.Any one can give me more efficient way to minimize code into 2 string to use in form (example:conn1 for dbinventory2.mdb and conn2 for cashregister2.mdb)

2.after that I want to put down whole code in module into

Any replay is very much appreciate

best regards,

---

Efficiently Limit A File To 100 Lines
I am developing a small app that will write a line to a file fairly frequently and I'd like to guard against the file getting longer than about 100 lines. Anyone have an efficient method to do that?

---

Stucked---how To Write This Loop Efficiently
I have a one dimension dynamic array say, my_array(), for example, this time, it's been populated at run time as
my_array(0)=6
my_array(1)=7
my_array(2)=8

obviously, the actual value and length of array can vary at run time
I want to write a select case such as


select case my_number
case 1 to 6
'do something here...
case 7 to 13
'do something here..
case 13 to 20
'do something here...
'hope you could see the pattern here...

how do I write this more effectively and generically

---

Moving Shapes Efficiently - For Games
Hi there,
I've been trying to develop small games in vb like pacman, tetris etc but I think I may be moving the shapes around the form in an inefficient manner. For instance to move a shape from side of the form to the other I would write the following in a timer procedure (interval set at say 50ms):

Private Sub timer1()
  shape1.left = shape1.left + 25
End Sub

Although this works, it doesn't really make the shape move very smoothly. Is there another way that I can move a shape around on a form??

---

Best Way To Populate Multiple Combo Boxes Efficiently??
Hi everyone,

I have a form which is going to act as a "bulk entry" sheet for the daily work statistics of the users of my application.

It contains a picture box containing a row of controls which repeats 50 times. One of the controls is the "action" box which will be a combobox populated from a database table. Whenever I have used comboboxes like this in the past I have always populated them in the dropdown event. However as this control is repeated and the drop down action is therefore potentially repeated 50 times, it surely doesnt make sense to hit the db and populate the combo for each instance of the control that is used does it??

Can anyone suggest a more efficient/better practice way of doing this please. Should I be considering something like a disconnected recordset (I'd then only hit the db once and the data to populate each instance of the action control would already be there) or is there an even better way to do this ???

---

Does Windows Allocate Memory For VB Programs Efficiently?
Well, first I apologize for asking a similar question in another thread - but I feel I did not express myself well enough in that one - and this is a really important issue for me and I would really appreciate any feedback.


This started because I fired up the task manager in XP and tried to watch the memory allocation/deallocation for my program. I was not happy with what I saw (looked like my program had a memory leak) so I went through my code and made sure all of my object references were set to Nothing, when I was done with them. Then I set all my form references to nothing and I even put code in the form terminate event as an extra check that my forms were destroyed.


None of this made any difference so I started doing some experiments with very simple programs. I still noticed the following:


1.) When forms are destroyed, the memory is not freed.


2.) Message boxes result in an increase in memory usage with no corresponding decrease.


3.) In my bigger program I noticed "greedy" behavior. In other words, if I created and destroyed a bunch of objects, memory would increase and not decrease, but then if I created the objects again, memory would NOT increase. It was almost like the program was holding on to memory in case it may need it again later. I feel for a true memory leak, this would not happen - memory allocation would just keep increasing.


Does any of this make any sense? Could setting objects to Nothing in VB code not be enough? Could a system call be needed to force the program to release memory?


Could the garbage collector be on strike?


Thanks in advance.

---

How To Download Images Efficiently With WebBrowser Control In VB.NET?
I want to download all the images inside an HTML page. I have created a WebBrowser control; navigate to a URL; and cast the webpage as mshtml.HTMLDocument:

Code:WebBrowser.Navigate(txtURL.Text)
Dim HTMLDoc As mshtml.HTMLDocument = WebBrowser.Document

After the call to the Navigate function, all the images in that page should be downloaded. My problem is that I don't know how to extract and save the images directly from HTMLDoc. Currently I am using another way to do the task: get the image src from HTMLDoc and use Internet Transfer Control to download the images. This seems stupid since the same picture is downloaded twice.

Some early post suggested to save the whole webpage in order to get the images. But I don't want to do that, coz in the source file the ".src" attributes within the IMG tags will be changed - I want to extract some information from the original ".src" attributes.

Anyone has a solution to this? Many thanx!

---

Many Checkboxes To Control What Data VBA Outputs- How To Code Efficiently?
I have the following code to create headings on a worksheet based on what the user has selected. What's worse is that there is identical code that actually outputs data on the sheet. Can anyone suggest a more efficient way to do something like this?

If I could cycle through an array of checkbox values perhaps this would speed things up but even then, each if statement would be a bit different. Maybe there is someway to use bitwise AND and ORs in the if statements??

Any ideas would be appreciated- I'm guessing there are many better ways to structure this.

Thanks,

Dave



Code:
If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Angular Speed Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Angular Speed Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Angular Speed USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Angular Speed LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Angular Speed Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Angular Speed Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Angular Speed USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Angular Speed LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Angular Speed Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Angular Speed Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Angular Speed USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Angular Speed LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("U Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("U Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("U Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("U Angle LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("D Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("D Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("D Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("D Angle LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("R Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("R Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("R Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("R Angle LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("L Angle Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("L Angle Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("L Angle USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("L Angle LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("UD Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("UD Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("UD Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("UD Running Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Running Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Running Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Running Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Running Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Running Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Running Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("UD Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("UD Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("UD Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("UD Inrush Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Inrush Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Inrush Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Inrush Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Inrush Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Inrush Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Inrush Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("UD Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("UD Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("UD Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("UD Stall Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("DU Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("DU Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("DU Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("DU Stall Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("RL Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("RL Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("RL Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("RL Stall Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("LR Stall Current Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("LR Stall Current Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("LR Stall Current USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("LR Stall Current LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("Vertical Overrun Drift Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("Vertical Overrun Drift Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("Vertical Overrun Drift USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("Vertical Overrun Drift LSL")

If (FormMain.ckValue) Then setCellValueAndMoveRight ("Horizontal Overrun Drift Value")
If (FormMain.ckBool) Then setCellValueAndMoveRight ("Horizontal Overrun Drift Pass/Fail")
If (FormMain.ckUSL) Then setCellValueAndMoveRight ("Horizontal Overrun Drift USL")
If (FormMain.ckLSL) Then setCellValueAndMoveRight ("Horizontal Overrun Drift LSL")



Edit by Moderator:
Please post Excel questions, in the Excel forum.

Please use the [vb][/vb] tags when you post your code. Edit or reply to this post to see how.

Thank you.

---

Efficiently Loading Stock Price Data Into Excel
Hi everyone!

I refer to Peter's article at http://www.eggheadcafe.com/articles/20020108.asp
(quote: "The neat thing about this is that by simply changing the SQL Statement and supplying the name of ANY text file that resides in that folder, you can use the filesystem object (for example) to get a list of the files in the folder, and then read each file into a separate recordset object by simply looping through your array of stock price filenames and dynamically changing the SELECT statement to match on each iteration.")
and I'd like to ask the following:

I am looking for an efficient way to load stock price data into Excel from text files (or any other database stored locally , i.e. I'm not talking bout web queries) for testing purposes.
To be able to measure portfolio risk correctly, the data has to be loaded on a daily basis(*), one by one.

Does anyone have a sample file (e.g. for Excel VBA) which allows to learn the basics?
Would SQL, ADO or an Access database be the most appropriate solution for this?

(*) As I want to avoid recalculation of the same values (indicators, stops, risk, etc.) over and over again, I consider keeping all the calculated data in a database and calculate only one row (the newly added) in Excel.

I hope I explained everything clear enough.

I also post an excellent article on "VBA: Efficiency and Performance". - Enjoy!

Thanks for help and time.
Any help is appreciated.

Regards,
Thomas <tompf@gmx.at>

---

Can You Search An Array?
For example, if I have an array that contains:

["Apple", "Grape", "Cherry"]

Is there some way to search this array instead of looping through the indexes?

-Jeff

---

How To Search An Array
i have vreasted an arra y as a text file i need to use a msgbox or something for the user to search the array

and i have another function where i search then delete the element from the array im really really lost with this

thanks

---

Array Search
if I was to load a binch of text to an array, would there be a simple way to search the array and return true if it was found and false if not. and if so how would I write such a masterful piece of work

---

Search Array
I was wonderinghow i woudl go about searching an arrray for a specific word. If the word is there do somthign if not exit teh sub or function.
So lets say i have the words

Green, Blue, Red, Yelow, Orange, Black

And we will say we use red as an example. Hoe woudl i search the array for that word to find out if it exixsts or not?

---

Search Array
i have a csv file (2 columns) as below, what i need to do i read the data in search column 1 and if it matches a value a user has placed in a textbox then put the corresponding value in column 2 into another textbox?

csv format

1,asd
2,ggffg
33,dsfsdfs

---

Search For A Value In Array
A textbox and a text file. I have read the textfile into an array (the text fiel is only 1 column with 1500 lines), when the applications runs the user inputs an account code into the textbox and when the LostFocus action is initiated the value in the textbox needs to be verified against the array. If it matches then do nothing, otherwise throw up an invalid code message. Does anyone know how to check the textbox value against the array??

---

Search An Array
Can I anyone tell me the fastest way to
search a 2-dimensional array.
I have a 100 x 100 array and I only have to search
the values on the first column.

---

How To Search An Array
Hi,

I have just started programming in visual and I'm trying to create an array of 10 elements. A user will enter in 10 numbers and then I need to search through the array for numbers that are greater than 0 but I'm hitting loads of errors. Can someone help me?

---

Array Search
Two Questions:

1.
I am returning a recordset of information that I want to store in a variant array. The RecordCount property, for the recordset, tells me I have RecordCount of 17. When I go to make the variant array equal to what is being stored in the recordset,the UBound of the variant array is now only showing a count of 13. Is there a specific reason as to why I am loosing information from the transfer??

2.
I have a large recordset of information (RecordCount = 53000); this will be stored in a variant array. I need to be able to loop through the array, and compare a separate specific piece of information (ie. Client ID) with that stored in the array. Would the best way to do this be by performing a Binary Search?? If not does anyone have any other suggestions??

Thanks,
Sam

---

Search An Array
Hello,
first of all, i hope my question isn't too simple for this board.
In my program there's an ARRAY, where users can input any items (numbers).
Now i have to search this ARRAY for that item. If it exists, it's no problem to find and select it. But if it doesn't exist, the item should handed over to another variable. And that's my problem:

Dim intArray(yyy) as Integer
Dim intMissed as Integer
Dim intAnyNumber as Integer
Dim intI as Integer
Dim intMAX as Integer

For intI = 1 to intMAX
If intAnyNumber = intArray(intI) Then
any command ...
Else
intArray(intI) = intMissed
End If
Next intI

This doesn't work correctly, because the item is always unknown, if it's
not at first postion of the Array. I'm working with eVB 3.0 (the IsMissing-
Operator is missed here!).
Does anybody know a better solution for this ?
Thanks in advance

Carsten Lutz

---

Search Data Array
I am looking for some direction, I am a newby so please bear with me.
I need to be able to enter an account number, and search my data array, find the correct record and display, in label controls, the other elements of the record, name, interest rate, balance etc...
I have the array loaded, that seems to be working fine, I just dont know where to get started on my search . Can someone at least point me in the right direction?
Thanks
Dale S

---

Search For 0 In A Byte Array
Whats the fastest way to search for (get the index of) 0 in a byte array.

Say i wanna find the position of the first NULL byte starting at element 20 of the array..
like InstrB (20, myByteArray, 0) - but you can't search for vbNull using instrB...

Can't it be done without loops ?

---

Sorting An Array And How To Search Through It ?
ok , first things first. i have to say that without this forum i would not be able to keep up in my VB class. thanks so much.
i am a bloody beginner in my 4th week (12th hour) of vb, stuck with a teacher who barely speaks english, no textbooks and a workload that is beyond me.
moan , complain , hehe i know ...
i hope that someone can simplify things. i have not much knowledge of vb terms and commands and have to resort to this forum and msdn.com for help as the help feature is not available on my computer (dont ask)

now while i have used the search function of this forum extensively i haven t come up with an idea for a search function i need to implement.
our first assignment is a simple address book. you add a name and a number and the complete list should be displayed in a listbox alphabetically. <-- i am currently looking up sorting but i am honestly a bit puzzled by it still.
maybe someone can point me in the right direction.
also what i need to implement is a search function that spits out a single entry or group of entries ( like all that start with A ) from the address book including name and number.

what i have sofar and please dont laugh as it was hard work figureing out :

Code:
Option Explicit
Private list() As String
Private i As Integer
Private search As String
Private result As String
Private Sub cmd_Click(Index As Integer)
Select Case Index
Case 0: 'add entry
Dim space As String
space = " "
If (i = 0) Then ReDim list(0) Else
ReDim Preserve list(i)
list(i) = (i + 1) & ". " & txtname.Text & " " & txtnumber.Text
MsgBox "New Entry added to addressbook", vbInformation, "New Entry"
i = i + 1
Case 1: 'delete entry
MsgBox "test", vbCritical, "test"
Case 2: 'show all entries
Dim a As Integer
For a = 0 To UBound(list)
List1.AddItem list(a)
Next a
Case 3: 'search function
search = InputBox("enter name or number to look for", "search")
Case 4: Unload Me
End Select
End Sub

if i could ask my teacher i would do so but he is a jerk that when asked whether we could talk through the homework so we could overcome the parts where the class is stuck just answered:
no , we dont have time for this. i rather bombard you with as much new stuff as possible so at least something will stick.

oh well .. thanks for any suggestions or advice/help

edit:
do i have to loop through the array to find all entries that start with a certain letter , store each find in another array , loop through that one to sort again by second letter and so on .. is there a function that would make life easier ? also to list all entries alphabetically could i loop through the array searching for a specific ascii value in the first letter and increasing the ascii value by 1 on each loop ? would that make sense or is that too clumsy ?

---

Fastest Way To Search In An Array?
I have a very large array. Anyone know the fastest way to find something in the array. The array is 0 based and lets say the array has an upper bound of 185 and somewhere in there there is a value of 45. What is the fastest way to find the bound of the array that contains a value of 45?

I have tryed for x=lbound(array) to ubound(array) but that is not fast enough. Mabe something like this would work:

for each something in something

but I don't know how to use it.

Whats the fastest way to search through an array?

Thanx in advance!

---

Search Member In An Array
Is there an function which can search whether an item is in an array, instead of using a loop. similar with "InStr"

---

Search A BYTE Array
Is there a function that can search a byte array for a series of bytes or a string?

---

Intelligent Array Search
Hi, can someone suggest a better way to do this? Need to search a text array for 55 known strings and return the data that is offset (usually the next item). Each string exists only once, so a standard loop with Select Case checking each item against the 55 values isn't very efficient. Following is what I came up with. Any better (more elegant/efficient) way to do this? Thanks.

VBAhack


VB Code:
Option Explicit Dim astrR() As StringDim iP As Integer Sub SearchStuff()    Call LoadArray(astrR) ' with parsed HTML from website    Debug.Print SrchTxt(strTxt1, 1)    Debug.Print SrchTxt(strTxt2, 1)    Debug.Print SrchTxt(strTxt3, 1)    ' etc., etc., etc.    Debug.Print SrchTxt(strTxt55, 1)End Sub Private Function SrchTxt(strTxt As String, iOffset As Integer) As String    Dim i As Integer    For i = iP To UBound(astrR)        If InStrB(astrR(i), strTxt) > 0 Then Exit For    Next i    SrchTxt = astrR(i + iOffset)    iP = i + iOffset + 1End Function

---

Quick Search A Big Array
i thought i would ask before i jump into this little project of mine.

i was thinking of loading an array at form load with 110,000 words.
now my question is, what would be the quickest way of searching through the array.
example: i want to list all words beggining with the letters "po".
the array is already sorted and somebody told me a binery search would be best but i dont know how to do that.

thank you.
casey.

---

String Search - Array
question on arrays that I could use some help understanding please...

Can someone write sample code on how you would go about this.... I'd like to sort out 7 7 and 8 8 and 9 1, etc. from this text. I have a split function using " " as a divider using array(4) and (5), but I am unsure as to how to tell it the starting point and ending point. The text START will always come before the line of significance and the word END will follow..

jsdsdfj;]
asdfsdafsdafsda
sadfds
asdfsadfdsdafsadfasdfdasd
sad
asdfsdf
asdfdsa
START
JSLDFJ JLSDJF JSAJDFL JLASDJF 7 7 JSLAJFDL JASLDJF
SJALJF SADF SADFSDF SADFSDAF 8 8 ASDFSA ASDFSDA
sfsfsd asdf sdfsadf sadfsafsdf 9 1 safasdf asdfdsadf
END
SFSADDFSADF
ASDF
ASDF
SDAF
ASDF

---

Faster Way To Search An Array?
Anyone know how I can make this faster?

As the array grows, the speed decreases....

Code:
'Is the account number in the array arrAccountsToNote yet?
Dim intTimes As Integer
intTimes = 0
blnNewAccount = True
Dim blnFound As Boolean
blnFound = False
While blnFound = False And intTimes < UBound(arrAccountsToNote, 2) + 1
If arrAccountsToNote(0, intTimes) = adoNoteRecordset!AcctNbr Then
'Found
blnFound = True
blnNewAccount = False
End If
intTimes = intTimes + 1
Wend

---

Binary Search Array
I am new to VB (6) and to this forum. So here goes. I need help with figuring out arrays. Here is the project I have for school, that I've worked for nearly 6 hours on! and haven't gotten very far with it:
Write a program that accepts an American word as input and performs a binary search to translate it into its British equivalent. Use the following list of words for data, and account for the case when the word requested is not on the list.

American British American British

attic loft ice cream ice
business suit loung suit megaphone loud hailer
elavator lift radio wireless
flashlight torch sneakers plimsolls
french fries chips truck lorry
gasoline petrol zero nought

This is driving me nuts

I've only gotten this so far:

Private Sub cmdFind_Click()
Dim i As Integer
Dim result As String, American As Integer, British As String
'Find the equivalent British word for an American word
'A number from 1 thru 12 is entered for the American word
picResult.Cls
If American >= 1 Or American <= 12 Then
Open "C:/temp/BRITISHWORD.TXT" For Input As #1
'First line of file is equal to #1 on the American list
For i = 1 To 12
Input #1, wordBritish(i)
Next i
Close #1
End If
End Sub

The forum has a list numbered 1 thru 12 of the American names, with a text box for entry of one of these numbers; a command button labled Find British Word; and a pic box for the results of the search.
I am totally confused. Help!

---

Array Binary Search
anybody know of a good binary search for arrays?

Thanks

---

Search Array Keywords In VB 6.0
I am making a program that searches an array of company names for a keyword that the user enters in a text box. But the search is much to specific - i.e if a user is trying to find Microsoft they must type in "Microsoft", but the search will give no result if they were to type in "microsoft". So I need to know how to generilize the search, so that if I were to search "micro" it would find "Microsoft" as an available awnser.

Here is my current code:

Code:


Private Sub Command1_Click()
Dim result(1) As String

Dim allSites As String

Dim iCountVar As Integer

Dim search_rec As Integer

Dim search As String

Dim website(3) As String

website(0) = "Leighnachris"
website(1) = "Microsoft"
website(2) = "Google"
website(3) = "Dell"

For iCountVar = 0 To 3
allSites = allSites + website(iCountVar)
Next

For search_rec = 0 To 3
If website(search_rec) = Text1.Text Then
result(0) = website(search_rec)
result(1) = search_rec
MsgBox (result(0) + ", index:" + result(1))
Else
End If
Next

End Sub




As you can see my search box's name is Text1 and the search button is command1. The forms name is also just Form1.

Please help!
warmly,
Kai001

---

Search Array, No Match Then Add (Need Some Help)
Trying to search for a "line" in the array. If the "line" isn't found, then I want to add it into the array. Any help would be appriciated!

Code:
Dim PlyrNArray()
Dim PlyrCount
PlyrCount = 0

Sub AddPlayer(line)    
    Dim compare
    Dim found
    Dim index
    found = False
    index = 0
    If Len(line) > 0 Then
        Do
            ReDim Preserve PlyrNArray(Index)
            compare = StrComp(line, PlyrNArray(index),1)
            If compare = 0 Then
                found = True
                Exit Do
            End if
            If compare <> 1 then
                found = false
            End If
            index = index + 1        
        Loop Until index => PlyrCount
        
        If found = false Then
            PlyrCount = PlyrCount + 1
            ReDim Preserve PlyrNArray(plyrcount)
            PlyrNArray(PlyrCount) = line
        End If
    End if
End Sub


I dont mean to be lazy or anything but I have no clue to why this doesnt work.... Thought I typed everything in correctly. BTW this is vbscript (not vb)

- Identity



Edited by - identity on 8/2/2004 9:57:34 PM

---

Search Array For String ?
I have a text file which contains 500 names....1 name per line.

I have spilt the text file in to an array with 500 elements.

How do I search the array for a particular string.

For example the name "JOHN"

MARK
CRAIG
....
.......

---

Array Search Problem
I'm writing a program to look through a Word document looking for specific words to capitalize. I have the list of words that need to be capitalized in a listbox array. What I have works fine when I specify an element of the array, but not when I try to loop through it. I've tried both For/Next and Do/While. Here's the relevant bit:

    For i = 0 To ((lstCap.ListCount) - 1)
    With objWord.Selection.Find
            .Text = lstCap.List(i)
            .Replacement.Text = StrConv(lstCap.List(i), vbProperCase)
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = True
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
    End With
    Next i
    objWord.Selection.Find.Execute Replace:=wdReplaceAll

For some reason, it only does the last element in the array.

---

Using Items In An Array To Perform A Search
G'day all,

(Using vba in Excel 2002) I have a procedure in the UserForm Initialise event whichlooks at a sheet with, among other timesheet data, time units and time dollars. I need to filter through the data to find which employees worked what hours and at what cost, then import all relevant staff times and dollars into the UserForm. I have procedures to sort the data so that all employee times are grouped per employee, then to use subtotals to find the gross amounts. No problems so far.

It is the next step where my code fails.

Here is the code that isn't working:
Code:
For i = 1 To intCountNames
ReDim Preserve aryFirstNames(m)
Dim strSearchCriteria As String
strSearchCriteria = aryFirstNames(m) & " Total"
intCount = Cells.Find(What:=strSearchCriteria, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 3).Value
aryunits(m) = intCount
intCount = Cells.Find(What:=strSearchCriteria, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 4).Value
aryAmount(m) = intCount
m = m + 1
Next i
The procedure stops at the 5th line (the first time where the Cells.Find is used), and the error message is

"Run-time error '91':

Object variable or With block variable not set"

What I assume is happening is that if a staff member is not found (as in the array aryFirstNames(m)), then the procedure halts.

Any ideas on what is wrong?

My code is a bit long, but I will post it should your advice need to rely on the lead up to this bit of code.

Thanks,

Mitchy

---

Search String Not Found In Array
I'm trying to teach the function below to return "DNP" if the player didn't appear on the scoreboard that it is searching through. No matter what I've tried, the cell in Excel just returns #VALUE!. I've attempted to use Excel functions and VB functions, but none seem to work. Any help would be greatly appreciated. I'm brand new to VB and I'm trying to make my life a little easier in the work I have to do in excel.

In the code below, ScoreboardA and FieldA are just named ranges within the Excel file.

Function Thursday(Player As String)

Dim Scoreboard As Range
Dim Field As Range
Dim Score As String
Dim Row As Integer
Dim Column As Integer

Set Scoreboard = Range("ScoreboardA")
Set Field = Range("FieldA")

Row = WorksheetFunction.Match(Player, Field, 0)
Column = 3

Score = WorksheetFunction.Index(Scoreboard, Row, Column)

Thursday = IIf(WorksheetFunction.IsError(Score), "DNP", Score)

End Function

---

Array/Search/If Function Question
Hi,

I am trying to find if one of three possible car registrations appears in a string of gibbberish and if yes then extract it. I can do it one at a time but i would like to be able to use an array to find any of the three reg's at once.

Example vehicle:

Robert ManFE04 RHU FE04RHU FE 04 RHU

Example String:
Find one of the three reg's within string:

EMBER LATHOM VEHICLE RENTAL LT CAR 43N121519 FE04RHU 01/08/06-31/08/06

i am currently doing it one at a time using the following:

=MID(L1,FIND(Vehicles!E4,L1),7) ~ to find just one of the reg's

I would like to use a macro that could search on a number of different cars within a number of different strings to use to create totals for each car.

Any help would be greatly apprecieated.

gpemby

---