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:

---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: Game Codes

Quote from arjun000

Can anybody please help me create a simple memory game (4 by 4). It is due in two days and I dont know anything about excel/vba. The details for creation are given in this website under MP4: http://www.cs.uiuc.edu/class/fa06/cs105/mps.htm

Skip a few classes did we ??

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:

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: Unprotected Cells On A Protected Worksheet

Quote from Dave Hawley

Use this Custom Function within Conditional Formatting

Code

Function IsLocked(rCell As Range) As Boolean
    IsLocked = rCell.Locked
End Function

That's what I use already, but I thought Georgs' idea was better! No wonder I couldn't make it work!

Re: Unprotected Cells On A Protected Worksheet

Hi Georgs

Which version of Excel does this apply to ? I've tried it with 2000 and I get the message "Object does not support this property or method"

Regards

Rich

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:

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: Adding The Digits In A Long Number

Have a look at this: http://support.microsoft.com/kb/214053

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):

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:

Sub My_Sub(Arg1, Arg2)


Function My_Func(Arg1, Arg2)


Dim My_Var

This code is good:

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