Posts by Kabong

    Not what I'm looking for here

    Hmmm... no, I think that would just get into the way, especially since the amount of time between message boxes is variable depending on the amount of data being processed in any singe run.

    If there were a way to get a press of the space bar to trigger a goto event this would be sufficient, as apparently the Escape key can't be properly modified.

    Thanks again for all your help,

    Yes and No

    I am doing it both for myself and others. The program may take quite a while to execute and it's necessary to have a way out. But, at the same time, quitting at a random time in the middle of the program may leave the workbook in disarray with things half here and there. If I disable the halting property of the escape key can I reassign it as an error handler?

    Maybe there's another key I could have people press for this purpose. Can you have a keypress on the space bar trigger an event? Thanks


    Don't think so

    I don't think that will work because pressing the escape key is a program interrupt procedure, not an error. I tried your suggestion, and I do use error handling, but this is not an error in that sense. It is a method for prematurely ending the running of the macro. I'd like to treat it like an error because I know well enough how to handle those, but that's not what it is.

    Thanks Again,

    Some preliminaries: I have to program for Office X on Macintosh computers. I'm not sure if everything is the same between the systems (in some cases I know it is not) so I will present my question from naivety to Windows Excel.

    When I press the Escape key while running a macro the program pauses. I am presented with options to Continue, Debug, Exit, or Help. If I choose to exit the macro the workbook is left in an intermediate state... part way from the original state and part way from my intended finished state. I can reverse all the changes I have made through the running of the macro but I need to be able to use the Escape key as a prompt for a Goto command. Once in this subsection I can sniff out what I need to and correct it there.

    Does anybody have any idea how to change this function of the Escape key while the macro is running? Preferably I'd like a Yes/No box to pop up on Escape keypress. Thanks in advance for any help.

    Also, hello again Derk... I've been away for a while :)


    I've created a macro that runs a standard excel correlation and then color codes the output sheet to correspond to the level of significance according to a standard Pearson's critical value table. Currently, to determine the level of significance, I have the program reference a worksheet that contains a copy of this table. Instead of this, I would like the program to calculate these values on its own from a formula in the macro. Is there anybody out there with a way to determine the Pearson critical values without reference to an actual sheet of values? Thanks for the help.

    Okay, I use VBA to produce a correlation table that I then color code for significance (anybody want a copy) but when I add the correlation sheet it adds in front of, that is, to the left of the selected sheet. I want it to come after, to the right... I know this is picky. Hope somebody can help, can't be too hard, right?

    Thanks, Kabong

    The logic statements above will work only if the cell contains only the text "x". Is this what you want or did you use the "x" as a generic indicator of a character or group of characters that might be contained within a text string in a cell?


    I tried your script and it works on my computer. Ever time I left an empty cell in column B, it deleted the entire row that contained that empty cell. It only looks at column B and it does the deletion correctly. Perhaps you could clarify what you mean by "doesn't work."


    Hello again Derk. Yes, I'm still slowly plugging away at this code. It's been hard finding documentation on this topic due to the fact that it is hardly ever used, frustrating. I've been using an equation that figures the mean square error for the ANOVA and uses that in an equation to calculate the Duncan range. I then compare this range to the difference in the means of the two groups being analyzed. This seems to be the correct way to do things, from what I've read. What's more, I don't think this will be do dastardly a code to write... at least not as bad as that damned Fisher Exact! Anyway, thanks for your continued attention, and I'll take a look at your suggestions. We may just end up going with a professional program.

    Thanks again,

    Is there a way to make a msgbox that allows the user to interact with the workbook while it is still popped up? I want to prompt the user to paste data into a specific area of the workbook and then click ok when they are done. The standard msgbox will not allow the user to click on the workbook. Is there a special type like there are in inputboxes?

    Thanks for the offer Derk. I'll start ASAIC, and post what I develop. I'll probably start by simply asking for the stats that would be gotten from an ANOVA, as this is the basest application it will be needed for here in the office, then I think I may work in the ANOVA calculations themselves. The problem with my workplace is that we have a half-dozen programs each of which perform a handful of analyses. We enter the data in Excel and then export to these programs. Why not do it all in Excel you ask? Because I don't want to pay for it, and most of what we do doesn't have a macro anyway. So here I am, trying to update my lab to the Microsoft Age in computing. Anyway, thanks in advance for the help Derk.


    Does anybody know how to calculate a Duncan Multiple Comparison or have a macro to do so? I've been asked to look into it at work, but have had difficulty in finding anything about it (I've read it's not a well-used test, but it's the standard in my lab, and I don't have the say). I will go to the literature, but I wanted to drop a note to see if anybody has any macro that would help me. Thanks.


    Is it possible to return multiple variables from a single function or retrieve variables that have been calculated in that function?

    I'm particularly interested in post <a href&gt;</a&gt; where I the one-tailed and two-tailed fisher P values are calculated. The function returns the two-tailed, but I want to get the one-tailed as well without running a different function.

    Is that possible?


    What's the code for setting the number format on a variable before showing it on a sheet? I know I can use numberformat after I put it in a sheet, and I know I could multiply it by 1000 or something and use integer and then divide by 1000 again, but I was just wondering if excel has this function built in.

    Thanks, Kabong

    For anyone who runs across this post later wishing to have a macro calculate Fisher Exact for them, both of these programs work. I've found that my version is the same as another program that we use, and Derk says his represents a statistically more sound method. Either way, please use these codes as we put way too much time into them (and we want to save you the trouble). Derk, thanks again for all the help. Hope to work with you again!


    Here's what I've got now that I threw in a couple of lines to take care of a few cases where the distribution is symmetrical.

    Function FisherExact(r As Range)

    Dim i As Long, p() As Double, s As Double, n As Long, x(1 To 2, 1 To 2) As Double, y(1 To 2, 1 To 2) As Double
    Dim a As Double, b As Double, c As Double, d As Double, BaseProb As Double

    If r.Rows.Count <> 2 Or r.Columns.Count <> 2 Then
    FisherExact = "#Inputnot2x2"
    Exit Function
    End If

    n = Application.Min(r)
    ReDim p(0 To n)

    'put smallest value in x(1,1)
    If r(1, 1) = n Then
    x(1, 1) = r(1, 1)
    x(2, 1) = r(2, 1)
    x(1, 2) = r(1, 2)
    x(2, 2) = r(2, 2)
    ElseIf r(1, 2) = n Then
    x(1, 1) = r(1, 2)
    x(2, 1) = r(2, 2)
    x(1, 2) = r(1, 1)
    x(2, 2) = r(2, 1)
    ElseIf r(2, 1) = n Then
    x(1, 1) = r(2, 1)
    x(2, 1) = r(1, 1)
    x(1, 2) = r(2, 2)
    x(2, 2) = r(1, 2)
    x(1, 1) = r(2, 2)
    x(2, 2) = r(1, 1)
    x(1, 2) = r(1, 2)
    x(2, 1) = r(2, 1)
    End If

    a = x(1, 1) + x(1, 2) + 1
    b = x(2, 1) + x(2, 2) + 1
    c = x(1, 1) + x(2, 1) + 1
    d = x(1, 2) + x(2, 2) + 1

    'calculate observed table probability
    With Application
    p(0) = Exp(.GammaLn(a) - .GammaLn(x(1, 1) + 1) + .GammaLn(b) - .GammaLn(x(1, 2) + 1) + .GammaLn(c) - .GammaLn(x(2, 1) + 1) + .GammaLn(d) - .GammaLn(x(2, 2) + 1) - .GammaLn(a + b - 1))
    End With

    BaseProb = p(0)
    s = p(0)

    'store table for second tail calculation
    y(1, 1) = x(2, 1)
    y(1, 2) = x(2, 2)
    y(2, 1) = x(1, 1)
    y(2, 2) = x(1, 2)

    'calculate more extreme table probabilities
    For i = 1 To n
    p(i) = p(i - 1) * x(1, 1) * x(2, 2)
    x(1, 1) = x(1, 1) - 1
    x(1, 2) = x(1, 2) + 1
    x(2, 1) = x(2, 1) + 1
    x(2, 2) = x(2, 2) - 1
    p(i) = p(i) / (x(2, 1) * x(1, 2))

    'only add if probability is less than observed table
    If p(i) < p(0) Then
    s = s + p(i)
    End If
    Next i

    If y(1, 1) + y(1, 2) = y(2, 1) + y(2, 2) Or y(1, 1) + y(2, 1) = y(1, 2) + y(2, 2) Then
    FisherExact = 2 * s
    GoTo Ending
    End If

    'calculate second tail using same method
    If y(1, 1) &gt; y(2, 2) Then
    n = y(2, 2)
    n = y(1, 1)
    End If

    ReDim p(0 To n)
    p(0) = BaseProb

    For i = 1 To n
    p(i) = p(i - 1) * y(1, 1) * y(2, 2)
    y(1, 1) = y(1, 1) - 1
    y(1, 2) = y(1, 2) + 1
    y(2, 1) = y(2, 1) + 1
    y(2, 2) = y(2, 2) - 1
    p(i) = p(i) / (y(2, 1) * y(1, 2))

    If p(i) < p(0) Then
    s = s + p(i)
    End If
    Next i

    FisherExact = s

    End Function

    I'm reading from Biostatistical Analysis, Second Ed, by Jerrold Zar. He says that after the probability on the one-tail side is done, the second tail is calculated by talking the most extreme case of the smaller number of the pair not yet addressed. Then he says to add the probabilities of all tables having probabilities at least as small as that of the table originally observed. I think this is how I've been calculating the summed probability. I'm not sure where your definition comes from, though I'd be interested in comparing in case my reference is in error.

    Derk, I was reading over a stats book and I found that if either R1=R2 or C1=C2 (these are row and column sums) then the two-tailed is simply twice the one-tailed. In the two cases that you point out that mine deviates from correct values, R1=R2. If I stop the program after it has found one-tailed using your method and multiply that by 2, it looks to correspond with accepted tables. Let me know what you think. I'll toss this into my code and repost.