Posts by soulfire21

    Re: Gathering Values on Workbook Open for use later in subroutines


    Did you declare them outside of the subroutine?


    Depending on the size of your application, you may consider using a separate module to keep track of only your global variables. This way it is easy to keep track of them and have them in one place.


    Global variables will only last the lifetime of the application, that is as long as that particular Excel workbook is open. If you'll need these values after the user has closed and re-opened the workbook, you may consider a hidden worksheet where you record variable values (perhaps in the Workbook_Save event, for example) and then you can read them on open the next time.

    Re: Field for BDP formula in Excel using Bloomberg


    Try "EQY_SH_OUT"


    From [URL="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0CCAQFjAA&url=http%3A%2F%2Fwww.xavier.edu%2Fwilliams%2Fcenters%2Ftrading-center%2Fdocuments%2Fbloomberg%2Fexcel%2FBloomberg_Equity_Spreadsheet_Application.xls&ei=RtmMU_2zDcGryAS56YCwBw&usg=AFQjCNFsYUbdcRjdBULdgHaQJd6y1vvyPg&sig2=DTMssMiQdNOTNBX2RiPaYA&bvm=bv.68191837,d.aWw"]this sample workbook[/URL] from Xavier University.

    Re: Adding an ENTER into a cell after 10 characters have been input


    I don't think this is particularly possible. The reason is that there is no VBA code (to my knowledge) that can be run while the cell is being edited.


    As a workaround, you could insert a text box and then add the following code to the worksheet you've added the text box to:

    Code
    Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)    
    If Len(TextBox1.Value) > 9 Then
            Range("A65536").End(xlUp).Offset(1, 0).Value = TextBox1.Value
            TextBox1.Value = ""
        End If
    End Sub


    What this would do is check the length of whatever is in TextBox1 and as soon as it reaches 10 characters it would automatically add it to the next open cell in column A (change that to whichever column you need).


    I've attached a quick example.
    forum.ozgrid.com/index.php?attachment/60759/

    Re: Parsing data


    You could try this:
    =MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5,6,7,8,9})),LEN(A1))


    Where A1 has the column with the original text.


    MID extracts the middle of a string (in this case A1) from a starting position to an ending position. The MIN(FIND combination is used to determine the starting index of the first numeric character in the string. LEN(A1) is a quick and easy way to tell the MID function to go the remainder of the way down the string.

    Re: Can one add fraction to =Date( ?


    This is a bit tricky with years because of leap years and months not having the same number of days.


    A simple way would be to assume there are 365.25 days in a year (because of leap years) and use this:
    =A1 + 365.25*B1 (where cell A1 has your date and cell B1 has the decimal years)


    For your example of adding 8.6 years to 2/12/1961 the above formula returns 9/19/1969 which seems appropriate.


    There is a good thread on this here which reiterates the formula I just gave and discusses a couple of other options.

    Re: Day of any given date


    I used this and it worked fine:


    =CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")


    In this formula, cell A1 contains the date (I used today's date 5/30/2014) to test. The WEEKDAY function defaults to assume 1 = Sunday, 2 = Monday, 3 = Tuesday, etc. that's why it is formatted as such in the CHOOSE statement. You can re-order these if necessary but I suspect it isn't.


    Edit:
    I think Luke's suggestion is better. You can just nest them up such as =TEXT(DAY(A1),"dddd") and that should do the trick.

    Re: Similar texts with minor differences - How to get just ONE (a standard one)


    You would have to do something in order to determine a standard to compare to. It may be a list you receive from elsewhere (i.e. obtain a list from accounts receivable if this were a billing situation, find the names in another table in your database, etc.) or you may have to do the cleaning as you describe anyways to determine a standard.


    I can't answer much more specifically without knowing more about the design of your database and which (if any) database management system (i.e. SQL Server) you are using.

    Re: Similar texts with minor differences - How to get just ONE (a standard one)


    You may consider using the free Excel add-in from Microsoft to perform fuzzy lookups.


    Otherwise, you would probably have to use a custom VBA function. If you're interested in the VBA route, try this code (from stackoverflow:(



    • Open the VB Editor (ALT+F11)
    • On the left hand panel, right click your VBAProject for your workbook
    • Select Insert --> Module
    • Open Module1 and paste in the code below



    What this will do is attempt to find a string that is close in value to another, or a "fuzzy" lookup.


    You can then use this function like any other function on your worksheet by typing =FuzzyLookup into a cell and providing the proper arguments.
    [TABLE="class: grid, width: 500"]

    [tr]


    [TD="align: center"][/TD]
    [TD="align: center"]A[/TD]
    [TD="align: center"]B[/TD]
    [TD="align: center"]C[/TD]

    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Raw List

    [/td]


    [td]

    Your Standard

    [/td]


    [td]

    FuzzyLookup Result

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    EXCEL CORPORATION LUMBER DBE

    [/td]


    [td]

    EXCEL CORPORATION LUMBER DBE

    [/td]


    [td]

    EXCEL CORPORATION LUMBER DBE

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    EXCEL CORPORATION LU DBE

    [/td]


    [td]

    QUICK CO AND FIXTURES

    [/td]


    [td]

    EXCEL CORPORATION LUMBER DBE

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    QUICK CO AND FIXTURES

    [/td]


    [td][/td]


    [td]

    QUICK CO AND FIXTURES

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    QUICK COAND FIXTURES

    [/td]


    [td][/td]


    [td]

    QUICK CO AND FIXTURES

    [/td]


    [/tr]


    [/TABLE]


    The results in the "FuzzyLookup" result column are what comes of using the function =FuzzyFind on the data in the Raw List.


    So you would use the formula as so:
    =FuzzyFind(A2,$B$2:$B$3)
    =FuzzyFind(A3,$B$2:$B$3)
    =FuzzyFind(A4,$B$2:$B$3)
    =FuzzyFind(A5,$B$2:$B$3)
    etc.


    You would type the formula in cells C2, C3, C4, and C5 above. This compares whatever cell has your raw data (in the above case cells A2, A3, A4, and A5) against your defined list of standards (cells B2 and B3 above) and will place the best match in the standards into cells C2-C5.

    Re: IF not


    I'm not sure I fully understand the entire logical structure you are attempting, however, you can nest IF statements to achieve the result you describe.


    =IF(AND(F2="YES",G2="YES"),"YES",IF(C2>0, "NO",""))


    This statement will first evaluate whether F2 and G2 are = "YES". If both are = "YES" then "YES" will be displayed, regardless of the value in C2. If the first condition returns a FALSE (i.e. if either F2 or G2 are not "YES") then the next condition will be evaluated. The next condition, in this case, is whether or not the value of C2 is greater than 0. If this is true, a "NO" will be displayed, else an empty cell will be displayed.

    Re: Similar texts with minor differences - How to get just ONE (a standard one)


    You could use the SUBSTITUTE function to substitute nothing for commas and periods.


    For example, if in cell A1 you had EXCEL AMERICAN COMPANY S.A., using =SUBSTITUTE(SUBSTITUTE(A1,".",""),",","") would return EXCEL AMERICAN COMPANY SA in whatever cell you put the formula.


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Excel Entry

    [/td]


    [td]

    Formula Result

    [/td]


    [/tr]


    [tr]


    [td]

    EXCEL AMERICAN COMPANY S.A.

    [/td]


    [td]

    EXCEL AMERICAN COMPANY SA

    [/td]


    [/tr]


    [tr]


    [td]

    EXCEL AMERICAN COMPANY SA.

    [/td]


    [td]

    EXCEL AMERICAN COMPANY SA

    [/td]


    [/tr]


    [tr]


    [td]

    EXCEL AMERICAN COMPANY, S.A.

    [/td]


    [td]

    EXCEL AMERICAN COMPANY SA

    [/td]


    [/tr]


    [tr]


    [td]

    ASSOCIATION, INDUSTRIAL

    [/td]


    [td]

    ASSOCIATION INDUSTRIAL

    [/td]


    [/tr]


    [tr]


    [td]

    ASSOCIATION INDUSTRIAL

    [/td]


    [td]

    ASSOCIATION INDUSTRIAL

    [/td]


    [/tr]


    [tr]


    [td]

    L.A. COMPANY CO

    [/td]


    [td]

    LA COMPANY CO

    [/td]


    [/tr]


    [tr]


    [td]

    LA COMPANY CO

    [/td]


    [td]

    LA COMPANY CO

    [/td]


    [/tr]


    [/TABLE]


    If you have further character to filter out, you can continue to nest them. I am unsure what the nesting limit is though, so this may not work if you have tens of characters to replace.


    For example, say you also need to filter out ampersands (&). You could add this as follows:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),",",""),"&",""))


    Additionally, you may want to throw on a TRIM on the entire thing just to remove extra spaces as so:
    =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",""),",",""),"&",""))

    Re: Change the background color


    The "Office" theme Orange, Accent 6 is RGB(247,150,70). To find this I highlighted a cell, colored it the orange color from 'Office' theme and then used the drop down from the fill option --> More Colors and read the RGB values from that.


    Code
    ActiveCell.Interior.Color = RGB (247,150,70)


    I'm not exactly sure what 'Lighter 60%' is as I don't do much with themes. I quickly recorded a macro and when I selected a cell and then selected the orange color from the "Office" theme and this is the code returned:

    Code
    Range("D3").Select    
    With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

    Re: Convert decimal time into real time


    2.06 hours + 3.55 hours is not 6 hours and 1 minute, I don't think.


    Here's my reasoning:
    2.06 hours = 2 hours + 0.06 hour * 60 min / 1 hour = 2 hours 3 minutes
    3.55 hours = 3 hours + 0.55 hour * 60 min / 1 hour = 3 hours 33 minutes


    2 hours 3 minutes + 3 hours 33 minutes = 5 hours 36 minutes.


    I may not be understanding you correctly here though.

    Re: Convert decimal time into real time


    To convert decimal time (i.e. 5.06 hours) into time (i.e. 5:03) you can simply divide the decimal hours by 24 and then format that cell as time HH:MM. To do the reverse, just multiply the HH:SS by 24 to return back to decimal time.


    [TABLE="class: grid, width: 500"]

    [tr]


    [td][/td]


    [td]

    Hours Worked

    [/td]


    [td]

    Divide by 24

    [/td]


    [td]

    HH:MM

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    3.55

    [/td]


    [td]

    0.147916667

    [/td]


    [td]

    3:33

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    2.06

    [/td]


    [td]

    0.085833333

    [/td]


    [td]

    2:03

    [/td]


    [/tr]


    [tr]


    [td]

    TOTAL

    [/td]


    [td]

    5.61

    [/td]


    [td]

    0.23375

    [/td]


    [td]

    5:36

    [/td]


    [/tr]


    [/TABLE]


    Where the HH:MM column would read "3 hours and 33 minutes" for the first record, "2 hours and 3 minutes" for the second record, and "5 hours and 36 minutes" for the total.


    "Five point six one" hours is the same thing as five hours and 36 minutes.


    Adding rows/columns that are formatted as HH:MM will return your result in HH:MM as well.

    Re: Fixing an Invalid Qualifier error in VBA


    When qualifying your references, you must do it like this:

    Code
    Worksheets("Focus").Range("A2")
    Worksheets("Data").Range("A1:K100000")


    [ATTACH=CONFIG]60632[/ATTACH]


    Additionally you can use 'code names' instead of using Worksheets("WorksheetName") to reference sheets as well. In the image I posted above (from the VB Editor) you'll see that the worksheet "Focus" has a Sheet1 to the left of it and the worksheet "Data" has Sheet2 to the left. You can use Sheet1 and Sheet2 to qualify references as well. These are handy because they will not change (whereas worksheet names and indices sometimes do). Using the image for reference:

    Code
    Sheet1.Range("A2") 'This is equivalent to Worksheets("Focus").Range("A2")
    Sheet2.Range("A1:K100000") 'This is equivalent to Worksheets("Data").Range("A1:K100000")


    There's some other things that stand out to me in your code:

    • What is Table1 and Table2? (These aren't declared anywhere that I can see so I don't know what they are right off the bat)
    • You should be assigning Bank_row using an equal sign:


    [INDENT=2]

    Code
    Bank_row = Worksheets("Focus").Range("A3").Row 'Alternatively you could just set it to 3 because that's the row of A3
    'Or
    Bank_row = 3

    [/INDENT]


    • Same thing with Bank_clm


    [INDENT=2]

    Code
    Bank_clm = Worksheets("Focus").Range("A3").Column'Alternatively you could just set it to 1 because that's the column of A3
    'Or
    Bank_row = 1


    [/INDENT]
    If you want more help I'd advise you to explain what you're trying to do exactly and I (or another person) can be of more assistance.

    Re: Password Protect the VBA code using VBA code


    Makes perfect sense, unfortunately I didn't make that simple connection!


    There are a couple of ways to do it, but they are laborious and require you to open workbooks in separate instances of Excel.


    Here are some resources:


    A couple things that weren't immediately obvious to me: some of these solutions require the 'Microsoft Visual Basic for Applications Extensibility 5.3' reference as well as changing your trust settings (File --> Options --> Trust Center --> Trust Center Settings... --> Macro Settings --> Select 'Trust access to the VBA project object model')


    What you request is possible, but it seems like an entirely new instance of Excel must be created in order to do this, which I think makes it not worth the effort because in the end you would have to: save the current user's workbook, create a new instance of Excel, close the current workbook, open the one you just saved, etc.



    It's a lot of effort for minimal gain in my opinion, but maybe you can find something I did not in these resources.

    Re: Password Protect the VBA code using VBA code


    Do non-admin users need access to the VBA code?


    I only ask because I am wondering why you want (or need) this done using VBA as opposed to just using the GUI to do so.


    You can currently protect your VBA code by:

    • Opening the VB Editor (ALT+F11)
    • Select Tools --> VBAProject Properties...
    • Go to the 'Protection' tab
    • Check the 'Lock project for viewing' option
    • Enter a password (and password confirmation)
    • Click 'OK'


    This will protect your VBA code in the workbook from all users who do not know the password you specified.

    Re: MS Query in Excel - Criteria


    You might consider using sub-queries in this instance.


    I'm mostly familiar with SQL Server, so here is an example:


    The user would be providing that input item @input. The query inside the parenthesis is evaluated first. This selects the parent from the table 'items' where the item is whatever the user provides. The outer query is then executed which selects all from the items table that have the parent (which was evaluated in the sub-query).


    I am unsure if similar functionality exists when using Microsoft Query but that might be a nudge in the right direction.


    After playing around with Microsoft Query I was able to get the following to work:

    SQL
    SELECT items.item, items.parent
    FROM `C:\TEST\test.accdb`.items items
    WHERE items.parent IN (SELECT items.parent FROM  `C:\TEST\test.accdb`.items items WHERE items.item = 'I11.1')


    Just change the 'I11.1' to be whatever expression you need, or however the user is inputting the value.

    Re: Show picture when a cell value is between set values


    I would use the Worksheet_Change event to accomplish this. To use worksheet methods, open the VB Editor and double click the worksheet you want the code to work on and use the following code (or similar):



    The .Visible property will display (or hide) an image, wherever you have put that image on the sheet.


    You can add as many criteria to the SELECT ... CASE statement as you like. For example, if you wanted to show a different picture if the value is over 1000 you could implement that in the same SELECT statement.



    Also, to achieve the formatting options I think you want I would use the cell formatting option.


    Right-click the cell with the currency in it --> Format Cells... --> Select 'Currency' and then in the dropdown select £ English (U.K.)


    This will keep the value as a number and allow you to perform mathematical comparisons while displaying the value as you want it.


    Here is a simple example that I used to test this code:
    forum.ozgrid.com/index.php?attachment/60294/

    Re: Vary data marker size by value


    This is possible, here is a snippet of code that will read values on the chart object "Chart 2" (change this to whatever you need) and make the point size = 30 (very large) if the value is greater than 10.



    If you want to have multiple sizes based on a value, than I would suggest using a CASE statement in place of the If...Then statement. The code below will assign a different size based on values, starting with values from 1-10, then 11-20, then > 20.


    It would look something like this:

    Re: Unable to specify worksheets(i)?


    Quote from al4xms;710110

    Thanks, that is very informative both of you
    just for completeness..what changes the index of the sheet? Its position in the list on the project window? - ignore that point, i missed it in the tutorial! seems the most dangerous in terms of absoluteness


    I will absolutely be using the code name where possible from now on


    The index of the sheet is the order in which the worksheet tabs are displayed along the bottom of the window.


    So if I had, in this order, Sheet1, Sheet2, Sheet3, Sheet4 then the indices would be 1, 2, 3, and 4, respectively. To reference Sheet3 I could definitely use the Worksheets(3) reference.


    If I moved Sheet3 to be first, so the order was now Sheet3, Sheet1, Sheet2, Sheet4 then the index of Sheet3 becomes 1, instead of 3 as it was above. If I used Worksheets(3) here I would be referring to Sheet2.


    I hope that is clear, happy coding!