Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>While this code pretty much belongs in CR rather than SO, as I have a lot of time in my hands, I've decided to at least post a few things about your code.</p> <p>===</p> <p>A few tips:</p> <ol> <li><p>Avoid <code>.Select</code> and <code>.Activate</code> as much as possible. Recording macros is a good start to VBA, but the first big step is leaving the "comfort zone" provided by these properties. In the beginning, they're good, but they're bound to create problems in the long run.</p></li> <li><p>Read up on the following "basic" procedures: copying/pasting/inserting ranges, creating/deleting sheets, and determining the last rows/columns of a sheet or range that has relevant data. These three are your bestfriends. By learning these three by heart, you can manipulate a lot in Excel VBA.</p></li> <li><p>After (2), start learning how to dimension variables and/or objects. Setting the jargon aside, this is basically just akin to giving each important thing you're working on "nicknames". Say you're working on 3 sheets. You don't want to keep on referring to <code>ThisWorkbook.Sheets("Sheet1")</code> and so on and on. You'd much rather want <code>Sh1</code> or <code>Sh2</code> instead.</p></li> <li><p>Learn how to bunch similar procedures together using <code>Union</code>, <code>With</code>, or the like. This goes hand in hand with (1) above. You'll see an example later on this.</p></li> <li><p><code>Application.ScreenUpdating</code> - one of the best time-shaving tricks in Excel VBA.</p></li> </ol> <p>Now, a few samples:</p> <p><strong>(1) Avoiding <code>.Select</code> || Learning to use the very nice <code>.Copy</code> one-liner</strong></p> <p>This part...</p> <pre><code>Range("A1").Select Selection.Copy Columns("F:F").Select ActiveSheet.Paste </code></pre> <p>...can be reduced to:</p> <pre><code>Range("A1").Copy Range("F:F") </code></pre> <p>From four lines to just one. And it's much more readable. The second code snippet aboves basically reads, "Copy A1's value to the whole F column". Note that that's actually quite memory intensive, as in Excel 2010, you're actually pasting to a million and more rows with that command. Better be specific, like <code>Range("F1:F1000")</code>.</p> <p><strong>(2) Bunching commands together</strong></p> <p>Bunching commands together in "written" VBA is different from the way you do it in macros. Since macros are recorded, everything is based on real time modifications. In "written" VBA, you can specify an action that will allow you to apply a single action on multiple objects. Let's say for example you want to delete Columns A and C while shifting all relevant data to the left.</p> <p>When recording a macro to do this, you can select both A and C and delete them at the same time. However, most beginners take the safe path and record the deletion of columns <em>one at a time</em> which--while safe--is highly counterintuitive. Selecting both before deleting is the best option.</p> <p>In written VBA, the second method above is a massive no-no (or at least, it's not the norm). Unless there's a specific and necessary reason, bunching similar commands together is the convention as it both eliminates error to a large degree and is not resource intensive.</p> <p>In your code...</p> <pre><code>Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Delete Shift:=xlToLeft Columns("E:E").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Columns("G:S").Select Selection.Delete Shift:=xlToLeft </code></pre> <p>... is such a pain to read. We don't know why there are two deletions there, we don't know for sure where the data in Column S originally was, etc, etc. In instances like this, determining ahead of time the ranges you want to delete and executing the deletion is the perfect way.</p> <p>Let's assume for example's sake that you want to delete the columns A, C, E, and F to O. A neat approach like follows will pull this off quite quickly and efficiently.</p> <pre><code>Union(Range("A:A"),Range("C:C"),Range("E:E"),Range("F:O")).Delete </code></pre> <p><code>Union</code> is one of your early bestfriends. As with set notation in math, ranges you specify are put together into a set of ranges together and are actioned upon at the same time (in this case, <code>.Delete</code>d at the same time). Since the default shift is to the left, we can altogether remove the <code>Shift:=xlToLeft</code> line (another nifty VBA fact).</p> <p><strong>(3) <code>With</code> - one thing you cannot live without</strong></p> <p>At this point, you might be thinking, what about multiple actions on these ranges? We've only done single actions on multiple ranges and not the other way around. This is the point where <code>With</code> comes in. In this context, <code>With</code> will be used only on <code>Ranges</code> but it can be used on almost anything in VBA. Objects, ranges, external applications, etc. I will not delve on this, but suffice to say that using <code>With</code> is like using an anchor on something you want to work on with a few procedures.</p> <p>In your code, we find...</p> <pre><code>Columns("C:C").Select Selection.ColumnWidth = 8.29 With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Rows("1:1").Select Selection.RowHeight = 30 With Selection .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With </code></pre> <p>... which can be reduced to:</p> <pre><code>With Columns("C:C") .ColumnWidth = 8.29 .HorizontalAlignment = xlCenter End With With Rows(1:1) .RowHeight = 30 .WrapText = True End With </code></pre> <p>Basically, we did two things here. First, we anchored on Column C, and did two actions on it: set the column width, then the horizontal alignment. After anchoring to Column C and modifying it, we change the anchor to the whole of row 1 and we modify its height and set it to wrap text to cell width. From 24 lines, we've reduced that macro block to just 8 lines. How's that for brevity? :)</p> <p>Why did I make without the other lines? As in the previous example (<code>Union</code>), we can make do with some lines that are either the default value anyway or are not modified. There will be exceptions to these, but they will be far and few and are a bit off your level for now. You'll get there.</p> <p><strong>(4) Creating/modifying sheets and avoiding <code>.Activate</code>, and a touch on dimensions</strong></p> <p>One of the pitfalls of beginners in VBA is that they use <code>ActiveWorkbook</code>, <code>ActiveSheet</code>, and <code>.Activate</code> a lot. This is not bad per se, but it's not good either. It's convenient to use, but it will cause a myriad of headaches if you incorporate it in really complex subroutines and functions.</p> <p>To combat this, we first go into the idea of dimensioning or qualifying your objects. This is done by first declaring a keyword and then a data type. I will not delve into this further, as there are lots of VBA tutorials you can read for this, so I'll just point out some important ones.</p> <p>Let's say you're working on two <strong>open</strong> workbooks. We can create a "nickname" for each of them so you can refer to them without having to type whole lines of reference.</p> <pre><code>Dim SourceWbk As Workbook Dim TargetWbk As Workbook </code></pre> <p>The two lines above read as, "<code>SourceWbk</code>/<code>TargetWbk</code> is my nickname and I am dimensioned as a workbook, so I'll be expecting to be referred to a workbook". Now that we've created dimensions for them, we can point them to what they will stand for.</p> <pre><code>Set SourceWbk = ThisWorkbook Set TargetWbk = Workbooks("I AM THE MASTER REPORT") </code></pre> <p>Note the "=" here. Now, we've basically declared that from now on, <code>SourceWbk</code> is going to refer to the workbook containing this code, and <code>TargetWbk</code> is going to refer to the <strong><em>open</em></strong> workbook named "I AM THE MASTER REPORT". Now let's look at the simple act of copying a sheet from <code>SourceWbk</code> to <code>TargetWbk</code>.</p> <pre><code>SourceWbk.Sheets("Sheet1").Copy After:=TargetWbk.Sheets("Sheet1") </code></pre> <p>Looks familiar? That's because this is pretty much the same as this recorded block of your code:</p> <pre><code>Sheets("Fresh Agent Leads").Select Sheets("Fresh Agent Leads").Copy After:=Workbooks( _ "MSS Call Routing Master List.xlsx").Sheets(1) </code></pre> <p>Now, you can go one step further and name the sheets themselves, then copy them. Example follows:</p> <pre><code>Dim FAL As Worksheet 'Expects a worksheet. Dim LastSheet As Worksheet Set FAL = SourceWbk.Sheets("Fresh Agent Leads") Set LastSheet = TargetWbk.Sheets("Sheet1") 'You can use a number index or specific name FAL.Copy After:=LastSheet </code></pre> <p>At this point the code is very, very short and sweet already. No hassles, and the only effort you actually need is to remember what the "nicknames" refer to. Take note that there are specific words you should <strong>NOT</strong> be using as variable names. As much as possible, make it personalized but reasonable. Simply naming a sheet as <code>Sh</code> is good but it gets you nowhere in a file with 100 sheets each with different purposes.</p> <p><strong>(5) The <code>Application</code> Trickbook</strong></p> <p>In Excel VBA, there are a few things you can manipulate to increase the efficiency of your code. After all is said and done, a macro is just a repeated action. Running a recorded or a written one both will take you through the actions. <code>.Select</code> will select specific ranges and you'll see them get selected. <code>.Activate</code> will do the same, more or less. <code>.Copy</code> will show you those "ants" and the highlights they leave behind. All these contribute to a longer and often sloppy visual execution of the code. Herein steps the <code>ScreenUpdating</code> "trick".</p> <p>Mind you, it's not really a trick. Most people consider them highly important parts of their code, but their inclusion into "layman" VBA modules are nonetheless helpful. One of the best practices is setting <code>Application.ScreenUpdating = False</code> at the beginning of a subroutine then setting it back to <code>True</code> at the end.</p> <p><code>ScreenUpdating</code> will "freeze" your screen, making everything happen without you seeing them. You won't see items getting copied or ranges getting selected. You won't see closed workbooks being opened and closed. While this only affects Excel when you call it, it's nonetheless invaluable.</p> <p>A quick and dirty list (do not use this as an absolute reference!) of <code>Application</code> tricks:</p> <ul> <li>.ScreenUpdating (False/<strong>True</strong>): Eliminates the visual updating of Excel when <code>False</code>. Absolutely necessary when copy-pasting or deleting rows.</li> <li>.Calculation (<strong>xlCalculationAutomatic</strong>/xlCalculationSemiautomatic/xlCalculationManual): Similar to the <code>Formulas &gt; Calculation Options</code> ribbon function, setting this to Manual will suspend all calculations. Highly recommended especially when you're updating ranges that are depended on by loads of <code>VLOOKUP</code> or <code>INDEX</code> formulas.</li> <li>.EnableEvents(False/<strong>True</strong>): Disables triggering event based procedures. A bit advanced, but suffice to say that if you've got some automatic macro triggering on event-based changes, this will suspend them in favor of the current running macro.</li> </ul> <p>There are loads of others and it will be to your best interest to learn most of them. ;)</p> <p><em><strong>THE BIG FINALE</em></strong></p> <p>Here's a sample code taken from your recorded macro that uses all the above techniques and takes into consideration the procedures you executed on your macro. <strong><em>This is not your whole code.</em></strong> Read this, test this, modify this and you'll improve a lot in a day.</p> <pre><code>Sub RefinedCode() Dim SourceWbk As Workbook, TargetWbk As Workbook Dim FALSht As Worksheet, FALSht2 As Worksheet, MasterSht As Worksheet Application.ScreenUpdating = False 'We won't see the copy-paste and column deletion happening but they will happen. Set SourceWbk = ThisWorkbook Set TargetWbk = Workbooks("MSS Call Routing Master List") Set FALSht = SourceWbk.Sheets("Fresh Agent Leads") With TargetWbk Set MasterSht = .Sheets("Master") 'Basically reads as Set MasterSht = TargetWbk.Sheets("Master") FAL.Copy After:= .Sheets(1) Set FALSht2 = .Sheets("Fresh Agent Leads") End With With FALSht2 Union(.Range("A:A"),.Range("C:C"),.Range("E:O")).Delete With .Rows(1) .RowHeight = 30 .WrapText = True End With .Range("A1").Copy .Range("F1:F100") End With MasterSht.Name = "Call Router" TargetWbk.Save SourceWbk.Close Application.ScreenUpdating = True 'Return to default setting. End Sub </code></pre> <p>Hope this helps.</p>
    singulars
    1. This table or related slice is empty.
    1. POCan I make this macro more efficient or faster?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USManhattan
    1. USManhattan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCan I make this macro more efficient or faster?
      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. VTAcceptedByOriginator
    1. COTerrific contribution to a newbie's learning journey. It's a good thing when people like you have "too much time on their hands". A couple of things I always add: Must use `Option Explicit` to catch typos in variables (and generally get better error checking during compilation); use specific types (rather than Variant) whenever possible - much faster, and don't forget `Application.ScreenUpdating = False`. That one alone will save a TON of speed especially when you are changing formatting etc.
      singulars
      1. PO
      1. USFloris
    2. CO@Floris: I was actually a bit torn about including those in the above. First, the text is too much as it is. And `Option Explicit` for a real beginner is a bit too intimidating. It will point out error after error and can hamper the code. For most of us, debugging is like 90% of the work, but for beginners, this is something they will eventually encounter. The `Application` "tricks", that's for another time. Hahaha. But, yes, let me put in at least `.ScreenUpdating` since this is virtually a necessity! :)
      singulars
      1. PO
      1. USManhattan
    3. COMeh, expanded it a bit more than just `.ScreenUpdating`. :)
      singulars
      1. PO
      1. USManhattan
 

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