Posts by Demgar

    Hello again

    I have a sticky one that I can't get past, and can't find a resource on. Essentially, I need to invoke a Subroutine of an object using a string variable. I DO have control over the class modules of the objects in question if that's important to the solution.

    This is simple to do with a method in a code module with

    Code"ModuleName.MethodName",param1, etc..)

    But you can't invoke a method on a created object this way, and unfortunately that's important in this case.

    I could envision an elaborate Case Selection in which I map the various possible methods to their string matches, buuut if there's a way past that? It feels like a crutch and there's several objects involved. There will be more methods added later and I need to keep it simple enough for other users to implement more.

    I really hate banging my head against stuff like this that is well implemented in .net when I need a VBA solution!


    I have an interesting problem that I'm having trouble working around, I will provide a little bit of background in case someone can help come at it from another angle that I'm not seeing.

    Essentially I'm using "magic strings" to direct the source of a lookup formula where the lookup range exists on another workbook. I've hit the nested formula limit (restricted to 2003), and need to expand it to further options.

    Right now it looks (very simplified) something like this:

    =if(source = "ABCD", vlookup(x,y,z,0),if(source = "WXYZ",vlookup(a,b,c,0), if( etc...

    Ok, so I know this isn't really pretty, but it was functional.

    The project has expanded and I now need 8 or more different possible formula. So I say, "This is a job for an Indirect() reference to a named formula"! Problem being, I don't think that you can reference a formula or constant with an indirect... it implies a range reference. (If you need proof, try something simple.. define AnswerLife as =42. =AnswerLife will work, but =Indirect("AnswerLife") returns a #REF). Normally of course you could just go ahead and name the ranges, and write a match/indirect to have a multiway switch, but it bears repeating that the source ranges are on other workbooks, and can't be named normally (you can create a named reference to a range in another workbook, but it still counts as a "formula" for this purpose).

    I can see possibly two ways out, but I don't like either for this project.
    1. Helper columns. I could potentially run the lookups 1 per column and then reference them depending on the criteria. I really dislike this, because I don't have any flexibilty on the layout of the sheet (client reporting), and I don't have a way to flag non-reported columns in the reporting application. I don't really feel like re-writing it...

    2. UDF - this is simplicity itself with a UDF; A select case switch would be pretty easy to implement. I've got dual concerns with both speed, and portability. I would like to stick with "out of the box" functions if at all possible. If I don't discover another solution, this will have to do however.

    So, hopefully that was at least partially clear. The constraints summed up:
    1. A column should contain a formula that will lookup on 8+ workbooks.
    2. Based on a "magic string" critera in another column.
    3. Excel 2003.


    Re: Multipages userform

    I'll take a look and see if I can see what your error is, but in the meanwhile, you mind editing your post to use CODE tags as per the forum rules?

    Re: Looping through objects

    Welcome to the forums, you will want to edit your post and use code tags as per the forum rules.

    You are looping every button 98 times and assigning them all the same name each time through..

    For i = 1 To s
    For Each cCont In Me.Controls
    Next cCont
    Next i

    Maybe try:

    For Each cCont In Me.Controls
      If TypeName(cCont) = "CommandButton" Then
       i = i+1
       ccont.Caption = sheets(i).name
      end if
    Next cCont

    Re: Code for button runs and causes "1004" error on Exit and Save

    I'm stumped. I can suggest to put a breakpoint at the beginning of the on_close and hitting F8 through the run to see why the other procedure is being run. That might give you a clue, but I really can't think of any reason it should behave differently depending on whether you choose to save or not.

    Re: Display message when cell's formula automatically recalculates value

    Are you saying that the sheet takes several minutes to calculate or that the event doesn't trigger very often?

    I *think* the event should trigger whenever you have updates in your data, you mentioned that there were formulas generating it (or formulas looking at it would work). The live data should arrive, trigger a recalculation, and thusly this code. Without knowing how your data gets there, I can't suggest a better event to use. Another way to go would possibly be the application.ontime method by which you can schedule the next check every x seconds. Running a constant check in the background has drawbacks as well, and I only like to use it as a last resort.

    Unfortunately VBA is relatively crap at event driven programming. Sometimes you have to go to the moon and back to get it to fire correctly all the time.

    Re: Problematic SUMIF

    The data needs to correspond in both arrays. You were starting at a different place.


    edit: Beat to the punchline ;)

    Re: Display message when cell's formula automatically recalculates value

    You need to store your old values in an array, since there will be potentially many of them. I created a type to contain the info about our old values.

    It shouldn't give you a bunch of messages at startup either, since we will append a new entry onto the array if we find a cell that we haven't noticed yet. If there are commands you aren't familiar with, I would encourage you to look around for info on them.

    I tested this, and it works great for me.

    of a standard code module:

    Type storedContents
        address As String
        value As Variant
    End Type
    Public OldValues() As storedContents

    Worksheet module:

    Edit: How big is your dataset? It's not super optimized, I would probably keep it under 100 or so to not notice any slowdowns.
    Edit x2: small optimization

    Re: macro to sum numbers with same signs

    Option explicit is declared above the first procedure in a module and is a good idea to leave in, it forces you to declare your variables and helps prevent spelling errors.

    For speed, try it with the changes I made in bold.

    Re: Run Time Error '9': Subscript Out Of Range Only with Some Users

    As an aside, when I'm adding a worksheet via VBA it's generally to create a report of some kind, and I like to keep them as tidy as possible. I have a procedure that I call so I know I am always starting with the same workbook (below). Then I add any additional sheets I need.

    You can try inserting after(1) instead of before(2), like shg suggested. It should work just fine.

    Re: Speed Optimising Conditional Sum of Lookups

    Have you tried renaming your lookup tables to use static ranges (or even the entire column)? Dynamic ranges are nice to reference from vba, and on smaller spreadsheets, but they aren't handled very well for the purpose of sheet calculation sometimes.

    As long as you don't return any N/A on your lookups, and it seems you don't, looking at the entire column with your vlookup won't be any slower than a fitted static range, and can be significantly faster than a dynamic range.

    You also might be able to play tricks with your calculation, selectively calc'ing ranges that need it. Overall though, I think you have a great candidate for a database solution, this is the sort of thing (relational keyed lookup tables) that even a low end DB will handle much more efficiently than Excel.

    Re: Speed Optimising Conditional Sum of Lookups

    Nice challenge Rob!

    If I read you correctly, when a column has a negative header, the numbers in that column are representative of data from the lookup table, and not their own value?

    Is it feasible within the scope of your project, instead of representing the values of the lookup table, to just go ahead and transform them directly (or a copy of them in another location)? You could add a vba solution to toggle the values between their "stand-ins" and their actual values on demand, maybe with some formatting indicating when they've been swapped out. Might make the data more clear to the user, as well as drastically simplifying the formula needed.

    Just shooting in the dark on what's going to be "legal" or make sense in your project. Aside from that, yeah hidden/helper sheets or columns to break up the work I reckon.

    Re: Summary Page in workbook, summing concantenated cells returns a 0 value.

    As an aside, you can perform a mathmatical operation (divide by 1) on numbers that are being treated as a string in order to "re-number" them. In this case, use the vlookup though ;)

    ="5" & "5" is a string, and won't show up in a sum.
    =("5" & "5")/1 is the number 55 and will be treated as such for the purpose of a sum.

    Re: Run Time Error '9': Subscript Out Of Range Only with Some Users

    I think shg hit it with "has only one sheet", the other conditions would break the run earlier I think.

    The "IOT" workbook which you've created with workbooks.add earlier in the run may have only a single sheet. This would break on my machine for example, as I've edited my own default workbook in this manner.