Posts by Fencliff

    Re: Find 11 Not 1

    If you look for value 11, the value 1 should not be found. I'm guessing you meant it the other way around?

    To force an exact match, use the argument LookAt:=xlWhole :

    Set Found = ActiveSheet.Columns("A:B").Find(what:=wht, lookat:=xlWhole)

    Edit: Whoopsie, beaten like a dog.

    Re: Hiding A Selection Of Cells

    Well, by conditional formatting you can only make the cell values appear hidden with setting the font color to the cell background color, not actually hide the cells.

    However to simplify the conditional formula, you can set up a formula using OR() in one cell that returns True if any of your five cells' value is "other". Then in the conditional formatting you can just refer to that cell.

    Re: Activate Webpage Button Where 2 Have Same Name

    I mean that you should check the contents of the doc.body.innerhtml property to find out what exactly is loaded into the HTMLDocument object at the time of the error, and you should "view source" of the web page in a browser, and compare these two.

    The easiest way to inspect the .innerHTML property is to go to debug mode after the error throws, and in the immediate window type


    Re: Activate Webpage Button Where 2 Have Same Name

    What throws the error this time?

    Well, can you check the state of the Doc object upon the time of crash (take doc.body.innerHTML and compare it to the source of the web page - are these identical?)

    Other than that, I'm afraid I can't really help without being able to access the website.

    Re: Udf Name Vba Caller

    Unfortunately VBA doesn't have much capacity for reflection or metaprogramming in general.

    I think the best way to do it would be to declare module-level constant to hold the sheet name, and a local constant to hold the name of each procedure, and then pass these as strings to your class instance.

    There is also the VBA extensibility library that lets you access the VBA project structure. I don't have much experience there, and I don't believe it will be of any help, but this would be my best guess.

    Re: Activate Webpage Button Where 2 Have Same Name

    Hmm, I uploaded the bit of HTML you posted onto my server and tested it, getElementById finds the correct control.

    Here's how to do what you originally requested, but this is still just a workaround to the real problem. However that problem is really hard to troubleshoot without access to the webpage itself.

    Re: Automatically Collate Data From Forms Onto A Table


    You can insert data into a table-formed workbook with ADO. Here's a crude example (suggest you add error handling, at least):

    You will obviously need to change the reference to the central file on the top of the procedure, as well as the correct field names in the SQL string and references to the fields in your userform.

    The first row of the target sheet needs to contain headers, and the data must contain no entirely blank rows.

    Re: Reading Spinner Properties

    Here's a fixed version (min and max are properties of the underlying control, not the shape). You also need to verify that the control is a of FormControlType 7 (xlSpinner). Also, you don't need to activate a worksheet to work on it.

    Re: Activate Webpage Button Where 2 Have Same Name

    It's hard to debug this when we don't have access to the specific website, but it might be that you get the error due to the fact that the document is not fully initialized. If memory serves me right, the .ReadyState flag is not 100% reliable. If you put a Stop statement before assigning value to the Doc variable, wait for a good while for the page to load, and then continue the execution, does it work?

    Re: Environ Command In Vba To Get Logged Windows User Name

    Simple, yes. But beware when using the Environ function in general: the environment variables may not be present in all systems, and some are editable by users, so check your output and have a API fallback.

    Using Environ("USERNAME") should be safe, though - although it may return "Administrator" on some Windows Server configurations, even though the user logs on with a different name.

    Hi All,

    I have returned from vacation, and it's time to get back to business: dicking around in Excel VBA while pretending to be working :) Today I thought: wouldn't it be nice, if in Excel strings were true objects with methods. Well, why couldn't they?

    If in VBA strings were objects, you would of course instantiate them like any other object:

    Dim myString As New CString
    myString = "Hello World!!!$$$????"

    Now, these objects would of course have methods. And maybe the the methods would return a copy of the string object, you could link these methods together. Maybe we would want to remove all non-alphabet characters (I don't know why they're there in the first place...), reverse it, turn it to upper case and print it to the immediate window:


    I guess that's ok, but wouldn't it be more elegant to remove the non-alphabets with regular expressions?

    myString.Remove("[^a-z A-Z]", RegularExpression).Reverse.UpCase.Inspect

    Yeah, that's better. And there is no reason why these methods couldn't extend the functionality above "normal" string manipulation, too. Let's assume for a while that you have a text file, where you want to programmatically replace all occurrences of "{date}" with the current date and write the new contents back into a file.

    myString.ReadFromFile("C:\template.txt").Replace("{date}", Format(Date, "dd-mm-yyyy")).WriteToFile "C:\today.txt"

    I think I'd like that.

    If you take a look at the code, you notice that there are some useful methods, and some less useful, just simple wrappers for existing functionality, that I might remove down the line, because they add some overhead to the memory footprint of the objects instantiated from this class. However, I think there's a lot of interesting functionality I haven't thought of. What kind of string manipulation you often do? Any cool ideas? I will add any good suggestions and post the updated code here.

    I haven't had a chance to write any documentation on this, but take a look and play around. I tried to include the code for the impatient and curious, but apparently the post was too long, and all the text got deleted. A word of warning: Do not copy paste the code into your project!. The Value property of the class is set as default to enable using 'myString = "Something"' instead of 'myString.Value = "Something"'. The default property also enable you to pass a CString object into anywhere that expects a string - VBA knows to pass the value instead of the object automatically. This attribute does not get carried over if you copy the code. You can either download the workbook and drag the class to your own project, or you can download the text file and import it into your project.

    For a reference, here's a list of the methods and properties of the class. As a somewhat unorthodox design, I have assigned some functionality as property getters, when they would probably make more sense as methods. That's because apart from converters (ToDate, ToStringArray etc.) I wanted all the methods to return a linkable CString object, and the little icon on the intellisense menu gives a nice distinction. This list with descriptions and comments can be found in the attached workbook.

    Insert(sText, lPos)
    Replace(sFind, sReplament)


    Re: Fastest Lookup Method

    Only in VBA, that was sort of the point of the exercise, since I don't exactly know how OP was planning on implementing the bit twiddling for a jump table on a worksheet. Exact matches, naturally.

    Re: Fastest Lookup Method


    The table looked like this:

    K1 Value1
    K2 Value2
    K3 Value3
    K30 Value30

    I picked a short key, so the hash table wouldn't have much unfair advantage (if the differentiating character would have been, say 10th char in the string, the string comparison overhead would've probably made the lookups even slower.

    And the test was ran using WorksheetFunction.VLookup.

    Re: Global Workbook_open

    You could go to File > Properties and look through the tabs, if there is any indication of the template origin of the file (most likely in the Custom tab). If yes, you can access them by Workbook.BuiltInDocumentProperties and Workbook.CustomDocumentProperties to verify that the workbook is borne of a template.

    Re: Global Workbook_open

    Andy Pope already gave you the answer, but here's my contribution: THIS. IS. A. HORRIBLE. IDEA.

    Popping up a message box every time user opens/creates a workbook? Christ, if this happened in my company, I would probably find the culprit and adjust their outlook with a clue-by-four. Not to mention all the user macros that would break.

    Isn't there any way you can detect whether something is a xpressv1.8 template or whatever?