Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Couple of general tips.</p> <ol> <li><p>Take your function and work out where the bottlenecks really are. See this question for the <a href="https://stackoverflow.com/questions/1190833/what-are-the-best-tools-for-optimizing-queries-in-vba-and-why">use of timer</a> in excel. I'm sure there are VBA profilers out there... but you probably don't need to go that far. <em>(NB: do this with one cell of data first...)</em></p></li> <li><p>Think about your design... 400x120 cells of data is <strong>not</strong> a lot. And for it to take hours that must be painful. (<em>In the past i've cracked it after waiting a minute for 1,000s of <code>VLOOKUPS()</code> to return</em>) anyway maybe instead of having having a stack of UDFs why not have a simple subroutine that <code>for..each</code> through the range and does what you need it to do. 48,000 cells could take seconds or maybe just minutes. You could then associate the subroutine with a button or menu item for the user. </p></li> </ol> <p>Out of interest i had a quick look at option 2 and created MyUDF(), using the sub <code>DoMyUDF()</code> to call it for the active selection worked 10x faster for me, than having the UDF in each and every cell.</p> <pre><code>Option Explicit Function MyUDF(myVar As Variant) As Variant MyUDF = myVar * 10 End Function Sub DoMyUDF() Dim r As Range Dim c As Variant Dim t As Single t = Timer If TypeName(Selection) &lt;&gt; "Range" Then Exit Sub End If Set r = Selection.Cells Application.DisplayStatusBar = True For Each c In r c.Value = MyUDF(c.Value) Application.StatusBar = "DoMyUDF(): " &amp; Format(Timer - t, "#0.0000ms") Next Debug.Print "DoMyUDF(): " &amp; Format(Timer - t, "#0.0000ms") End Sub </code></pre> <p>If you replace <code>MyUDF()</code> with your UDF this may only save you 4.5 minutes... but it's possible there are some other economies you can build in. Especially if you are repeating the same calcs over and over again.</p>
    singulars
    1. This table or related slice is empty.
    1. POUser Defined Functions in Excel and Speed Issues
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USMark Nold
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    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
    1. COinteresting, i'll try this out. the problem is that while 120x400 cells of data is not a lot, i have to do 30,000+ simulations. in the past, i was able to do this within an hour (without UDFs), but right now it's taking 3 hours or so. i will try your suggestions and see what happens. in the meanwhile, thanks for taking the time out to respond!
      singulars
      1. PO
      1. USB Rivera
    2. COYou can also make things a bit faster by loading a variant array to the usedrange of your worksheet, then populate that array with cell values, and put that variant array as the usedrange's value. This is significantly faster than telling Excel to populate each individual cell with a value one at a time...
      singulars
      1. PO
      1. USJon Fournier
 

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