Posts by XLdude

  • Show autofilter criteria

    Re: Show autofilter criteria


    the method to avoid "Application.Volatile" seems like an attactive alternative if it can reduce the recalc/UDF trigger to only a change in auto filter.


    I tried the method described by BruceMutton:


    I did this-
    1. commented out the "Application.Volatile" line
    2. changed the formula input in the cell with the function from " =AutoFilter_Criteria(D2:D2) "
    to " =AutoFilter_Criteria(D2:D3) "


    but that does not work. the cell now shows #VALUE!
    is there something more to be done?


    Using the original form with "Application.Volatile".....
    a problem I noticed was that Autofilter "Show All" does not cause the UDF to recalc/update,
    only if create my own UDF for ShowAll which has a "Calculate" as the last command.


    Any suggestions on addressing these?


    Thanks.
    Dave

  • assign constant values to multidimensional array in VBA

    Re: assign constant values to multidimensional array in VBA


    Fantastic.
    That was the solution I was originally pursuing.
    Thanks to Norie.


    Also, the looping method offered by jindon is a good demonstration and I recommend any newbies try that code, step through it while watching Locals variables window - very educational. Be sure to expand the array variables as they are poplulated. I learned a lot from that one.


    Before I saw Norie's solution,
    I suspected that all that looping was calculation and time intensive and I came up with a 3rd alternative, which has no looping and uses only statements-


    3rd Alternative Method:


    Code
    Sub test3()
    Dim x, y, lookvalue, valindex
    lookvalue = 20
    x = Array(-90, -84, -72, -61, -50, -39, -28, -17, -6, 6, 17, 28, 39, 50, 61, 72, 84)
    y = Array(472, 460, 440, 416, 386, 350, 305, 260, 215, 170, 125, 89, 59, 35, 15, 3, 1)
    valindex = Application.Match(lookvalue, x, 1)
            MsgBox Application.Index(y, 1, valindex)
End Sub


    Thanks everyone.
    -Dave

  • assign constant values to multidimensional array in VBA

    Re: assign constant values to multidimensional array in VBA


    Thanks for the quick response Dave.
    --Sorry, I don't understand your comment.


    if you were suggesting this---

    Code
    Application.WorksheetFunction.HLOOKUP(lookvalue,{-90,-84,-72,-61,-50, _
-39,-28,-17,-6,6,17,28,39,50,61,72,84; _
472,460,440,416,386,350,305,260,215,170,125,89,59,35,15,3,1},2,TRUE)


    it gives a "Compile Error-Invalid character" because of the {
    The worksheet cell function is okay with that format of the array, but VBA won't work.


    Perhaps I should clarify--
    The key thing I am trying to figure out is how to initialize the values in the 2x17 array, in VBA


    for example here is what I first tried:


    But, what does not work above is the line for
    U1 = array (...row one values... ; ...row two values...)
    which produces a Compile Error because of the ";" .
    The ";" character is okay syntax for the array in the actual worksheet cell function to separate the row one values from row two values.


    Is there a way to initialize all values in an X by Y array?
    ie. without looping through 2x17=34 lines of code using
    myarray(x,y) = value
    to set each value individually ???
    Hopefully that makes it clearer.


    Thank you for your help.
    -Dave
    [ps: 100 hacks book is Great!]

  • assign constant values to multidimensional array in VBA

    my objective is to create VBA Function equivalent of this formula:


    =HLOOKUP(lookvalue,{-90,-84,-72,-61,-50,-39,-28,-17,-6,6,17,28,39,50,61,72,84;472,460,440,416,386,350,305,260,215,170,125,89,59,35,15,3,1},2,TRUE)


    as you can see contains a 2x17 array


    VBA code so far:

    Code
    Dim U1(2, 17) As Variant 
Function ChartNum(Atlas As String, ra As Date, dec As Variant) As String


    The next step is to assign the values of the 2x17 array


    I see from VBA help the function to assign a 1 dimensional array is

    Code
    U1 = Array(-90, -84, -72, -61, -50, -39, -28, -17, -6, 6, 17, 28, 39, 50, 61, 72, 84)


    the problem is I need to assign values to all the dimensions, with 'something' like this hypothetical code:

    Code
    U1(1, x) = Array(-90, -84, -72, -61, -50, -39, -28, -17, -6, 6, 17, 28, 39, 50, 61, 72, 84)
U1(2, x) = Array(472,460,440,416,386,350,305,260,215,170,125,89,59,35,15,3,1)


    it seems like it should be possible to assign values to the "n-th" row or dimension with a statement, since it can be done at the 1 dim level without a loop.
    how do I do this WITHOUT using a loop? (preferred)
    how do I do this WITH a loop?


    Thanks in advance.
    Dave

  • Cell Formating Slows Down AutoFilter

    Re: Cell Formating slows down use of Autofilter


    Mystery solved, but not explained.


    This thread could be renamed "[af]*[/af], Formatting Mystery, and Wordwrap Impact"


    I don't want my users waiting 2 min for something that should take a dozen seconds.


    This problem was driving me crazy, there was no logical reason for it.


    I had earlier revisions of the file, that were basically the same; the file had 'evolved' somewhat with
    enhancements, but the formatting of the 14000 rows was basically the same, and the forumlae only slightly
    more complex.


    The 'old revision' of the file WITH FORMATTING takes about 4 seconds to "ShowAll" (via VBA) starting from a typical


    filtered state, and I might expect the newer version to run 3x run time of the old, but not 30x the run time !!


    **THE HYPOTHESIS:
    Suspecting that cell formatting had gotten 'corrupted' or 'mutated' I tried an experiment.
    I was/am suspicious that many Copy / PasteFormats from other cells had somehow propagated this mutation.


    **THE SOLUTION:
    -Clear formats
    I did a Edit / Clear / Formats, for cells of the filtered range.


    -Manually apply formatting
    Then I manually built up the formatting to the desired state, applying each format attribute from the regular menus.


    **THE RESULT:
    WITH the REBUILT FORMATTING it takes about 20 seconds to "ShowAll" from a typical filtered state.
    This is within expectation.


    **WORD WRAP (WRAP TEXT) IMPACT ON AUTOFILTER:
    One of the columns has a long text description, word wrap is important to use there.
    I suspected that cell word wrap format would have to be 'recalculated' everytime the filter was applied,
    I tried another experiment.
    WITH WRAP TEXT FORMAT = 'ShowAll' runtime = 20 seconds
    NO WRAP TEXT FORMAT = 'ShowAll' runtime = 1 second.


    No questions about those results.



    Strange phenomenon about the format 'mutation', maybe this learning will help someone else.


    **Future
    Regarding the Dynamic Formatting Code, I have been thinking it would probably be more explicit and 'safe' to use a WITH statement to change cell properties instead of Copy the row range and Paste Formats.
    I had not done this because it was more complex code and the flexibility that the Copy/Paste from the 'seed' top two rows allowed the user to alter the format of those and replicate the two-row format throughout the list.


    I suppose the cell properties of the top row's dozen cells could be 'read' and then applied to the other cells using a
    WITH statement.


    ???
    Anyone got some slick code to 'read' all format properties of a range of cell's format properties (each cell could have a unique format), and say, map it to a variable(s)?


    Dave


    PS:
    I have created a handy VBA procedure Run Timer (Msgbox tells you how long the code took to run) which I will post to another thread.

  • Create pull down lists dependent on eachother

    Re: Create pull down lists dependent on eachother


    Foxxy1,
    I agree with Steve SWST, AUTOFILTER does exactly what you describe as your requirement...
    and its more flexible than the conditional method you were pursuing in ExampleA.xls


    I added Autofilter to your list in ExampleB.xls


    As the user filters on specific values, Autofilter restricts the choices of the other drop downs.
    It's more flexible than the column by column lookup because the user may choose to filter any sequence of fields rather than be restricted to the only VLOOKUP setup configured (eg Mfg->Descript->Model)


    Quote

    The problem with the autofilter is (and this maybe a problem with me) that I don't know if I can set it up as code and make it recognize data within a certain cell.


    What do you mean by this? what is your (additional?) requirement?
    To do what I think you originally described, Autofilter does not need any additional code.
    However, there are possibilities to use some formulae and or VBA in combination with Autofilter.
    ~Dave

  • Cell Formating Slows Down AutoFilter

    I have an [af]*[/af] list of 14,000 rows by 14 columns, and the cells have some specific formating: fill color, font,
    protection status, wrap, etc....
    There are an additional 7 columns of formulas to the left of the filtered range.


    The strange thing is----
    -WITH the formating, trying to Unfilter the list takes 2 min, via a manually activated Data>Filter>ShowAll OR via a macro run of 'ActiveSheet.ShowAllData' .
    (In an attempt to optimize speed, the VBA macro sets calculation to manual before the 'ActiveSheet.ShowAllData' and screen updating set to false.)


    -WITHOUT the cell formating (eg. by doing Edit>Clear>Formats), the ShowAll takes about 3 sec.


    Does anyone have experience or an explanation for this?
    Why should the Formating affect Filtering so much?
    Options for improving speed of autofilter?



    NOTE:
    I don't know if, or why it would be a factor, but note that I am using [dr]*[/dr] and VBA to expand/contract the formulas


    and formating to size of the list/table. Although this is not done during the filtering use.


    Here is the dynamic formating code



    I am wondering if some strange 'artifact' of manipulating the formating is becoming a factor ??


    Thanks in advance for your responses.
    Dave

  • stop Cell Comment scoll when sheet is scrolled

    I have some cell comments which have many lines (5-20 lines, also one cell comment has a picture for a 'pop-up diagram') and the sheet has freeze panes set...
    and the 'large' cell comments extend over into the unfrozen pane... (the comments are always in hide mode, and used only for 'cursor hover' display as needed)


    the problem is that when you scroll the body of the sheet that is not frozen, the cell comments scroll in lock step with the sheet/cells


    Is there a way to keep the full height of the comment visible as I scroll down the unfrozen portion of sheet??
    cell comment needs to be in 'hide' mode.


    the scenario I describe is probably obvious, but attached is a file example....


    thank you,
    Dave

  • Enable Sort On Protected Sheet

    Re: enable Sort on Protected Sheet -for User, NOT VBA


    Ok. I suppose that's the end of it. I will have to leave the whole sheet unprotected.



    WillR,
    thank you very much for your quick response.


    Dave

  • Enable Sort On Protected Sheet

    I have searched the archives extensively....
    the nearest post I can find on sorting protected sheets is http://www.ozgrid.com/forum/showthread.php?t=28699


    sheet layout:
    database-like list; hidden formulas in cols A..H, left of the list range; top 10 rows for parameters entry and forumlas; the List range has an AutoFilter.


    I would like to be able to protect (& hide) the "surrounding" (left and above) cells & formulae WHILE still enabling the User to manually exercise the AutoFilter and Sorting.
    I want the List range data to be Unprotected.


    From previous posts by Dave Hawley, I see what how to do this for AUTOFILTER


    Code
    Private Sub Workbook_Open()
    With Worksheets("Sheet1")
        .Enable.Sort
        .EnableAutoFilter = True
        .Protect UserInterfaceOnly:=True
    End With
End Sub


    which I have tested and seems to enable the mix of protection


    --HOWEVER, how to enable SORT on a protected sheet WITHOUT resorting to VBA intervention, turning protection on and off.


    The VBA protection on and off method would inhibit the ad hoc editing, sorting and filtering needed by user.


    Dave


    PS. Fantastic website and forum archives!
    I have learned so much in the past months!!