PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Excel Tips and Tricks-VBForums
Results 1 to 3 of 3

Thread: Excel Tips and Tricks

  1. #1

    Thread Starter
    Frenzied Member zaza's Avatar
    Join Date
    Apr 2001
    Location
    Borneo Rainforest Habits: Scratching
    Posts
    1,486

    Excel Tips and Tricks

    Some handy tips and tricks for using Excel, which can make life a lot easier and allow you a lot more functionality in your spreadsheet apps.



    Comboboxes: Yes you can use a combobox control to display data dropdowns and code it in VBA, but sometmes it just isn't necessary. You can use Excel's built-in combo equivalent, by setting a validation on a cell.

    Usage:

    Select a cell and go to Data, Validation menu. On the Settings tab, choose Allow: List and then specify your source range. Voila, an in-cell dropdown. You could combine this with...


    Detecting cell changes: Each worksheet has Change and SelectionChange events which pass to the code a range called Target. This allows you to determine which cell has been changed (in the case of the former) or selected (in the case of the latter).

    Usage:
    VB Code:
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. MsgBox (Target.Row & ", " & Target.Column)
    3. End Sub
    Tells you the row and column of the cell that was changed.


    The SpecialCells method: Matches cells according to specific conditions, eg empty cells, cells with numbers or formulae, last used cell etc.

    Usage:
    VB Code:
    1. ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
    Activates the last used cell.


    The WorksheetFunction object: Allows access to all those handy Excel formula functions that you type in a cell function. All the maths functions, statistics, etc can now be accessed through your code so that, for example, you don't have to use an extra cell or a big loop to calculate the average of a range, just use the WorksheetFunction to put it in a variable.

    Usage:
    VB Code:
    1. Dim MyRangeAverage as Double
    2. MyRangeAverage = WorksheetFunction.Average(Range("A1:A10"))
    Averages the numbers in the range A1 to A10.


    The Application object: Very useful to find out information about the current application, such as the current user ID, the start up path, the templates path etc.

    Usage:
    VB Code:
    1. Msgbox (Application.UserName & ", " & Application.StartUpPath)
    Displays the Office username and startup path of the current user.


    Dialog boxes: VBA supports all sorts of dialog boxes, accessed using the Dialog object. These run built-in dialog boxes as though you'd clicked the button yourself. So the Open dialog box will actually open a file, the Chartwizard box will set up a graph etc.

    Usage:
    VB Code:
    1. dlgAnswer = Application.Dialogs(xlDialogOpen).Show
    Runs the Open dialog box, and opens the selected file. Returns True.

    Sometimes, however, you'd like to present your user with a file dialog that acts as a picker rather than as an actual Open / Save box. For this you can use the FileDialog, and then code the action to take on the basis of the selection.

    Usage:
    VB Code:
    1. Dim dlgOpen As FileDialog
    2. Set dlgOpen = Application.FileDialog(FileDialogType:=msoFileDialogOpen)
    3. With dlgOpen
    4.  .AllowMultiSelect = True
    5.  .Show
    6.  End With
    7.  For Each i in dlgopen.selecteditems
    8.  MsgBox (i)
    9.  Next i
    Opens a file picker dialog and returns the filenames of all the selected files.


    The Find method: You can use the Find method of a range to pick out the data that you want. The Find method returns a cell if the data is found, or Nothing if not.

    Usage:
    VB Code:
    1. With Worksheets(1).Range("A1:B500")
    2.     Set c = .Find(2, lookin:=xlValues)
    3.     If Not c Is Nothing Then
    4.         MsgBox (c.Row & ", " & c.Column)
    5.     End If
    6. End With
    Finds the number 2 in the range A1 to B500 and returns the row and column where it first occurs.


    Excel Graphing: Rather than spend hours generating multiple graphs by hand, or for situations in which you might want to select from a list of parameters and update a graph accordingly, you can simply generate the graph through code and then change the SeriesCollections to display whatever data you might want.

    Usage:
    See the link in my sig


    Setting sheet visibility: Not quite the same as actually setting permissions, because this doesn't involve locking the sheet but it can be a handy way to determine what users can and cannot see.

    Usage:
    Add a worksheet and call it Settings. In column A, list all the Office usernames of your admin users. Then place some code in the Workbook Open event:

    VB Code:
    1. Set c = Worksheets("Settings").Range("A:A").Find(Application.UserName)
    2. If Not (c Is Nothing) Then
    3.     Worksheets("Sheet2").Visible = True
    4.     Worksheets("Settings").Visible = True
    5. Else
    6.     Worksheets("Sheet2").Visible = False
    7.     Worksheets("Settings").Visible = False
    8. End If
    Matches the current user to a list on the Settings worksheet. If the user is not on the list, then neither the Settings sheet nor Sheet2 are visible.
    I use VB 6, VB.Net 2003 and Office 2010



    Code:
    Excel Graphing | Excel Timer | Excel Tips and Tricks | Add controls in Office | Data tables in Excel | Gaussian random number distribution (VB6/VBA,VB.Net) | Coordinates, Vectors and 3D volumes

  2. #2
    Addicted Member
    Join Date
    Feb 2005
    Posts
    163

    Re: Excel Tips and Tricks

    Using the find method how can you activate the found cell?

  3. #3

    Thread Starter
    Frenzied Member zaza's Avatar
    Join Date
    Apr 2001
    Location
    Borneo Rainforest Habits: Scratching
    Posts
    1,486

    Re: Excel Tips and Tricks

    If you've used:

    VB Code:
    1. Set c = .Find(....)


    then you can use:


    VB Code:
    1. Cells(c.row, c.column).activate


    zaza
    I use VB 6, VB.Net 2003 and Office 2010



    Code:
    Excel Graphing | Excel Timer | Excel Tips and Tricks | Add controls in Office | Data tables in Excel | Gaussian random number distribution (VB6/VBA,VB.Net) | Coordinates, Vectors and 3D volumes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width