Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Some examples of how to avoid select</p> <p>Use <code>Dim</code>'d variables</p> <pre><code>Dim rng as Range </code></pre> <p><code>Set</code> the variable to the required range. There are many ways to refer to a single-cell range</p> <pre><code>Set rng = Range("A1") Set rng = Cells(1,1) Set rng = Range("NamedRange") </code></pre> <p>or a multi-cell range</p> <pre><code>Set rng = Range("A1:B10") Set rng = Range("A1", "B10") Set rng = Range(Cells(1,1), Cells(10,2)) Set rng = Range("AnotherNamedRange") Set rng = Range("A1").Resize(10,2) </code></pre> <p>You <em>can</em> use the shortcut to the <code>Evaluate</code> method, but this is less efficient and should generally be avoided in production code.</p> <pre><code>Set rng = [A1] Set rng = [A1:B10] </code></pre> <p>All the above examples refer to cells on the <em>active sheet</em>. Unless you specifically want to work only with the active sheet, it is better to Dim a <code>Worksheet</code> variable too</p> <pre><code>Dim ws As Worksheet Set ws = Worksheets("Sheet1") Set rng = ws.Cells(1,1) With ws Set rng = .Range(.Cells(1,1), .Cells(2,10)) End With </code></pre> <p>If you <em>do</em> want to work with the <code>ActiveSheet</code>, for clarity it's best to be explicit. But take care, as some <code>Worksheet</code> methods change the active sheet.</p> <pre><code>Set rng = ActiveSheet.Range("A1") </code></pre> <p>Again, this refers to the <em>active workbook</em>. Unless you specifically want to work only with the <code>ActiveWorkbook</code> or <code>ThisWorkbook</code>, it is better to Dim a <code>Workbook</code> variable too.</p> <pre><code>Dim wb As Workbook Set wb = Application.Workbooks("Book1") Set rng = wb.Worksheets("Sheet1").Range("A1") </code></pre> <p>If you <em>do</em> want to work with the <code>ActiveWorkbook</code>, for clarity it's best to be explicit. But take care, as many <code>WorkBook</code> methods change the active book.</p> <pre><code>Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1") </code></pre> <p>You can also use the <code>ThisWorkbook</code> object to refer to the book containing the running code. </p> <pre><code>Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1") </code></pre> <p>A common (bad) piece of code is to open a book, get some data then close again</p> <p>This is bad:</p> <pre><code>Sub foo() Dim v as Variant Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx") v = ActiveWorkbook.Sheets(1).Range("A1").Value Workbooks("SomeAlreadyOpenBook.xlsx").Activate ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v Workbooks(2).Activate ActiveWorkbook.Close() End Sub </code></pre> <p>And would be better like:</p> <pre><code>SUb foo() Dim v as Variant Dim wb1 as Workbook Dim wb2 as Workbook Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx") Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx") v = wb2.Sheets("SomeSheet").Range("A1").Value wb1.Sheets("SomeOtherSheet").Range("A1").Value = v wb2.Close() End Sub </code></pre> <p>Pass ranges to your <code>Sub</code>'s and <code>Function</code>'s as Range variables</p> <pre><code>Sub ClearRange(r as Range) r.ClearContents '.... End Sub Sub MyMacro() Dim rng as Range Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10") ClearRange rng End Sub </code></pre> <p>You should also apply Methods (such as <code>Find</code> and <code>Copy</code>) to variables</p> <pre><code>Dim rng1 As Range Dim rng2 As Range Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10") Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10") rng1.Copy rng2 </code></pre> <p>If you are looping over a range of cells it is often better (faster) to copy the range values to a variant array first and loop over that</p> <pre><code>Dim dat As Variant Dim rng As Range Dim i As Long Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000") dat = rng.Value ' dat is now array (1 to 10000, 1 to 1) for i = LBound(dat, 1) to UBound(dat, 1) dat(i,1) = dat(i,1) * 10 'or whatever operation you need to perform next rng.Value = dat ' put new values back on sheet </code></pre> <p>This is a small taster for what's possible.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to avoid using Select in Excel VBA
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USProfex
    1. USchris neilsen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to avoid using Select in Excel VBA
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload