Ask me anything about Microsoft Excel - Page 26 - Other Topics Forum - Discuss Pop Culture, Arts and Entertainment

zomg,
I got a question myself. As previously stated here, I am working on a Excel/Access Tool.

I got a macro that reads out roughly 2500 datasets.
The thing is, I open a new connection and recordset every single time I get one new dataset, so this might or might not slow down the process significantly.

Would it drastically improve, if I'd open one connection at the beginning and use that throughout?

Firstly, did you get the copying table thing to work?

I'm not 100% what you mean you should only ever being using one 'connection' then using

Code:

rs.open sql,conn
rs.close
rs.open newSQL,conn
rs.close 
etc etc

Have you looked at using asynchronous queries so they can all run at the same time? I am doing a similar thing, here is some code I am using:

Code:

    Dim ADODBconn As ADODB.Connection
    Dim rs0 As New ADODB.Recordset
    Dim rs1 As New ADODB.Recordset
    Dim rs2 As New ADODB.Recordset
    Dim rs3 As New ADODB.Recordset
    Set ADODBconn = New ADODB.Connection
    ADODBconn.Open getCurrentADODBConnection

    rs0.Open sql0, ADODBconn, adOpenStatic, adLockReadOnly, adCmdText + adAsyncExecute
    rs1.Open sql1, ADODBconn, adOpenStatic, adLockReadOnly, adCmdText + adAsyncExecute
    rs2.Open sql2, ADODBconn, adOpenStatic, adLockReadOnly, adCmdText + adAsyncExecute
    rs3.Open sql3, ADODBconn, adOpenStatic, adLockReadOnly, adCmdText + adAsyncExecute
    
   Do While rs0.state <> adStateOpen And rs0.state <> adStateClosed 
and rs1.state <> adStateOpen And rs1.state <> adStateClosed 
and rs2.state <> adStateOpen And rs2.state <> adStateClosed 
and rs3.state <> adStateOpen And rs3.state <> adStateClosed 
       ' lalallala waiting for queries to execture
   Loop

I'm pretty sure this isn't what you are talking about though, so can you paste the function you are working with?

In answer to your original question of whether it would be significantly slower, assuming you go through one one row at a time in excel, build a query and execute vs going through all the rows and executing at the end you could try something like

Code:

Dim sqlArray() As String
Dim currentRow as long
ReDim Preserve sqlArray(0)

for i = 1 to range("A65000").end(xlup).row
            sqlArray(i-1) = "INSERT INTO yourTable (col1,col2) values ('" & range("A" & i) & "','" & range("B" & i) & "')
            ReDim Preserve sqlArray(i)
next i 

    On Error GoTo unsaved
    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection
    conn.BeginTrans
    For i = 0 To UBound(sqlArray)
        conn.Execute sqlArray(i)
    Next i
    conn.CommitTrans
    Exit Function
    
unsaved:
    conn.RollbackTrans
    conn.Close
   msgbox "error!"

obviously thats just an example but hopefully it is kind of what you were looking for

Quote

02-08-2012 , 05:14 PM

#627

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by d10

If you know all 3 side lengths you can use either

=DEGREES(ASIN([opposite length]/[hyp]))

or

=DEGREES(ACOS([adjacent length]/[hyp]))

oh right, that explains what S-O-H C-A-H T-O-A meant, haven't done trig for like 10 years

Quote:

Originally Posted by Yeti

i need to write a macro that adds more data to an existing graph (from a table already existing in the workbook), chooses colours, writes the labels etc. that's the short version. assume this is possible in VBa? i haven't tried yet, will have a play around later with the record function. i got a book and have still not really delved into it yet

Most definitely although its quite fiddly, I've done it before I will try dig up my old code but if not once you get started paste the code here and ill take a look

Quote

02-08-2012 , 05:22 PM

#628

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by noname6520

Code:

Public Function CheckName(name As String) As Integer
    Dim i As Integer
    For i = 1 To 5
        If Worksheets("Sheet1").Range("B" & i) = name Then
            MsgBox (name & " is located on row " & i)
            CheckName = i
        End If
    Next i
End Function

anyone know why I am still getting a #Name result?

thanks in advance

zomg this is the solution you helped me with in the other subforum I am still getting the same #Name result I was getting before tho

This works fine for me, i think the issue is you are putting the code in the wrong place, you need to right click on a sheet tab > view code, then on the far left (in the project explorer) right click and go Insert > module then paste it there. There's some screen shots in post #388 if you don't follow what i'm saying

Quote

02-09-2012 , 04:08 AM

#629

d10

Carpal \'Tunnel

Join Date: Jan 2005 Posts: 7,513

Quote:

Originally Posted by Yeti

assume this is possible in VBa?

The more I use vba the more I realize it's capable of almost anything. It's way more powerful than I thought it was when I first started using it. It can easily handle your project.

Quote:

Originally Posted by zomg

oh right, that explains what S-O-H C-A-H T-O-A meant, haven't done trig for like 10 years

I haven't either. Google helped me out there. I guess the TOA implies there's also a 3rd way to write the formula.

Quote

02-09-2012 , 05:42 AM

#630

subandi

Carpal \'Tunnel

Join Date: Jun 2007 Posts: 6,836

Quote:

Originally Posted by zomg

Ok not sure if this will work in OO, if it doesn't let me know and i'll download it and try there

Assuming your first range is in cells A1:B4 and your second range is in cells D1:E4 so the formula would start in E2 then be copied down to E4 and E1 would be xxx

Code:

=VLOOKUP(D2,$A$1:$B$4,2,0)-MAX(B1:$B$4)

great, thank you very much! I got it to work with a little tweaking; had to change the "," for ";".

Also I just removed all the "$" as they seem to do nothing? I can't see a difference between

Code:

=VLOOKUP(D2;A1:B4;2;0)-MAX(B1:$B$4)

and

Code:

=VLOOKUP(D2;A1:B4;2;0)-MAX(B1:B4)

both give the same (correct) output - what was the $ supposed to do?

Code:

player1	1000		player1	XXX
player2	500		player2	-500
player3	300		player3	-200
player4	50		player4	-250
dfgdfg	20		dfgdfg	-30
dfgdfg1	3		dfgdfg1	-17

so as far as I can see my problem is solved, thank you again!

on to my next question (lol):

Is it possible to have the player list automatically obfuscated with a command or something in OO/excel?

Ideally I would like to push a button and make the left part look like the right part

Code:

player1	XXX		XXXyer1	XXX
player2	-500		XXXyer2	-500
player3	-200		XXXyer3	-200
player4	-250		XXXyer4	-250
dfgdfg	-30		XXXdfg	-30
dfgdfg1	-17		XXXdfg1	-17

I *think* something similar could be achieved with vlookup again,
by having a list of all player names and their "aliases" looking like this:

Code:

player1	XXXyer1
player2	XXXyer2
player3	XXXyer3
player4	XXXyer4
dfgdfg	XXXdfg
dfgdfg1	XXXdfg1

and then having vlookup look up the alias from that list?

I wouldn't know how to implement that though, what I *think* should be possible is:

Code:

A	B	C	D	E	F		G	H
player1	1000		player1	XXXyer1			player1	XXXyer1
player2	500		player2	XXXyer2	-500		player2	XXXyer2
player3	300		player3	XXXyer3	-200		player3	XXXyer3
player4	50		player4	XXXyer4	-250		player4	XXXyer4
dfgdfg	20		dfgdfg	XXXdfg	-30		dfgdfg	XXXdfg
dfgdfg1	3		dfgdfg1	XXXdfg1	-17		dfgdfg1	XXXdfg1

where column G,H is my static list of players names and their aliases and column E is a vlookup thing that for example in E2 looks for the phrase in D2, then looks for the same phrase in column Gx and shows me what is written in Hx right?

thanks again for any help!

Quote

02-09-2012 , 10:43 AM

#631

subandi

Carpal \'Tunnel

Join Date: Jun 2007 Posts: 6,836

- figured it out, thanks

Last edited by subandi; 02-09-2012 at 10:43 AM. Reason: pretty proud at myself haha

Quote

02-09-2012 , 10:48 AM

#632

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

tops, well done.

In answer to your $$ question, assuming OO is the same as excel the $ signs make a cell reference Absolute rather than relative. E.g. if in cell A1 you put "=B1" then cop and paste that cell down 5 rows it would automatically change to "=B6" (same for copying it across and it changing to C6, D6 etc) a $ in front for the letter stops it from changing when you move it across columns and the same for numbers and rows so "=$B1" copied across three columsn would stay as "=$B1"

The reason you didn't notice any difference in your formula is because you happen to have the same the same data and the same rows if you had:

Code:

player1          player4
player2          player2
player3          player3
player4          player1
player5          player6
player6          player5

The vlookup wouldnt work because in cell E2 it would be A1:B6 but then in cell E3 it would be A2:B7 then A3:B8 when you copy it down.

hope that makes sense!

Quote

02-10-2012 , 01:01 PM

#633

Harruin

grinder

Join Date: Jul 2005 Posts: 564

I've tried googling this but haven't had any luck so far.

I have code that loops through files and then prints their respective PDF files through shellexecute. Here is the code:

Code:

 result = ShellExecute(0, "PRINT", filename, "", "", vbNormalFocus)

That part works just fine, but it prints them completely out of order. I know they are being called in correct order due to an excel worksheet it fills out, but it prints in whatever order it feels like. I've tried the system wait time function, but that seems to do nothing. I've found some code but it's wildly more complex than anything I've used, and it might not even work in excel. I was wondering if you had a better idea how to attack this problem?

Quote

02-10-2012 , 02:39 PM

#634

DeezNuts

veteran

Join Date: Sep 2002 Posts: 3,016

Very simple (probably) Excel question.

Trying to get a quarterly (or monthly) report where I drop in a date at a certain point along a row and the rest of the cells in the row populate with the end of the quarter. I think it would be something with EOMONTH. I tried with IF and YEAR/MONTH etc but it got way too ugly and complex.

For instance, I would manually input the quarter ending for a a release date for a movie at a certain point (say 12/31/12) and the cells to the left would populate with 3/31/12, 6/30/12, 9/30/12, etc and the cells to the right would populate 3/31/13, 6/30/13, etc.

No macros or VB please.

Great thread, thanks in advance.

Quote

02-10-2012 , 03:06 PM

#635

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by Harruin

I've tried googling this but haven't had any luck so far.

I have code that loops through files and then prints their respective PDF files through shellexecute. Here is the code:

Code:

 result = ShellExecute(0, "PRINT", filename, "", "", vbNormalFocus)

Have you tried looking at ShellAndWait?

Quote:

Originally Posted by DeezNuts

If the starting date is in D1 (12/31/12) then in C1 put

Code:

=MONTH(EDATE(D1,-3)) & "/" & DAY(EOMONTH(D1,-3)) & "/" & YEAR(EDATE(D1,-3))

copy that to B1, A1

and in E1 use the same formula but do +3

Quote

02-10-2012 , 03:26 PM

#636

DeezNuts

veteran

Join Date: Sep 2002 Posts: 3,016

Quote:

Originally Posted by zomg

If the starting date is in D1 (12/31/12) then in C1 put

Code:

=MONTH(EDATE(D1,-3)) & "/" & DAY(EOMONTH(D1,-3)) & "/" & YEAR(EDATE(D1,-3))

copy that to B1, A1

and in E1 use the same formula but do +3

You rock, thanks!

Quote

02-10-2012 , 04:19 PM

#637

Harruin

grinder

Join Date: Jul 2005 Posts: 564

Quote:

Originally Posted by zomg

Have you tried looking at ShellAndWait?

I have, and I still get nearly random results. I have a worksheet that has written on it with the filenames of 5 files, let's say a,b,c,d,e in A1,A2, and so on. I then run this code:

Code:

Do While Cells(i, 1) <> ""
 result = ShellAndWait(ShellExecute(0, "PRINT", "C:\toprint\" & Cells(i, 1).Value, "", "", vbNormalFocus), 5000, vbHide, PromptUser)
 i = i + 1
Loop

The print order comes out a, b, e, d, c.

Quote

02-11-2012 , 08:00 AM

#638

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Quote:

Originally Posted by zomg

Firstly, did you get the copying table thing to work?

I'm not 100% what you mean you should only ever being using one 'connection' then using

Code:

rs.open sql,conn
rs.close
rs.open newSQL,conn
rs.close 
etc etc

Have you looked at using asynchronous queries so they can all run at the same time? I am doing a similar thing, here is some code I am using:

Code:

    Dim ADODBconn As ADODB.Connection
    Dim rs0 As New ADODB.Recordset
    Dim rs1 As New ADODB.Recordset
    Dim rs2 As New ADODB.Recordset
    Dim rs3 As New ADODB.Recordset
    Set ADODBconn = New ADODB.Connection
    ADODBconn.Open getCurrentADODBConnection

    rs0.Open sql0, ADODBconn, adOpenStatic, adLockReadOnly, adCmdText + adAsyncExecute
    rs1.Open sql1, ADODBconn, adOpenStatic, adLockReadOnly, adCmdText + adAsyncExecute
    rs2.Open sql2, ADODBconn, adOpenStatic, adLockReadOnly, adCmdText + adAsyncExecute
    rs3.Open sql3, ADODBconn, adOpenStatic, adLockReadOnly, adCmdText + adAsyncExecute
    
   Do While rs0.state <> adStateOpen And rs0.state <> adStateClosed 
and rs1.state <> adStateOpen And rs1.state <> adStateClosed 
and rs2.state <> adStateOpen And rs2.state <> adStateClosed 
and rs3.state <> adStateOpen And rs3.state <> adStateClosed 
       ' lalallala waiting for queries to execture
   Loop

Code:

Dim sqlArray() As String
Dim currentRow as long
ReDim Preserve sqlArray(0)

for i = 1 to range("A65000").end(xlup).row
            sqlArray(i-1) = "INSERT INTO yourTable (col1,col2) values ('" & range("A" & i) & "','" & range("B" & i) & "')
            ReDim Preserve sqlArray(i)
next i 

    On Error GoTo unsaved
    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection
    conn.BeginTrans
    For i = 0 To UBound(sqlArray)
        conn.Execute sqlArray(i)
    Next i
    conn.CommitTrans
    Exit Function
    
unsaved:
    conn.RollbackTrans
    conn.Close
   msgbox "error!"

obviously thats just an example but hopefully it is kind of what you were looking for

I havent tried the copying table thing, because I got "distracted" by other parts of the project.

What I am doing (I cant post the function because I am not at work right now and it is long) is basically:
I got 4 input variables. I open a new Connection then a new recordset and query for what I am looking for (input are those 4 variables).

One of those variables changes every time, so I query roughly 200 times for a single worksheet (where I fill in the data).

I was thinking of open a connection in the main sub, then input it in every function and sub I use along the way (I open a new connection every time for this). I am not sure, if this works though.

I got some other ideas to improve performance, because right now I need roughly 2 minutes for those 200+ outputs.
Running the queries at the same time might help though and I will try this. Problem is that I would need to declare all those different recordset variables, while I dont know how many of those are needed.

Would it be possible to create an array and store the number of recordsets I need?

Quote

02-13-2012 , 06:09 AM

#639

d10

Carpal \'Tunnel

Join Date: Jan 2005 Posts: 7,513

I have a workbook with 2 sheets. Similar data in each sheet but structured differently. Every time a cell is changed in the first sheet I have a macro to put that data where it belongs in the second sheet. It works good as long as the user is only changing one cell at a time. It can't handle if they copy->paste a block of cells though. Is there a way to cycle through a block of pasted cells and handle them individually (preferable)? Or if not can I disable copy and pasting?

Quote

02-13-2012 , 10:42 AM

#640

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by d10

Assuming you are using worksheet_change event you can do

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then
        For Each ce In Target
            MsgBox ce.Address
        Next
    End If
End Sub

or disabling copy/paste

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.CutCopyMode = False
End Sub

although I have disabled copy/paste before and in general people get annoyed by it

Quote

02-13-2012 , 10:45 AM

#641

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by Spurious

......
Would it be possible to create an array and store the number of recordsets I need?

Not really.

To answer the rest of your Q i'd need to see the actual code. If you don't need the results frim the recordset and are just inserting data then the transaction block will definitely increase performance

Quote

02-13-2012 , 11:08 AM

#642

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

I improved the performance by only querying once and going through the recordset.

Works fine for now.

Quote

02-13-2012 , 12:59 PM

#643

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Quote:

Originally Posted by Spurious

I improved the performance by only querying once and going through the recordset.

Works fine for now.

Actually, it is now super slow again. I dont get why it takes seconds to perform a rs.movenext line.

Quote

02-13-2012 , 01:24 PM

#644

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Do you define the cursor type? if you are just reading each one try

rs.Open sql, conn, adOpenForwardOnly

edit: adOpenStatic may be faster, i'd try them all (there's 0,1,2,3,4 i believe) and see

Last edited by zomg; 02-13-2012 at 01:33 PM.

Quote

02-13-2012 , 01:38 PM

#645

Spurious

Carpal \'Tunnel

Join Date: Aug 2006 Posts: 18,593

Tried it, didnt make it faster :/.

It happened from one moment to another. It might be my computer.

Quote

02-13-2012 , 04:15 PM

#646

noname6520

veteran

Join Date: Feb 2008 Posts: 3,414

Hi to preface, this function gets two values a temperature and a pressure
It is then supposed to loop though a data table matching the pressure to the columns on row 2 and the temperature to rows in column 2

Once it figures out between which rows and columns the temperature and pressure are it is supposed to interpolate linearly between the upper and lower bounds and find the actual value that way.

I am not looking for somone to debug my code or find errors in the math, mostly just wanna know if there are any glaring syntax mistakes or glaring logical mistakes.

Here is the function

Code:

Public Function z0calc(Treduced As Double, Preduced As Double)
    Dim P1 As Double
    Dim P2 As Double
    Dim T1 As Double
    Dim T2 As Double
    Dim R1 As Integer
    Dim R2 As Integer
    Dim C1 As Integer
    Dim C2 As Integer
    Dim Xmid1 As Double
    Dim Xmid2 As Double
    Dim Trow As Integer
    Dim Pcol As Integer
    
    Trow = 3
    Pcol = 3
    Do
        If Worksheets("Sheet2").Range(Cells(2, Pcol)) >= Preduced Then
            P1 = Worksheets("Sheet2").Range(Cells(2, (Pcol - 1)))
            P2 = Worksheets("Sheet2").Range(Cells(2, Pcol))
            C1 = Pcol - 1
            C2 = Pcol
            Exit Do
        End If
        Pcol = Pcol + 1
    Loop While Not IsEmpty(Worksheets("Sheet2").Range(Cells(2, Pcol)))
    Do
        If Worksheets("Sheet2").Range(Cells(Trow, 2)) >= Treduced Then
            T1 = Worksheets("Sheet2").Range(Cells((Trow - 1), 2))
            T2 = Worksheets("Sheet2").Range(Cells(Trow, 2))
            R1 = Trow - 1
            R2 = Trow
            Exit Do
        End If
        Trow = Trow + 1
    Loop While Not IsEmpty(Worksheets("Sheet2").Range(Cells(Trow, 2)))
    
    Xmid1 = ((Treduced - T1) / (T2 - T1)) * (Worksheets("Sheet2").Range(Cells(R2, C1)) - _
    Worksheets("Sheet2").Range(Cells(R1, C1))) + Worksheets("Sheet2").Range(Cells(R1, C1))
    Xmid2 = ((Treduced - T1) / (T2 - T1)) * (Worksheets("Sheet2").Range(Cells(R2, C2)) - _
    Worksheets("Sheet2").Range(Cells(R1, C2))) + Worksheets("Sheet2").Range(Cells(R1, C2))
    z0calc = ((Preduced - P1) / (P2 - P1)) * (Xmid2 - Xmid1) + Xmid1
End Function

Thanks in advance

Quote

02-13-2012 , 04:29 PM

#647

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

My only question is why are you doing this in VBA?

I created a table where cells B3:B12 are t1,t2....t10 and cells C2:L2 are p1,p2...p10

then to find any value in the range C3:L12 you can use:

Code:

=INDEX(C3:L12, MATCH("t2",B3:B12,0), MATCH("p4",C2:L2,0))

I made a screenshot for you cause thats kind of confusing

Quote

02-13-2012 , 04:53 PM

#648

noname6520

veteran

Join Date: Feb 2008 Posts: 3,414

Quote:

Originally Posted by zomg

My only question is why are you doing this in VBA?

I created a table where cells B3:B12 are t1,t2....t10 and cells C2:L2 are p1,p2...p10

then to find any value in the range C3:L12 you can use:

Code:

=INDEX(C3:L12, MATCH("t2",B3:B12,0), MATCH("p4",C2:L2,0))

I made a screenshot for you cause thats kind of confusing

Hi thanks for the response. Two things:

1. The main reason is that my school assignment is to right a function in vba
2. The part I posted is just part of a much bigger function and I suppose Id like to keep all the mechanism of the overall process hidden from the spreadsheet.

like I said tho the main reason is that I have to write it in vba cus thats the assignment.

Quote

02-13-2012 , 05:58 PM

#649

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

OK, if it's for an assignment then i'd put more effort into using intrinsic variable names and commenting your code. Also you don't need range(cells()) unless you are referencing more than one cell, you can just use cells.

I would also define the worksheet

Dim dataWS As Worksheet
Set dataWS = ThisWorkbook.Sheets("Sheet2")

then instead of

T1 = Worksheets("Sheet2").Range(Cells((Trow - 1), 2))

you would use:

T1 = dataWS.Cells(Trow - 1, 2)

I also prefer using for/next rather than do/until something like:

Code:

    lastColumn = dataWS.Cells(2, 100).End(xlToLeft) + 1 ' use + 1 so that it won't cause an error if the last column is in fact >= pressure
    For Pcol = 3 To lastColumn
        ' find the first column that is greater than or equal to entered pressure
        If dataWS.Cells(2, Pcol) >= Preduced Then Exit For
    Next Pcol
    If Pcol = lastColumn Then
        ' error could not find pressure
        z0calc = 0
        Exit Function
    End If

You only really need to store Pcol, Trow because everything else is just referencing that or that - 1

Quote

02-13-2012 , 07:09 PM

#650

CrazyEyez

Carpal \'Tunnel

Join Date: Sep 2003 Posts: 8,669

zomg,

When working with sheet names, instead of defining sheet variables I use the "code name" that you can see in the VBA Project Explorer window. So rather than

Worksheets("Sheet2").Range....

I use

Sheet2.Range....

Which works regardless of what the sheet is named. (I learned this early on, I think from a jwalk book.) But I pretty much never see this technique in other code I encounter. Is there a drawback to it that I'm not aware of? I've been doing it for years and I can't recall coming across a problem.

Quote

Page 26 of 120

First

6 16 21 22 23 24 25 26 27 28 29 30 31 36 46 76

Last

Post Reply Subscribe

...

Page 26 of 120

First

6 16 21 22 23 24 25 26 27 28 29 30 31 36 46 76

Last