Posts by Kyle123

    Re: Foolproof Protection of VBA codes

    I've seen Roy's approach before - or at least I think I have, it uses the in built mechanisms of Excel with regards to viewing projects in a shared workbook. Excel thinks the project is shared - even though the workbook isn't so won't show you the code. It's pretty ingenious, but imo even easier to view than the usual password protection. I haven't tried googling it, but I think it's a little less well known than the password cracking/hex approaches so users are less likely to find how to view the code (although in fact the method is easier than usual password protection).

    Ultimately there is no sure-fire way to protect your code, Excel is inherently insecure. I believe you'd get security by writing the code in .Net, compiling it and using it as an add-in. It is however much more work, so it's always a balance of how usable the code is or easy to write vs how bothered you are about people viewing your code. IME, for a commercial package, users are far more bothered about having support than they are about having a few free copies so are happy to pay for things.

    If you're really bothered, the package that seems most secure to me (though I haven't tried it) is LockXLS since it wraps the workbook in an application. Though to be honest, I'd just stick a password on it/use Roy's method and be happy that >95% of people will use it properly.

    Re: complete web form with excel vba

    Mohan, you can send multiple container numbers at the same time if you want to do a batch check, consider:

    That way, you only need to call the website once :)

    You might also want to remove the second instancing of ie, it slows your code down

    Re: complete web form with excel vba

    As a UDF:

    Called with =ValidContainer(A1) Dragged Down

    Re: Calling a UDF in VBA, how to pass array from one UDF to another?

    locations doesn't have a second dimension, I think you're getting a bit mixed up between multi-dimensional arrays and jagged arrays.

    A multi-dimensional array is a single array, a jagged array is an array of multiple arrays. Their syntax differs:

    2d array:


    Jagged array


    The easiest thing to do may be something like this:

    If VarType(locations(LBound(locations))) = 8200 Then
        newarray = locations(LBound(locations)) 'Assign the array at postion 0 of the locations array to the new array
        newarray = locations
    End If

    Then just use newArray, it will always be a single array

    Re: Calling a UDF in VBA, how to pass array from one UDF to another?

    No, when you pass something via split to param array, you are creating an array within the locations array, so at locations(0) you don't have a string, you have an array containing the split data.

    If you pass values manually, location(0) will be a string so will return 8, it will not be an array.

    Re: Calling a UDF in VBA, how to pass array from one UDF to another?

    Yes, you can test if the param array value has been passed by a split function:

    If VarType(locations(LBound(locations))) = 8200 Then 'Passed by split

    This works as the first element in the locations array will be a string array, any other type will not have a vartype of 8200

    The paramarray will be an array of arrays, so will take the form of locations(0) (0),locations(0) (1),locations(0) (2) etc

    Re: Check hyperlinks

    If they're unc shares, you should be able to use:

    Function FileExists(fPath as string) as Boolean
        If len(Dir(fPath))<>0 then FileExists = True
    End Function

    This is a modified version of code I have used elsewhere, it returns drive time in seconds, and distance in meters between 2 given postcodes. I've only tested this in the UK, but I don't see why it wouldn't work elsewhere.

    It uses the same services that the on-line TomTom route planner uses. I've read through the T&Cs of the service and can't find anything that using the service in this way would breach, but let me know if you think there are issues and I'll remove the code. (I used to use google APIs for doing this, but it breaches their T&Cs so this is an alternative - there is also a Bing Maps method that I've used, but find this more reliable).

    The only required parameters are the A-End and B-End Postcodes, without setting the other parameters, the function will assume the following:

    • It should not route to avoid traffic
    • It should not include traffic delays in drive time duration
    • Day of travel is Today
    • Time of travel is Now

    A sample usage:

    Re: CreateObject(&quot;System.Collections.ArrayList&quot;)

    Did you ever manage to get an array in without looping?

    I think I've made my mind up that you can't since the array list is just a collection, though I'd be very happy for you to show me the error of my ways ;)

    Re: last stock price from yahoo finance

    FWIW I use

    I then use this as a worksheet function:


    Re: last stock price from yahoo finance

    hmmm, I think you've got a bit of code missing. Where's the code that actually downloads the stock data? your workbook just seems to pull data from a spreadsheet called Quotes

    Scratch the above, just re-read the code

    Re: Work with 3 users on 3 different PC's in the same excelfile, see changes real tim

    I think you're barking up the wrong tree entirely with Excel, it is fundamentally not designed to do this.

    I'd look for other solutions, have a look at access, again you won't get data pushes but it will allow multi-user access. I'd also look at what google offers, I've a suspicion that google docs may offer something similar to this, but I've never used them