Posts by Demgar

Re: Invoke Object Method Via String Reference - VBA

CallByName it is!

I knew there was something like this, just couldn't track it down. Thanks a ton!

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

application.run("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!

Re: Expert: Indirect Reference to Named Formula ; Multi-Formula Switch

Yeah, I came across the alphebetical quirk as I was testing the formula. I think match is indeed a better choice here since I only want to return a sequence number for the search term and not some other associated value.

Thanks again, it's working perfectly.

Re: Expert: Indirect Reference to Named Formula ; Multi-Formula Switch

Good grief.

Why didn't I think to use choose?! I knew there was something obvious and I was just making things harder on myself. That should totally work, thanks a bunch Rob.

Hello,

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.


Possible?

Re: Multipages userform

I was unable to re-produce the error. Perhaps you could be more specific about what behaviour is causing it?

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: Copy data from one sheet to first empty cell in another using VBA button

Hi VRF, welcome to the Ozgrid forums. This is a very common question, I would encourage you to take a look at some of the related threads at the bottom of the page and see if you can find some relevant answers there.

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.

=SUMIF(A:A,F5,B:B)

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.

Top of a standard code module:

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

Worksheet module:

Option Explicit


Private Sub Worksheet_Calculate()
Dim i As Long, lastrow As Long
Dim foundStored As Boolean, test As Boolean


On Error GoTo instantiateArray:
    test = LBound(OldValues) 'Fires an error if the array isn't instantiated
On Error GoTo 0


With Me
lastrow = .Columns(7).Find("*", lookat:=xlPart, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row


Dim cell As Range
For Each cell In .Range("G2").Resize(lastrow - 1)
    foundStored = False
    For i = LBound(OldValues()) To UBound(OldValues())
        If cell.address = OldValues(i).address Then
            foundStored = True
            If OldValues(i).value <> cell.value Then
                MsgBox cell.address & " has changed from " & OldValues(i).value & " to " & cell.value, vbOKOnly
                OldValues(i).value = cell.value
            End If
            GoTo NextCell:
        End If
    Next i


    If Not foundStored Then
        ReDim Preserve OldValues(i + 1)
        OldValues(i + 1).address = cell.address
        OldValues(i + 1).value = cell.value
    End If
NextCell:
Next cell
End With


Exit Sub
''''''''''''''''''''''''''''''''
instantiateArray:
ReDim OldValues(0)
OldValues(0).address = "Address"
OldValues(0).value = "Value"
Resume Next
End Sub

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.

Option Explicit
Sub SumSameSignBlock()
    Dim rng As Range
    Dim chk As Boolean
    Dim c As Range
    Dim temp As Integer
    Dim s As Double
    Set rng = Range("C3:C" & Range("C" & Cells.Rows.Count).End(xlUp).Row)
    temp = Sgn(Range("C2").Value)
    s = Range("C2").Value
 
[B]Application.Calculation = xlCalculationManual[/B]


    For Each c In rng
        If Sgn(c) <> temp Then
            chk = True
            temp = Sgn(c.Value)
        End If
        If chk Then
            c.Offset(-1, 1) = s
            s = c.Value
            chk = False
        Else
            s = s + c.Value
        End If
    Next
    rng.Cells(rng.Cells.Count).Offset(0, 1) = s
 
[B]Application.Calculation = xlCalculationAutomatic[/B]
End Sub

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.

Public Sub TrimToSingleSheet(BookToTrim As Workbook)
Dim CurrentSheet As Worksheet
Dim alertStatus
 
'Takes the active workbook (normally a workbook.add) and pares it down to a single tab
alertStatus = Application.DisplayAlerts
Application.DisplayAlerts = False
 
For Each CurrentSheet In BookToTrim.Sheets
  If CurrentSheet.Index > 1 Then CurrentSheet.Delete
Next CurrentSheet
 
Application.DisplayAlerts = alertStatus 'set the status back to where it was. We don't know where this might be in the call stack.
End Sub

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.

Re: Change A Formulas Spreadsheet Reference Based On Another Cell's Contents

You can get a string reference to use with your indirect with ADDRESS:

=ADDRESS(ROW(A1),COLUMN(A1))