Posts by shades

    Re: Writtennumber()


    Here is J-Walk's code:


    Re: Function "replace" Missing For Mac Vb


    Quote from ByTheCringe2

    Moving this to Technical Issues forum.


    Should it be moved there?


    This is strictly XL and VBA related.


    Does this mean that someone who has to change code to account for XL 97 requires that the thread be moved to Technical Issues?

    Re: Function "replace" Missing For Mac Vb


    Howdy. In order to make the transition to using VBA for Mac, you have to make the changes in your code so that it is usable for Excel 97 on the Windows side (both Mac Excel X/2004 and XL 97 were based on VB 5).

    Re: Writtennumber()


    Quote from zapacoman

    I found the following and pasted it into the VBA page for that particular sheet...but can't get it to work...


    Code
    Function SPELLDOLLARS(cell) As Variant 
    ...
    End Function


    That is the one from Walkenbach's book/CD. How are you referencing this in the spreadsheet?

    Re: VBA Conditional Format Based On Multiple Conditions


    Howdy. yes it will take VBA.


    Here is one approach. If you set up the color that you want for each condition on the CFControl worksheet (I have cities, you would need your conditions, and then the appropriate color index).


    The code is automatic on the Data worksheet and will use the CFControl worksheet to change color as needed.



    If you are using formulas and not typing in the values, then you would need something to trigger the response to change.


    (Note, I put the values from CFCOntrol on the Data worksheet, just to give you an idea of what to enter int he cells.

    Re: Change Date Formulas To Values


    Just a note: You set calculation to manual at the beginning of the code. Are you wanting to set it to Automatic at the end? If so, you didn't, it continues as Manual.

    Re: Data Analysis


    Quote from shades

    I chose red font, but in your case change that red font to red font. That way if there are more they will be visible, and fewer, they will appear invisible.


    I wrote this incorrectly. In the Conditional Formatting dialog, change the red font to white font, so that they will "disappear" against the white background of the cell.


    Sorry for the confusion.

    Re: File Size - Reduce?


    Quote from royUK


    What I really wondered was if you have your data in a classic Excel Table Format?


    That would be my first question.


    My second would be: What kind of formulas? That can make a difference.


    My third question: What is the sequence of formulas and formulas relative to worksheets. You always want formulas pulling from behind, never back and forth.


    Probably one of the biggest issues with formulas is the sequence of calculation. Generally, you should put raw data tabs/worksheets with the first in the sequence. Thus, I label all sheets this way:


    data sheets:


    A0_Data1
    A1_Data2
    A2_Data3
    etc.


    Then formula worksheets:


    B0_Formulas1
    B1_Formulas2
    B2_Formulas3


    etc.


    The key is that you want formulas always looking backward never forward (thus, B1_Formula can look at any data sheet and B0_Formulas, but should not get data from B2_Formulas3). I have made significant changes and reduced file size and speed of calculations using this. See this for more information:


    See Charles Williams site for more on this topic.

    Re: Data Analysis


    Okay, this might be more complicated, but it will do what you want.


    There is a formula in cell A4


    =D4-C4


    Then I put numbers in column A begininng with cell A6 with 0, and then adding one to each cell down.


    Then in cell C6 I put this formula:


    =C$4+$A6


    and copied down to row 20.


    In cell E6, I put this formula:


    =E$4


    and copied down to row 20.


    Then on the Work worksheet, I added a column A, and dynamic named range for it.


    =OFFSET(Dates,0,-1)


    In cell F6 I put this formula:


    =INDEX(Data,MATCH(C6&E6,DataWS_LU,FALSE),6)


    and copied down to row 20.


    On worksheet Sheet3 I would hide column A, then I added conditional formatting for cells C9:F20. Select those cells, then in the dialog box, on far left, choose dropdown "Formula Is", then put this in the box to the right:


    =$A9>$A$4


    I chose red font, but in your case change that red font to red font. That way if there are more they will be visible, and fewer, they will appear invisible.


    If you want to protext the sheet, then the users won't be able change any of this.


    (I deleted some rows on the Work tab because of the file size)


    HTH

    Re: Data Analysis


    Howdy.


    What about some formulas and dynamic named ranges instead of VBA?


    I changed your sheet name to Work, just for simplicity. Then Control has a some named ranged for using in drodowns. Also, dynamic named ranges on Work (based on Dates).


    Then on Sheet3, I placed the Start Date with a dropdown, End Date also dropdown, and Weather Station. Then in Cell F5 I put this formula:


    =SUMPRODUCT((Dates>=$C$4)*(Dates<=$D$4)*(DataWS=$E$4),DataHDD)


    Add other combinations as needed to make it a dashboard. I would probably setup a couple of intermediate worksheets to feed a dashboard, but this gives you an idead.


    You could make adjustments as necessary, and even protect the worksheet so they can only change dropdown cells.


    This might be easier to maintain than VBA (depending on your skill level for each).


    Just a thought.

    Re: Vba, R.i.p?


    Quote from Derk

    My understanding is that for the Macintosh version Office 2008, VBA will not be supported. Moreover, I believe there will be no replacement for it beyond the more general Applescript. Microsoft's announced reasoning is it is too hard to make the conversion of the clunky Mac version of VBA to the more modern coding basis Office will be using.


    Yeah, I may have seen the last update for myself (Office 2004). Since it runs VBA (as long as it is compatible with Office 97, it works on Mac). But with the 2008, it will not even run. At work we upgraded to 2003 just in the last 18 months (65,000 employees). So if they support VBA for 10 years, I'll be long gone into retirement. So may not bother with VB.net.


    I know some companies that have depended on VBA for Macs (not heavy duty, but essential tasks). There is no way they can upgrade.


    I wonder if a third-party language might offer the best cross-platform potential (Python, Ruby, etc.).