Posts by JamesRyann

  • Power Forecast Formula Errors Out on Legitimate Value

    Re: Power Forecast Formula Errors Out on Legitimate Value


    ??? So, something else has got to be going on here, because I just pulled up the project this morning to figure out at least a workaround, and it's working fine. Seriously. The formula on the exact same data set is working just like I thought it had for the previous several weeks. Maybe I just need to spin around three times, click my heels together, and sing "God bless my Excel sheet"! ;) Weird. Happy! But weird.

  • Power Forecast Formula Errors Out on Legitimate Value

    Re: Power Forecast Formula Errors Out on Legitimate Value


    Well, that's unfortunate. :( Questions abound in my mind like "Why have I used this formula on this dataset for weeks and it never errored out before?" and "I've used this formula for years, and it's never errored out before. Why now on such a typical dataset?"


    But there's no way for us to back and look at all of that. At least, there's no way that I know of.


    Anyway, I think I'm just whining at this point. ;) In the end, if there's a capacity issue with Excel, then that's a legitimate reason. If you recreated it on your computer, it's not something wrong with my computer. If it mathematically hits the ceiling, then 1+1=2, and I can complain about it all day long, but tomorrow 1+1 is still going to = 2. Thanks for letting me know! Just 'cause I don't like the answer, doesn't make it not right. lol!


    James

  • Power Forecast Formula Errors Out on Legitimate Value

    So, maybe I've just been working way too hard on this project, and my Excel program is sick of seeing me every day for 18-20 hours at time, but I really don't get this one.


    I'm using the following power forecast formula:


    =(EXP(INDEX(LINEST(LN(MyPrices),LN(MyUnits),,),1,2)))*SubjectUnit^(INDEX(LINEST(LN(MyPrices),LN(MyUnits),,),1))


    to predict the value of a property from a set of comps along a nonlinear regression analysis. If you don't know what all of that means, don't sweat it. It's not that important.


    What *is* important is that I've been using it for years, and it'll work for just about anything. Might not be terribly accurate it you feed it bad data, but it'll still spit out a value.


    I've been using a database set using the same subject and comps for several days, putting together an analysis interface, when I started getting errors on this formula, and always on dates. I figured I just had something going on in my code that was wrong, and was just going to ignore it while I built the interface, and then go back and fix any bugs that were still hanging around.


    Until today.


    Today, a part of my code that's been in place and untouched for quite a while started throwing the same error. On the same dataset that's been working fine for months without throwing errors. So I get annoyedly curious (if I can coin a word).


    I've isolated the problem down to an identifiable breaking point, but I still have no idea what's causing this never-before-experienced error on seemingly legitimate data that's been working fine for weeks.


    I've included two screenshots so you can see *exactly* what's happening on my screen; one works, the other throws an error.


    Both screenshots show a list of 23 properties that have sold in the past year, closing dates shown in column A, and closing prices shown in column B. There's a scatter chart showing all of the data points of the 23 properties, by date and price. In cell H2, we have the forecast formula I referenced above, with a copy of the actual formula in use immediately below it so you can see it.


    The only thing that's different between the screenshots in the data set is the value of cell B24 (highlighted in yellow), the closing price of the 23rd comp. If I set the closing price at $131,000 or above, everything's fine. The predicted value works in cell H2, and the power trendline is displayed on the scatter chart.


    If I set the value of cell B24 below $131,000 (for example: $130,000), then the formula errors out to #NUM, and the trendline disappears from the scatter chart. The data point of this 23rd comp is identified in the scatter chart in red, and as you can see, there's nothing all that extraordinary about it. It's not on the fringe. It's not throwing the data set into to some skewed formation. It's just sitting there nicely in the middle as a good little comp.


    I've used this formula and charting system on thousands of properties, with anywhere from three to three thousand comps at a time. I've never seen this happen. I have no idea what's causing it, and so I have no idea how to fix it.


    Thoughts?


    Thanks!


    James


    Works:
    [ATTACH=CONFIG]71236[/ATTACH]


    Doesn't Work:
    [ATTACH=CONFIG]71237[/ATTACH]

  • Fix Solver Relation:= Field "ByRef argument type mismatch" Error

    Re: Fix Solver Relation:= Field "ByRef argument type mismatch" Error


    Okay, true to form, I've developed an answer before getting a reply. ;)


    Admittedly, this one's a work-around, so not really a solution, but it works . . . for whomever might find this thread in an effort to accomplish the same thing.


    As there are only six options for the Relations field, I just used If-Then statements with hard-coding:

    Code
    If MyRelation = 1 Then SolverAdd FormulaText:=MyCondition, Relation:=1, CellRef:=MyVariableRange
If MyRelation = 2 Then SolverAdd FormulaText:=MyCondition, Relation:=2, CellRef:=MyVariableRange
If MyRelation = 3 Then SolverAdd FormulaText:=MyCondition, Relation:=3, CellRef:=MyVariableRange
If MyRelation = 4 Then SolverAdd FormulaText:=MyCondition, Relation:=4, CellRef:=MyVariableRange
If MyRelation = 5 Then SolverAdd FormulaText:=MyCondition, Relation:=5, CellRef:=MyVariableRange
If MyRelation = 6 Then SolverAdd FormulaText:=MyCondition, Relation:=6, CellRef:=MyVariableRange


    It works. Not pretty. Not tight. But functional. For now. :)


    James


    (I give myself 10 reputation points for the best answer! LOL!)


    P.S. I'd still be interested in any *real* solutions anyone came up with for this issue.

  • Fix Solver Relation:= Field "ByRef argument type mismatch" Error

    I'm trying to run Solver by setting the "Relation" field of the SolverAdd element using a variable.


    However, whenever I try to set the "Relation" field as a variable, I get a "ByRef argument type mismatch" error on that line.


    If I hard-code the line (e.g. set the value myself), it works fine. If I set it as a variable, I get the error.


    I've tried specifying the variable type (Integer, Double, String, unspecified), and nothing seems to affect it.


    Here's the code that works:

    Code
    SolverAdd FormulaText:="1", Relation:=1, CellRef:="$A$2:$A$10"


    Here's the code that doesn't work:

    Code
    SolverAdd FormulaText:="1", Relation:=MyRelation, CellRef:="$A$2:$A$10"


    I've tried:

    Code
    MyRelation = 1
SolverAdd FormulaText:="1", Relation:=MyRelation, CellRef:="$A$2:$A$10"


    Code
    Dim MyRelation:     MyRelation = 1
SolverAdd FormulaText:="1", Relation:=MyRelation, CellRef:="$A$2:$A$10"


    Code
    Dim MyRelation     As Integer:     MyRelation = 1
SolverAdd FormulaText:="1", Relation:=MyRelation, CellRef:="$A$2:$A$10"


    Code
    Dim MyRelation:     As Double:    MyRelation = 1
SolverAdd FormulaText:="1", Relation:=MyRelation, CellRef:="$A$2:$A$10"


    Code
    Dim MyRelation:     As String:    MyRelation = "1"
SolverAdd FormulaText:="1", Relation:=MyRelation, CellRef:="$A$2:$A$10"


    (Yes, setting the Relation:="1" works fine if I hard-code it that way, as the field itself is text-neutral.)


    Again, if I hard-code the value, it works. If I set the value as a variable, I get the ByRef error.


    Thoughts?


    Thanks!


    James


    (P.S. sorry for including the ":" in the Thread Title. I'd change it if I could, but I have no idea how.) :/

  • UserForm will Neither Hide nor Unload

    Re: UserForm will Neither Hide nor Unload


    Ah! Okay. I guess I'm tired. ;) (I've only been working 20-hour days on this for a month and a half. Ha!)


    Found my problem, but I'm leaving the thread alive to let someone else get the benefit of me being an idiot! (Most of my Excel problems are because I'm an idiot! LOL! Most of my problems in all of *life* are probably because of that, too! ;))


    Code
    Application.ScreenUpdating = True


    Yeah, that kind of does it.


    I had my ScreenUpdating set to False in a previous subroutine, which I knew, and did intentionally, and totally didn't even seem to think about enough to set it back to True during this dialog session. (sigh.)


    Eh, there you go for someone else, if ever they see this thread. :))


    (This is why I don't like to post questions on forums: I generally figure things out before I get a viable answer from someone else. LOL! ;))

  • UserForm will Neither Hide nor Unload

    I have a UserForm (named "Update_Status") that I've created to display the status of a subroutine tree that's in process. (By subroutine tree, I mean that I have a series of consecutive subroutines running, not just a single subroutine. Kind of beside the point, but clarity is often a good thing! ;))


    At one point in the process, I need to hide this "Update_Status" UserForm in order to manipulate the spreadsheet data through another UserForm that I've created. Basically, the Update_Status UserForm is in the way, and I need it gone in order to see the spreadsheet data that I'm manipulating. I use several of these types of UserForms in the process, so I'm very comfortable with understanding how to manipulate them, and I've been writing this type of code using UserForms for years.


    However, I can't get the Update_Status UserForm to go way. Arrrggghh! (Die! Die! Die! Already!)


    I've tried:


    Code
    Update_Status.Hide


    Code
    Unload Update_Status


    Code
    Update_Status.Hide
DoEvents


    Code
    Unload Update_Status
DoEvents


    Out of crushing frustration, I even went overboard with:


    Yet, my UserForm persists. ??? Really. Come on! Just die already!


    Okay, so if I step through the code, it dies fine. :)


    Here's my actual code:


    Like I said, if I step-through the code, it dies fine. If I run the code, it doesn't.


    If I put a breakpoint on the "If MsgBox("No current update file found." line (as shown below, designated by the asterisk (*)), and run the code, the code stops at that line, waiting for me to continue the step-through, but the Update_Status UserForm is still *expletive* displayed! (Seriously! Just DIE already!)


    Code
    Unload Update_Status
                    DoEvents
                'Determine what the user wants to do.
*                   If MsgBox("No current update file found." & vbCrLf & vbCrLf & _
                              "To STOP processing, click the ""No"" button." & vbCrLf & vbCrLf & _
                              "To CONTINUE processing, and open a file to use as the current Update file, click the ""Yes"" button.", vbYesNo) = vbNo Then


    So it obviously has to run through the "Unload Update_Status" line to get to the breakpoint, but it's ignoring the statement, as even when the VBA stops at my breakpoint, the UserForm is still displayed!


    If I manually move the Run Cursor (sorry if it's actually called something else; it's what I call it, and I've never taken the time to look it up.) three lines back to the "Unload Update_Status" line, and either run the code or step-through it, the UserForm goes away.


    But the for the life of me, I can't get it to do it in realtime.


    (For what it's worth: Windows 7, Excel 2010)


    If there's something simple that I am just somehow overlooking, Great! :) Make me happy. If there's something deeper, and you can show me how to fix it, even better! :)


    Thoughts? :)

  • Freeze Panes VBA Without Select

    Re: Freeze Panes VBA Without Select


    Short answer to the OP: Yes, you can. Contrary to pretty much everything I've seen on this subject around the web, amusingly enough. For the quick $0.20 answer to a $2.00 question, here's how:


    Code
    With Windows("My Workbook Name.xlsx")
    .SplitColumn = 1
    .SplitRow = 1
    .FreezePanes = True
End With


    The reference you want is the Windows("My Workbook Name.xlsx"). That's the relevant part. The SplitColumn and SplitRow are the just the references to the cells you want to freeze. i.e. SplitColumn 1 is Column "A". SplitRow 3 would be Row 3. Hmmm. What do you think SplitColumn 5 would be? (A,B,C,D,E) Yeah, "E". You get the idea. But it's the Windows("My Workbook Name.xlsx") that you're really asking about here.


    Now that I've given you the proverbial fish, I want to at least show the fishing, as it were, though mostly because I really just want to address some of things that have been passed around in this thread (and really, all over the web about this subject).


    First thing, the whole "why would you want to?" question.


    Short answer: it doesn't matter. I feel that asking that question turns the purpose of the discussion away from "how do you do it?" and towards "can you provide me with an acceptable rationale to try?" It's like I'm on trial for wanting to do something, and it's frustrating to me when it seems like someone is more concerned about convincing someone else that figuring it out isn't worth it. It's like I have to justify my desire to do something before you'll be willing to help me. I don't want to have to justify my desire. I just want to know how to do it. Please don't make me argue my reason for wanting it. Please just say, "Here's how" or "I don't know." (Yeah, perhaps, "Well, depending on what you want to do, there might be a better way" is legitimate, but it almost *never* comes across as helpful.)


    Long answer: I'm running code. I like to be able to stop in and troubleshoot a code with the visual aspects of a spreadsheet intact like I did it all manually. That way, it saves me time if I ever have to go back and change/fix/add/alter/whatever something at a later date. I'm constantly learning, and my ability to write code is constantly evolving. If I wrote something a year ago, it's probably with a slightly different mindset on code than I have now. I want to be able to go back and step-through a year-old code, and be able follow exactly what's happening both on the screen and in the code. In order to do that, I want to be able to see it like I would have manually done it. In this case, that involves Freezing Panes at the position I would have if I were doing it manually. No I don't really care all that much whether or not you like my rationale, or if you agree with my reason, or whether you think the color pink looks good on the towels in the bathroom or not. Regardless though, I still want to know how to FreezePanes on a sheet that's not active. Ironically in this case, it's not even on a worksheet that ever gets saved or is even seen by the user. I create the tab in the code on the fly, temporarily use it to parse data, record the data, and delete the tab. But I still want to freeze the panes so I can step-through it later if need be. :)


    Okay, second thing, really the more fun one, in my opinion, Freeze Panes is Visual, and therefore impossible on an inactive window.


    Nah. Everything is binary. There are no "eyes", only "i's", as in "index." Think about it. From what most of us who deal in VBA either already know or are quickly learning, everything either has or is part of an index. Workbooks(Index) - Sheets(Index) - Range (Index) - UserForm.Controls(Index) - Shapes(Index) You get the idea. You can reference the index by either number or name. VBA doesn't really care. It just needs to be pointed to the index associated with the object, and the netherworld of VBA becomes your proverbial oyster.


    Now, most of us are familiar not only with the "index" aspect of pretty much every object under the sun in VBA, but also with the "cheating" shortcuts of ThisObject and ActiveObject i.e.: ThisWorkbook and ActiveWorkbook, etc. Really, that's all the "ActiveWindow" is. It's a cheat shortcut to an Indexed Object without referencing the index behind it (or is that "over" it?). Anyway, once I realized that the "ActiveWindow" command was just the cheat shortcut around the direct index catalogue of the object, I figured that the "Windows" object must be indexed just like every other object. So, a little tinkering, and I found that I could reference that Windows index through the name I gave the "Windows" object, just like I do with every other object in VBA.


    And voila! I don't need to have ActiveWindow, I can reference Windows(Index). In my current project, I've got a bunch of windows open, and I'm passing information back and forth between them (huge, massive indexed databases with 100's of 1,000's of records with dozens of fields in each record, manipulated through dozens of UserForms, etc.). Like, really big. 100 mb spreadsheets, etc. Not to impress you, just to impress *upon* you that memory is, well, precious at times! So I don't do anything active if I can get away with it. And I pretty much can. ;) So then I just


    Code
    MsgBox Windows(1).Index


    to figure out what index I needed to reference, and it returned the index number. Hmmm. Well that's pretty simple! So I plugged in


    Code
    MsgBox Windows(MyWorkbook.Name).Index


    and it returned the index number! Woo-Hoo! Now I'm getting excited, because I'm getting somewhere! :) So I played a little more and came up with


    Code
    MsgBox Windows(MyWorkBook.Name).Caption


    and it returned my Workbook Name! (Shocking! I know! That's like asking "What's John's name?" Right? ;) From there, plugging it in was pretty easy. I set my Workbook variable .Name in the Windows(Index) feature, and I have my


    Code
    Windows(MyWorkbook.Name).FreezePanes = True


    Ah, fascinating! :)


    From there, I just referenced the SplitRow and SplitColumn index (again, everything's indexed) to tell the VBA what cells to freeze, and suddenly the impossible again returned to I.M.Possible. Pretty much everything is. Whether I have rationale for it that you like or not. ;)


    Lastly, thirdly?, nextly?, (whatever) . . . there's also the UnFreeze To ReFreeze requirement. :roll: ??? Nah. The only reason we have to UnFreeze to ReFreeze manually is because it's a toggle button. It's an on/off switch, but that's just the interface, not the function. Just call it off. If it was off before, it's still off now. Or, just call it on. If it was on before, it's still on now. No reason to toggle it back and forth once you're underneath the GUI (graphic user interface) toggle. Just call it what it is. VBA understands. It's just like setting the .Left, .Top, .Height, or .Width of a UserForm (or any other object for that matter). You don't need to hide it to redefine it. You don't need to unload it. You just . . . redefine it. Same principle.


    For what it's worth (or not), there's my rant. There's my solution. There's my two cents on a long-dead, but here-to-fore unsolved thread. Hope it helps someone. Somewhere. Sometime. Somehow. In someway. Eh, probably not, but at least it's fun to fantasize about.


    James


    (In hindsight, I guess this turned into more of a blog than a post. Would that be a "plog", maybe?) And of course, now I'm playing around with trying to figure out how to freeze panes on a Worksheet that's not even visible. Why do I want do that? I don't. I just started thinking about it while writing all this, and it made me want to know how! ;)