Posts by Fencliff

    Re: Use Of Hash Sign In Macro

    Quote from Dave Hawley

    Oh, yes, soooo much easier to read. Except the 1 letter variables like x, y, i, j etc.

    lRows or lngRow is so much easier to read and debug.

    I guess that's a matter of taste. I generally write pretty strict Systems Hungarian in VB and Hungarian/RVBA notation in VBA, but my indexes for walking arrays are in most cases i, j and k, and on the rare occasion I need to nest another loop, m, n and p.

    Once get used to i being the first dimension, j the second and k the third, it is in my opinion much easier to read than lngRow, lngCol and lngDepth.

    I do agree that in VB where the syntax consists of semantic proper case/camel case words, using a single character variable looks a little out of place, but coming from other languages where the syntax is much less verbose, it feels much more natural to iterate with "i" than for instance with "loop_index".

    Re: Save Workbook To Multiple Paths


    There are two things wrong here.

    If Franklin is supposed to be the filename, and not a variable, then you need to enclose it in quotes: "Franklin". Secondly, you should specify the file extension, for instance "Franklin.xls"

    Re: Autofilter Code Failing On Shared Workbook & Protected Sheet

    Dave, quoting my own post:


    - When I protect the sheet, I do allow the user to use autofilter. Consequently, Sheet.Protection.AllowFiltering and Sheet.EnableAutofilter both are on. Still, I keep getting the error.

    Thanks, but the solution isn't quite that simple.

    Hi all,

    I have a problem I can't figure out. I have a protected worksheet with some macros running on it, and I have been requested to make the workbook shared. Trying to implement this, I keep getting the standard "1004 You cannot use this command on a protected sheet" error when applying an autofilter. Here is what is making this problem a little tricky:

    - As the workbook is shared, I cannot do Protect UserInterfaceOnly because you can't change the protection settings on a shared workbook without unsharing it. Needless to say, I cannot unshare the workbook.

    - When I protect the sheet, I do allow the user to use autofilter. Consequently, Sheet.Protection.AllowFiltering and Sheet.EnableAutofilter both are on. Still, I keep getting the error.

    The failing line of code is:

    shData.Range("_filterDataBase").AutoFilter lngField, strArg

    where shData is the codename of the sheet, lngField is the number of the field and strArg is the filtering criteria.

    My guess is, that the error comes from the fact that (for some silly reason) in VBA the .AutoFilter method is used to create a new filter, not only to use an existing one. The members of Sheet.AutoFilter.Filters collection are read-only, and cannot be used to modify the existing filter.

    I would be very grateful if somebody could either clarify that using .AutoFilter on a protected worksheet is something that simply cannot be done, or point out what I am doing wrong. Also, if I am wrong in any of my above assumptions of "can't do" nature, please correct me.

    Re: Extract Date From File Name & Format

    Thanks for assisting, Dave. Here is a version of the function that will work with any standard extension (1 to 4 characters). walugi, read the verbose comments carefully, and I'm sure you'll get how it works.

    Re: Extracting Then Formatting A Date From A Filename

    A real simple solution:

    As you can see from the comments in the code, this will only work with filenames that have a 3-letter extension. Nevertheless, if you study how it works, it should get you started.

    Re: Not a Valid Add-in Message Loading Add-in

    The error you are getting is because the IsAddin property of ThisWorkbook object is set to False. This property has the side effect of making the workbook hidden (not using the Visible property of the Window object, so there is no way you can manipulate that aspect of the property).

    When you save a workbook as .xla in the "regular way", Excel saves a copy of the active workbook as xla, and keeps the regular .xls (or unsaved) workbook open for editing. Now, I don't know if there is a native way to do this through the FileDialog, and if there is, that method would of course be the best option.

    If there isn't, as a workaround you check if the user is trying to save an addin by accessing the FilterIndex property of the FileDialog object (the FilterIndex for an .xla is 34). If not, then proceed with Execute.

    If she is trying to save an addin, you can get the intended filename and path from the FileDialog's SelectedItems property and instead of Execute, do this:

    1. Set the workbook's IsAddin property to True
    2. Use Workbook.SaveCopyAs with the file name you retrieved from SelectedItems
    3 Set the workbook's IsAddin property to True

    Something like this:

    I know it's a bit of a workaround, but at least it works.

    Re: Maximum Length For A Macro

    Whoa, a 2000 line procedure! That has to be a pain to maintain.

    My biggest VBA program is about 2500 lines, not including comments, but that is split to 62 different procedures in standard and class modules, averaging to about 40 lines of code per procedure. The average lenght of the procedures is dragged down by some basic string, date and file handling functions and let-get-set statements, but in general, no procedure exceeds the 100 line limit.

    Here's a few hints you might consider incorporating in your coding:

    Any task you need to perform more than once should be it's own function. Sometimes when you only need to something once, writing and calling a function still pays off, because you can declare all the necessary variables to perform the tasks within the function, and you can have them destroyed immediately, which will save your memory resources.

    Write as general functions as possible, so they can be reused. If you need to for instance extract a pattern from a string using regular expressions, don't hardcode the pattern into the function, but pass it as an argument. That way you have a wrapper function that you can call with a single line of code from any procedure you ever write. This also achieves the fact that as you use a procedure, you start coming up with new ideas on how to do it better, discover bugs or errors, that you can then fix, and the overall quality of your code will improve over time. Look into MZ-Tools to start your own code bank.

    As a rule of thumb, each function should do exactly one thing. This will increase the reusability of your code. If you ever find yourself returning multiple results ByRef, you probably should do it with separate functions anyway.

    Similarly, if you need to do formatting for a data table, write a separate sub to do it. After that, when you need to get data from a database to the sheet, have a separate function to create the database connection, etc. In general the main procedure you or your users call should be a set or directions, and the actual low-level handling should be for the most part done by functions and subprocedures.

    Just my two cents.

    Re: Transfer Cells Values To Cell Comments

    Quote from error#9

    Fencliff and JIUK, Thank you both so much!
    Fencliff / or anyone in RE to the .xla version, how do i get it NOT to put a comment if there is nothing in the cell, at the moment i need to copy large ranges but there is some blanks, and its creating a comment for a blank!

    any ideas?

    I updated the code in my previous post, and also updated the attachment. Now it does not create empty comments and if cell already has a comment, it replaces it.

    You can either uninstall the addin, load the attachment and reinstall it, or easier, replace all the code in the module modPasteAsComment with the updated code above.

    Re: Autofilter, Vba

    Instead of looping through 58 autofilters, it might be more economical to first reset all filters, and then set the fields 2 and 3 again:

    With ActiveSheet
            If .FilterMode Then
            End If
            With .AutoFilter.Range
                .AutoFilter Field:=1, Criteria1:=State
                .AutoFilter Field:=2, Criteria1:=InType
            End With
        End With

    Re: Paste Cells As Comments

    I figured that sooner or later I'll have to convert a number of cell values into comments as well, so I went ahead and added it to my personal addin. I also made a separate addin to easily create and destroy the context menus. Just download the attachment to your computer and use Excel's Tools > Add-ins menu to install it.

    Here is the source code, the first bit is the event code that creates and deletes the right-click context menu item when you install or uninstall it:

    And this bit is the function that takes the cell values and "pastes" them as comments:
    *Updated on 24-08-2007*

    I tried to make it behave like an excel paste. If you copy an area of let's say 3x4 cells and select an area of 2x1 and paste, it will still fill the 3x4 starting from the top left corner of the selected area. What you cannot do is to copy a single cell, and then paste the same comment into multiple cells.

    There isn't much error handling, apart from checking whether the clipboard is empty and that the data is suitable for using as a comment, so I cannot guarantee that there will be no errors.

    Hope this helps,

    Re: Add New Worksheet With Event Code

    If you take Dave's advice, and use a worksheet template, I would also suggest that you raise your events to application level. Proliferation of same code to multiple worksheets will create more work for you, when you need to maintain the code. Build a custom class to handle your worksheet events, and in your template simply set reference to the class.

    While we are on the topic of Chip Pearson, he has written a short article on the above, if you're not accustomed to working with class events. See:

    Re: Change Text To Proper Case But Keep 2&3 Letter Words As Upper

    Hi trebormint,

    Maybe something like this:

    The function fProper converts any string passed to it into Proper Case, except for 2 and 3 letter words. You can either call it from your macro or use it as a worksheet function.

    The sub loopProper is just an example how you might use this in your own macros to loop through a number of cells and convert them all.

    Re: Write Data To Word

    Have you set reference to the Word library?

    If you haven't, in VBE go to Tools > References and tick the Microsoft Word 11.0 Object Library reference.

    Re: OnTime Method Opening Workbook


    Maybe it is because I've spent all morning going through somebody else's illegible legacy code, but the names Limpa, Salva and Tempo are really brushing me the wrong way. I don't want to nitpick, but a good idea for the future would be to give your procedures proper, descriptive names in plain English, so if anybody ever needs to read your code (like we do know), they would have some idea what's going on.

    Re: Count By Criteria But Only Non-Contiguous Occurences

    Here is something I've used to do similar things in the past

    It loops through every cell in the range and evaluates each cell and the cell below it, so it's not optimal for really long lists. You can call it in VBA to return the count as Long:

    fCountOccurrences(NumberOrTextYouAreCounting, RangeToCountIn)

    Or put it in your worksheet module and call it from the sheet, i.e.

    =fCountOccurrences("S"; A1:A100)

    Re: Check If Workbook Open If Not Open It

    I use:

    And Then

    If Not fBookIsOpen Then Workbooks.Open ...

    Re: VB GrabData Equivalent in VBA

    I would be interested in knowing more as well. Truth to be told, I was always a hack of a VB programmer, but regardless I've never used the GrabData method, and searches in MSDN Library didn't give me any clarifications.