Posts by soulfire21

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

Did you declare them outside of the subroutine?

Public varWorkbooksAddress As String 
Public varWorkbooksRow As String 
Public varWorkbooksCol As String 


Private Sub Workbook_Open() 
     'THIS SUBROUTINE LOADS THE INITIAL PARAMETERS WHEN THE WORKBOOK OPENS
     'TODO:  Need to set up error trapping to warn &/or escape if fails.
     'DEFINING VARIABLES
     
     'SETTING VARIABLES
     'varWorkbooksAddress = Cell location of first workbook to track
    Sheets("Parameters").Select 
    Cells.Select 
    Selection.Find _ 
    (What:="Worksheet 1", After:=ActiveCell, LookIn:=xlFormulas _ 
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
    MatchCase:=False, SearchFormat:=False).Activate 
    varWorkbooksAddress = ActiveCell.Address 
    varWorkbooksRow = ActiveCell.Row 
    varWorkbooksCol = ActiveCell.Column 
     
     'FINISH UP
    Cells(1, 1).Select '  PARKING  Cell
End Sub

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:

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

Function FuzzyFind(lookup_value As String, tbl_array As Range) As String    Dim i As Integer, str As String, Value As String
    Dim a As Integer, b As Integer, cell As Variant
    For Each cell In tbl_array
      str = cell
      For i = 1 To Len(lookup_value)
        If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then
          a = a + 1
          cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999)
        End If
      Next i
      a = a - Len(cell)
      If a > b Then
        b = a
        Value = str
      End If
      a = 0
    Next cell
    FuzzyFind = Value
End Function

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"]

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

1

Raw List

Your Standard

FuzzyLookup Result

2

EXCEL CORPORATION LUMBER DBE

EXCEL CORPORATION LUMBER DBE

EXCEL CORPORATION LUMBER DBE

3

EXCEL CORPORATION LU DBE

QUICK CO AND FIXTURES

EXCEL CORPORATION LUMBER DBE

4

QUICK CO AND FIXTURES

QUICK CO AND FIXTURES

5

QUICK COAND FIXTURES

QUICK CO AND FIXTURES

[/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"]

Excel Entry

Formula Result

EXCEL AMERICAN COMPANY S.A.

EXCEL AMERICAN COMPANY SA

EXCEL AMERICAN COMPANY SA.

EXCEL AMERICAN COMPANY SA

EXCEL AMERICAN COMPANY, S.A.

EXCEL AMERICAN COMPANY SA

ASSOCIATION, INDUSTRIAL

ASSOCIATION INDUSTRIAL

ASSOCIATION INDUSTRIAL

ASSOCIATION INDUSTRIAL

L.A. COMPANY CO

LA COMPANY CO

LA COMPANY CO

LA COMPANY CO

[/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.

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:

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"]

Hours Worked

Divide by 24

HH:MM

3.55

0.147916667

3:33

2.06

0.085833333

2:03

TOTAL

5.61

0.23375

5:36

[/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:

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:

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]

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]

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:

• Setting VBAProject password without using SendKeys
• Unprotecting VBAProject using a password you know (with SendKeys)
• Relevant StackOverflow thread

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:

declare @input varchar(10)




set @input = 'I11.1'




select * from items 
where parent = (select parent from items where item = @input)

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:

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):

Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
    '"Target" refers to a range object, check to see if it is the appropriate cell being changed
    If Target.Address = "$D$14" Then
        'Check the value of cell D14
        Select Case Target.Value
            'If value is between 1 and 1000, inclusive, show the picture
            Case 1 To 1000
                Worksheets(1).Shapes("Picture 1").Visible = True
            'If the value is 0, hide the picture
            Case 0
                Worksheets(1).Shapes("Picture 1").Visible = False
            'If the value is anything else, hide the picture
            Case Else
                Worksheets(1).Shapes("Picture 1").Visible = False
        End Select
    End If
End Sub

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.

Select Case Target.Value
            'If value is between 1 and 1000, inclusive, show the picture
            Case 1 To 1000
                Worksheets(1).Shapes("Picture 1").Visible = True
            'If the value is 0, hide the picture
            Case 0
                Worksheets(1).Shapes("Picture 1").Visible = False
            Case Is > 1000
                Worksheets(1).Shapes("Picture 2").Visible = True
            'If the value is anything else, hide the picture
            Case Else
                Worksheets(1).Shapes("Picture 1").Visible = False
        End Select

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.

Sub DataSize()
    
    Dim i As Integer
    Dim cht As Chart
    Dim s As Series
    Dim p As Point
    Dim vals As Variant
    
    Set cht = ActiveSheet.ChartObjects("Chart 2").Chart 'Rename Chart 2 to whatever your chart is named
    Set s = cht.SeriesCollection(1) 'My chart had only 1 series, you may have to change this number if your chart has more than 1 data series
    
    vals = s.Values
    
    For i = LBound(vals) To UBound(vals)
        If vals(i) > 10 Then
            With s.Points(i)
                .MarkerSize = 30
            End With
        End If
    Next i
    
    
End Sub

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:

Sub DataSize()
    
       Dim i As Integer    Dim cht As Chart
    Dim s As Series
    Dim p As Point
    Dim vals As Variant
    
    Set cht = ActiveSheet.ChartObjects("Chart 2").Chart 'Rename Chart 2 to whatever your chart is named
    Set s = cht.SeriesCollection(1) 'My chart had only 1 series, you may have to change this number if your chart has more than 1 data series
    
    vals = s.Values
    
    For i = LBound(vals) To UBound(vals)
        Select Case vals(i)
            Case 1 To 10
                With s.Points(i)
                    .MarkerSize = 8
                End With
            Case 11 To 20
                With s.Points(i)
                    .MarkerSize = 12
                End With
            Case Is > 20
                With s.Points(i)
                    .MarkerSize = 12
                End With
        End Select
    Next i
End Sub

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!