Posts by Rich_z

    Re: Looping If Login Id Is Not In Database


    Hi Cattytse.


    I'm assuming that you are using a form for this. (If you're not, a form is the best way to go)


    When you have validated the user name and password, if it is valid then execute a the statement:


    Code
    ---Pass a flag back saying credentials ok---
    Me.Hide


    If the credentials are invalid, do not execute the unload statement.


    Forms in Excel are shown modally within Excel for the worksheet they are on (I'm not sure if it's system wide), ie no other process can continue while the form is displayed so unless it's unloaded the code following it (ie the code to show your other worksheet) will not run.


    The down side to this is stopping the break key from working.....


    Regards


    Rich

    Re: Game Codes


    Quote from Will Riley

    Hey, when I saw a reply I thought someone had undercut my price!! :)


    $500 ? Us or Australian.


    And what's that in Euro's.


    Is that for the complete job or a daily rate ?


    And I wanna be team lead......

    Re: Reject User If File Is Being Opened By Other


    Write a VB wrapper that checks a semaphore file on the disc. If that file is absent, create the file, run excel with your work book, when excel exits delete the file.


    If the file exists then tell the user they can't access the file.


    Regards


    Rich

    Re: Reject User If File Is Being Opened By Other


    Well, this will not prevent them opening the file (even as read only), but it will not allow them to get any further:


    Code
    Option Explicit
    
    
    Private Sub Workbook_Open()
            If ActiveWorkbook.ReadOnly = True Then
               MsgBox "This work book is in use", vbExclamation, "Error"
               ActiveWorkbook.Close
            End If
    End Sub


    Regards


    Rich

    Re: Unprotected Cells On A Protected Worksheet


    Quote from Dave Hawley

    It was a post to pog_giles. Not sure why you are telling me you use it already??


    I found that method a while ago, but I thought that Excel would be intelligent enough to provide a range of editable cells in one go. I tried to make Georg's idea work, but couldn't, and hence the question about Excel versions. My comment was about Georgs code and why I couldn't get it to work.

    Re: Count Number Of Characters in Cell


    It's possible in VBA because you get an event firing each time there is a Key press or Key up/down but in VBA this event is missing so I'll go with the other guys and say it can't be done.

    Re: Meaning of Option Explicit


    Quote from Dave Hawley

    It doesn't force the use of correct type, only that you declare any variables. Passing the wrong type to a variable will result in an error with/without Option Explicit


    Not if what you pass is a valid variant. It'll only error when you try and use it in a situation where something requires a type:


    Code
    Sub Test()
        A = 1.2
        Debug.Print Sin(A)
        A = "34.35"
        Debug.Print Sin(A)
        A = "String"
        Debug.Print Sin(A)
    End Sub


    Will only error on the third debug.print. However, declare A as double and it falls over when you try and assign "String" to A. Not much difference in the small example above, but in a real world example assigning a string to a variable that is supposed to be numeric might cause an error that is many lines away from where the actual erro (ie the assignment) is.


    Regards


    Rich

    Re: Combining User-defined Functions


    ??? is the variable type of your variables. Change it to integer, long, single or double as required.


    Secondly, you cannot give a value to subroutine. Use the function statement instead. Remember A function has a value. A subroutine does something.


    Lastly, you have defined the functions first, second and third haven't you ?


    Rich

    Re: Run-time Error Every Other Time The Macro Runs


    Quote from Dave Hawley

    Agree it's good pratice, just not that it's a "requirement". It it were, VBA code would not run without it.


    <grins>

    Re: Combining User-defined Functions


    Quote from qqq516

    Say I have written three user defined functions: first(a,b,c); second(a,b,c);third (a,b,c), which all give value for a,b,c inputs
    How do I write a VBA program that uses the functions above?
    Say the code is called Combine(), after the user have typed in the value of a,b,c, the function Combine=first+second+third.
    Thanks


    Write a fourth function, which looks like (and I'm assuming addition):


    Code
    Function Combine(a as ???, b as ??? c as ???) as ???
             Combine = First(a,b,c) + Second(a,b,c) + Third(a,b,c)
    End Function

    Re: Formating Number: From Russian To American


    Quote from shg

    What's the source of the data? If it's already in Excel, changing Windows regional settings would automatically change its appearance. If it's arriving as a string, you could replace commas with periods.

    [edit] and then use the appropriate type-conversion function (e.g., Cdbl). You also need to process the thousands separator. Do you need further help?


    There has been a big discussion on another website about exactly this problem. Whilst the language used is not VBA (It's something called ABAP), perhaps a read through of the post might give the OP some further ideas.


    Regards


    Rich

    Re: Run-time Error Every Other Time The Macro Runs


    Quote from Dave Hawley

    Rich, I agree with most of what you say. But Option Explicit is not a "requirement".



    I agree with you in that it is an optional statement in VBA and programs will work without it.

    However, it's a starting point to good programming practices in that it makes you declare your variables to start off with. Alright, you can be lazy and just declare them as a default variant, but you still have to declare them.

    So, for me, anything that promotes good programming practice is a "requirement".

    Quote from Dave

    That isn't the use of Option Explicit, simply the fact you have already typed the variable name with Dim.



    Yes, but without Option Explicit, that code would not be noticed and you would be assigning the value of My_Var_zero to My_Var_Oscar which is not what you mean to do. Option Explicit would cause an undefined variable at My_Var_Oh (assuming that you've declared the _zero version). So yes, the example is valid for Option Explicit.

    Regards

    Rich

    Re: Run-time Error Every Other Time The Macro Runs


    Quote from gmccreedy

    It a nut shell...it forces you to declare all of your variables. Its not necessary, but I reccomend it. It helps if you make a simple error...will trap it for you.


    I'll disagree happily with GMc! It is a requirement. It is a real good development aid.


    To expand on what he said,


    Options Explicit requires that you declare using the Dim statement any variables that you use.


    Going one step further, when you declare your variables you should also state their type. This includes function modules and subroutine parameters. So this code is bad:


    Code
    Sub My_Sub(Arg1, Arg2)
    
    
    Function My_Func(Arg1, Arg2)
    
    
    Dim My_Var


    This code is good:


    Code
    Sub My_Sub(Arg1 as String, Arg2 as Long)
    
    
    Function My_Func(Arg1 as Integer, Arg2 as User_Type)
    
    
    Dim My_Var as Long


    The reasons for this:


    Option Explicit


    [INDENT]Option explicit makes you declare all the variables that you use. This prevents bugs from entering your code caused by typo's in variable names. For example accidentally typing My_Var0 rather than My_VarO would be caught by this[/INDENT]


    Typing your variables


    [INDENT]Typing your variables has a few effects. From the development side it prevents errors because the arguments you call a subroutine with must exactly match the formal parameters.


    It increases the speed at which your macros run. When VBA calls a subroutine, if the variables are untyped, it must determine the type of variables the subroutine requires and then coerce the actual arguments to those types.


    Similarly when you use intrinsic functions, if you use a variable that is not typed or not typed correctly, the system has to again coerce the parameter to the formal parameters type.


    If you do type all your variables and parameters, then these two steps do not occur, resulting in less time being spent doing this chore by the macro. This does not have much of an effect in straight code, but imagine a 10,000 iteration loop having to do these conversions all the time.
    [/INDENT]


    Regards


    Rich

    Re: Protecting Shared Book


    Unprotect the form when you update the cells in the code and then protect it again after you have finished the update.


    Use the macro recorder to get the macros for protecting and unprotecting the sheet.


    Regards


    Rich

    Re: Bad File Mode: Writing To Locked Binary .txt File


    Hi Andy,


    Yep, but this is also related to the thread about file locking.


    As your code stands at the moment there is no file locking happening so you could get a double read.


    The reason I suggested Random files rather than plain text files is that I'm not particularly sure what happens with the output clause - does it delete the file or truncate it ? And what happens if a read and a write are coincidental ?


    With Random access files, I know that the open will always either create the file or open it.


    Regards


    Rich

    Re: Excel XP Puts Quotation Marks Around Data In CSV File


    Unfortunately the way Excel handles CSV files does not exactly adhere to the spec. Quotes should only be placed around a string if that string actually contains a comma.


    However, it also appears that SAS doesn't understand CSV files properly either!


    I have had to write loads of data extraction and upload programs over the years and have come to a few conclusions which might help you in the future.


    • Use Tab Delimited files instead of CSV Files.


    [INDENT]Why ? Because a TAB character rarely appears in text it's ideal to use for data uploads.[/INDENT]

    • Write a few good and trusted routines and keep using them.


    [INDENT]Why ? Because you'll keep finding that you write the same thing over and again if you don't. Ones I have include parsers that split a data string into seperate fields, a 'correct' CSV, a correct TAB output (Does things like sorts out the comma problem, changes dates to YYYYMMDD etc [/INDENT]

    • If you're not responsible for both parts of the transfer (ie upload and download), agree with the other programmer on what conventions you will use first. Don't rely on the documents the end users give you....


    [INDENT]Why ?. Because generally they will not contain enough details for you to make a decent start.[/INDENT]


    Regards


    Rich