Posts by Bladeforger

    Re: Digital signatures: worth the money?

    Good point! I'll consider it!

    Meanwhile, what about the digital signature? I presume it gets added by me, since I've done the macros, right? (And wouldn't that be just one more claim to the code in the modules?)

    Re: Digital signatures: worth the money?

    Well, that is a good question. We don't have a written contract, he just pays money and I keep working. Eventually, I'm also supposed to get 3% of any sales, which I made small so I'd get something. I only know if he's selling it if he tells me so.

    Otherwise, all the code is password protected and only I have the password. So, who owns the code is irrelevant since I'm the only one who can access or change it... without a password cracking program. My client could care less at this point because he doesn't know VBA and doesn't want to learn. If all works well, why would he need anyone else? I suppose if I'm run over by a truck...

    Maybe I should add something in the code module that I have copyright rights to that particular code???

    Re: easy cell reference

    First you want to run a quick validation to see if Z9 is a valid number. You can MOST EASILY do this by using another cell somewhere with a formula like:




    To be really fancy, truncate it to an integer and then make sure it's a valid row... and you can check for the max row in your particular spread.

    Then if it's a valid address, assign this to a variable.

    Then use R1C1 notation and reference the variable.

    (I was assuming you had a worksheet named "521" from the formula you posted.)

    Re: working with the current range in VBA

    Three quick observations:

    1. Be very nice to Jack in the UK. He's helped me in the past, and he is a fount of information. I kinda get down on one knee as his posts scroll by. Anyway, I'd take a few minutes and get straight with him if I were you; he's a fine bloke. Look at the over 2000 posts, and you can bet it's mostly helping us--not asking questions. My two cents.

    2. Go ahead and use debug.print! I ran across that tip in Hansen's book, and it's saved me a world of grief. Going further, I don't delete the statements when it's running, I leave them in and just comment them out of the active code with a '. For those reading who haven't used it, or turn up your nose at it, try it sometime... then you'll happily add it to your bag of tricks!

    3. If you have a message box waiting for a reply (one cause) or anything else that causes VBA to be executing somewhere, then you will not be able to run your macro from inside the code itself. (Don't drink the water in Mexico. Don't ask me how I know!) Likewise, a subroutine that takes a parameter or a function that returns a value will require a little test subroutine to execute it while you're developing it. Once the tested subroutine or function is working okay, I move the testing subroutine to a different module and keep it in case I have to come back to revisit the now-tested-and-working macro. Otherwise, testing is best done via F5 or running via the menu bar. The menu bar will let you know if you need to reset or not.

    Hope any and all the above are at least somewhat useful!

    Re: logic functions...

    Just two cents for practicality: If you can do it in a worksheet, then you can hide the worksheet and refer to it via a named range. You can make a worksheet xl-very-hidden via the properties window while in the visual basic window of a workbook. Click on the worksheet, and then change it's visible property.

    OK, don't have a clue if this helped answer your question or not, but it's good info nevertheless. I've found that a very hidden worksheet has a lot of practicality...

    I've been developing a product for a client for about 6 months, off and on, and it's about ready to go beta and be used. I've read up some about digital signatures, and some of the other alternatives (Hide all worksheets and wait until macros are enabled; self-sign and have the user choose to trust me.)

    I'm still an amateur, even though I'm getting paid to do this one.

    How many of the professionals out there are using the digital signatures, like Verisign, and is it worth the cost?

    All comments are appreciated. Thanks, in advance, for replies.


    Re: Worksheet named range vs workbook named range and VBA declaration

    Hmmm, thanks, I'll experiment with that sometime. For now, unless anyone comes up with a better idea, I'll just set up a routine to delete the name and reassign it each time a worksheet is activated. That way I can have the same code and the same fields in all of the multiple worksheets. Easy enough to activate, etc. in the interests of automation. The goal is to make all the automation for one sheet and then duplicate it... because I'll have some spin buttons etc. that refer to named ranges.

    Thanks, Timbo, for the input!

    Re: Specifying a custom sort order within visual basic

    AARRRRGGGGHHH! I always hate it when that happens to me, and now I've gone and done it to you. :( My apologies! I answered a question you didn't ask and missed what your question was!

    Now, I understand which question you were asking; however, I don't know the answer.

    If you can find someone who knows how to do a custom sort MANUALLY, (that is without a macro), then you can step through it using the macro recorder and have it become automatic. I must confess that I don't know how to set up the list for a custom sort.

    *Droops head in shame and stumbles from room.*

    Re: Namingf a range on multiple worksheets

    That's certainly one solution... but I still think it's possible to have the same range names in more than one worksheet at one time.

    Anyway, if nothing else works, this is one way to do it... and one I hadn't thought of!! Thanks!

    Re: Namingf a range on multiple worksheets

    It *is* possible, see Excel hack #40 and Excel 2003 Programming with VBA by Steven M. Hansen.

    Now, I don't know how to do it right, yet, with VBA, but between the solution will be forthcoming. See my other thread on this.

    (Where there is a will, there is a way!)


    Re: Specifying a custom sort order within visual basic

    I have one that looks like this. Is that helpful?

    I just used the macro recorder and created the rascal first--then edited.
    The keys get specified individually. The other stuff once. Then the DataOptions are separate. I always go back to the top of the sorted data to avoid scrolling. Looking at it, I think the last 2 statements are redundant, but it's been working for months now.

    Hope this helps.

    My significant other just acquired a load of fonts, and she was trying to create samples of the approximately 200 fonts manually! Ick!

    I told her I thought I could automate the sampling process in Excel, and looked for some guidance on looping through the fonts as a colection using something like

    1. For each x in y 'The reference I can't get.
    2. 'Some code here to enter in cells, which I can figure out
    3. next x

    OOPS. I can't find any way to do it!

    This ought to be really easy, and as a wise old fart once told me, "If it's too hard, you're doing it wrong."

    Anyone know how to do this?


    Okay, background first:

    One workbook, multiple worksheets. I've tried Excel hack #40 with mixed results, but I want to formulize the naming anyway. I want to create ONE worksheet in Excel that names about 25 different ranges. Then, I want to copy that worksheet and name the same cells with the same range names. I want to do this about 10 times, and if I have to come back later and update the project, I want to be able to rework ONE worksheet and then cut and paste the code to the other 9 worksheets without having to think about it.

    I may need some code in the worksheet that looks like

    UNFORTUNATELY this is not working the way I'd like it to... in fact it just isn't working.

    I think the problem comes in not understanding well enough how to create and use worksheet named ranges versus workbook named ranges. Any and all help is gleefully and gladly appreciated.


    I have the following code in my application project module

    1. For i = 0 To 10 Step 1
    2. Set rEntityRanges(i) = .Range(Cells(6, (3 * i + 2)), _
    3. Cells(43, (3 * i + 4)))
    4. Next i

    What I really need, upon further testing and development, is to include

    1. (Cells(6, (3 * i + 2)), Cells(14, (3 * i + 4)))


    1. (Cells(21, (3 * i + 2)), Cells(26, (3 * i + 4)))

    in the set statement.

    My formatting doesn't work out any way I try it, and the intellisense typing help doesn't offer a suggestion. Can it be done?

    Re: Sheets "code name" and Range reference

    OK, I struggled through this and came up with the following code, which works. Obviously, I could remove the debug.print statements now. Also, I could have gotten by with one worksheet variable; however, I was working back and forth adding code and deleting code until I got the bugs ironed out. I also could have used case/select instead of 3 if statements, but they were added one at a time until it was working well.

    I would STILL like to know if there is a better way to do this and still use the CodeName references. If so, great! If not, at least I found something that works.

    Anyone who knows how to use the CodeName references without having to declare and assign string variables please share the secret with me!

    Re: Sheets "code name" and Range reference

    Quote from Jack in the UK

    Like you say best to make all valiables so stay with this format for now.


    Yeah, but this still uses the name the user sees on the worksheet tab, which is "Entity". I want to use the codename, which I changed in the properties box to "FinOverflow". The user can change the name on the tab, but she can't change the codename. The codename used to be Sheet2. It was also index 2. It's still index 2, so I could use Worksheets(2) unless it's moved around. But I want to use the codename that no user sees or touches. Do you know how to do that? I find two authors that recommend using the CodeName but I don't find the HOW part!


    Re: Sheets "code name" and Range reference

    Quote from Jack in the UK

    Hi buddy have you tried the code i provided ?? Should work ok, here is two versions of the same code i have tested on Excel 2003, hope this works ok for you.

    Jack: Thanks for your help and patience. Looks like we posted at the same time--less than a minute apart. So you probably didn't realize exactly what I wanted vis a vis using the codename versus the index (which I now have figured out is different from the codename and can change) or the name that appears on the worksheet tab.

    Can you help me reference using the code name?