Posts by Rich_z

    Re: Allow multiple users simultaneously

    Hi T.

    You're essentially trying to make a single user object do something it's not designed to do.

    To make a multi user scheme really work, you would have to update each change made by the user as it is made, and not when the spreadsheet is closed.

    You would also have to implement some form of locking scheme which locks the sheet at a row or column (or even cell) level which could be done using a shared random access file but would slow things down and you would still have the same problem as outlined above.

    All in all, you need an application that supports multi-users and record locking at a native level.

    As the other posters have said use a proper database (and that doesn't include access if you've more than 10).



    Re: Controls for Attendance sheet

    That would be quite simple using a macro in the selection change event.

    Create a new form with a password input field, and then in the selection change event check the locked property. If this is true then display the form and check the text field on it's return. If it's a valid password then unlock the cell.

    In any case, if the cell has a value when the selection changes, set the locked property.

    The problem with the approach above though is telling if the user is just skipping over the cell, or actually stopping there to make an entry, but it's a start. You could also possibly use the application.Ontime event to see if the cursor has stopped in a cell for a second or so and then display the password form.

    I doubt if you'll find a control to do what you're asking though, and it seems impossible to do with just formula's, but I'll be pleasantly surprised if some one comes up with something.



    Re: Shortening VBA code.

    Hi Simon,

    Another thing you can do to shorten this code is to use a procedure.

    It seems to me that there is a pattern running through your code, so what I would do would be something like this:

    Call Format_Ad_Text("Business Centres", "centres", "Text box string")
    Call Format_Ad_Text("Commercial", "offices", "Text box string")
    Call Format_Ad_Text("Corporate Offices", "Corporate Offices", "Text Box String")

    And then create a procedure:

    This has just come off the top of my head but it seems to me that you could reduce the size of your code by at least a factor of five.



    Re: Leading Zeros in a Cell

    Hi Slick,

    This looks suspiciously like an SAP customer or vendor number..... :)

    This is a common problem when downloading or uploading data from systems such as the above where numbers like this are normalised to a standard length by padding them out with zeroes.

    What I do in such cases (if this is indeed the case), is prepend the string with an apostrophe (')

    I make use of the VAL command, the STR and the RIGHT string processing commands to then format the string.

    So, to get the numeric value you would use something like:


    To format it you would use:

    Range("A1").value = "'" & Right("0000000000", & Str(my_number),10)



    Re: Open two workbooks at the same time

    Hi Denise,

    In your install program, you could ask the user where they wish to install the files. You can then store their answer possibly in the registry, or in some known, common place on their hard disc in a file.

    In your initialisation code you then read the registry with the registry keys that you have used, or open the file and read the entry from there and then concatenate that value onto your file name, providing you with the full path and file name for the work book that you then need to open.

    Re: HashTable in VBA

    It would be better to write the code in a compiled language rather than an interpreted language, but that's assuming that the OP knows C or VB.

    It might be an idea to search the web for a dll that does the job. This is a common 'indexing' technique so there's bound to be ready written stuff out there.



    Re: HashTable in VBA

    AFAIK, there is no provision for hash tables in VBA, so you'll have to do it yourself.

    Hashing takes the key values of a record and passes them through an algorithm which then generates a unique number. This number is then used as an index into a table or an array.

    Although the language used is not VBA, there is an article on hash tables in the Abappers Knowledge Corner at (although you would have to register to view the content).

    The steps you need to consider are:

    Calculating the hashing value.
    Handling collisions.

    When you calculate the hashing value you need to use an algorithm that generates unique numbers with little chances of duplicate numbers being generated for different keys (collisions).

    In handling collisions, you can either convert your search to a table or array scan using the hash value as a starting point, or you can recalculate the hash value using a different algorithm.

    Re: Error Handling

    Also, when you're using error handlers, don't forget to turn them off otherwise if you get an error elsewhere in the program you could end up on a long bug hunt.

    Also, provide for the case where you get an error that you are not anticipating. In this case the error handling is turned off, and the erroneous statement is retried so that standard VB error messages are displayed ö handy when you're writing the code.

    My error handlers look something like this:

    You'll notice that I use 'On Local Error', it's a hang over (and I suppose a bad habit) from when VB had two types of error handler, On Error provided a global error handling and On Local Error provided handling that was local to the subroutine or function where it was declared. With on local error, if the error handler didn't handle the error, it was passed up the chain of declared Local handlers until it found an error handler that did.

    It's obviously still legal syntax, but I wonder what happened to the functionality ?




    This is where On Local Error went to. I suppose I'd better start using the right version!! :)

    In reply to Dave's sticky above, surely this shows the difference between a user and a programmer ??

    Surely shouldn't you be normalising your data ? (or have I misunderstood you ?)

    When you normalise your data you take out all repeated terms, assign those terms a code and then place them in a seperate table, accessing them (and thereby displaying them) by the use of that code.

    This also means that should the information change, then it only needs to be changed in one place and the changes will then be displayed where ever that code or reference is used.

    For example, customer names, rather than repeating them as they occur, create a new worksheet, assign each customer a code in that worksheet and then use that code (plus a function to retrieve the actual name) where it's required rather than the name itself ?

    This also leads onto a concept in programming that basically is the same thing and that is the use of named constants.

    If you have a value that is used all over the place in your code, don't use string constants or 'magic numbers' in the code where the value should be used.

    Place it in a constant (ie Const xxxx = "value" or Const pi = 3.14159) and then use the variable name in the code. Make the variable name mean something so that the code is readable.

    For example say you have a series of purchase order document types such as a standard purchase order, a framework order, a requisition, give these names such as c_framework_order etc and assign the values to that constant and use the constant name throughout your code, then if it needs to be changed it only has to be changed in one place. It also prevents questions such as: "I have to change this value. Is it the same as the value over there and do I need to change that too ??"

    And on we go....

    Meaningful names.

    You may have to come back to your code after many months or even years in some cases (I spent 14 years writing and then maintaining a system for a client....) Make your variables speak to you! Don't call them X1 or Z7 call them things like w_Invoice_Number or w_VAT_Percent. You'll also notice that I appear to be classifying them.... c_ for constants, w_ for working. I personally also use the following:

    c_ Constants
    w_ Working Variables
    t_ Tables
    wa_ table work areas
    pu_ Parameters using (ie parameters to a subroutine that should not be changed)
    pc_ Parameters to a subroutine that can be changed.

    These vary from language to language, and in somecases the underscores may be missed out, but the idea is the same.

    Also ALWAYS use the Option Explicit statement in your code. This makes sure that typos in variables names are always caught unless you have two variables that have a close name such as w_Invoice_No and w_Invoice_N0 which you shouldn't do anyway....

    Always Always type your parameters. If the parameter is not meant to be changed, pass it as a by value parameter rather than the standard by reference. Not only does this prevent bugs, but it also speeds up your program as the program does not need to convert data types at run time

    Oh. And Global variables. Don't use them. Never. Ever. And if you do because you can't do anything else but, wrap them up into a subroutine to set their value, and a function to get their value.

    By using these techniques you can cut down on the number of bugs that can be inherent in a program.

    For example, I saw this in a program the other day. The original programmer had spent a week trying to figure out what the problem was.

    Admittedly a different languge, but what would happen if this code was used ?

    PERFORM prepare_indentation USING number_of_ulines
                                           CHANGING my_ulines.

    Good programming practice leads to good programs. Which leads to more efficient and lower maintenance cost. (And fewer headaches).....

    To end, going back to my initial statement, Excel is meant for end users to get what they want done quickly without the use of a programmer, BUT if you want to do something more than using the in built functionality of Excel in terms of a spreadsheet (rather than a macro) then if it's a reasonable sized application, get a good programmer who knows his trade to do the job for you.


    Cheap programmers = False Economy.

    Oh - and PS this isn't a dig a Dave, this started out as a text about normalisation and then because I've just spent a frustrating few weeks working on a system rampant with Cr'p code turned into a dig at the bean counters that employ cheap contract programmers and then find they have to employ people like myself to sort out the fall out that's left behind after the programmer has dissappeared leaving a mess in his wake.... :thanx:

    Re: Increasing the font in input and message boxes


    Whilst not an answer to this in Excel, Windows in itself does provide various settings that enable the partially sighted to use it.

    One thing to look at is right click on the desk top, select properties->Appearance, and then experiment with the "High contrast" options in the colour scheme drop down.

    Another property to play with is the Font size property in the same window. This will enlarge all of the fonts, including those in message boxes etc.

    These settings will affect all of the applications that are on the old ladies system so they mey help in other ways as well.

    Other things to think about are sticky key settings so they she does not have to hold down the shift key for example. These can be found under accessability options in the control panel.

    Re: VBA if then for odd-even

    Hi Ross,

    You can avoid this error by using the statement:

    Option Explicit

    In the top of your VBA modules.

    This forces you to at least declare your variables and then by declaring them you should remember to initialise them as well.

    Re: Classes and Inheritance[Resolved]

    Hi Dennis, Andy.

    Thanks for investing your time in this. So, some results for you.

    I copied the properties etc that I was using to the clsGutter class module and modified the code.

    The result was that the copied sheet could not see the various properties that I had defined.

    But then I had a :idea: moment.....

    Before I was Selecting and copying the worksheet by selecting the cells and then copying...... (can you see where this is going....)

    I replaced everything as it was, put Andy's copy code in place of mine and everything works!

    Thanks for all your help in this!

    Re: Classes and Inheritance

    Hi Dennis,

    I'm away on holiday tomorrow for the next two weeks. I'll print this out and let you know what happens when I get back.

    Having said that, will this cause any messages concerning macros or viruses to appear ??



    Re: Classes and Inheritance

    Hi Dennis!

    Thanks for the welcome!

    My client creates roofing for huge warehouses (a bit bigger than your average garden shed...)

    The spreadsheet is a front end for simplifying data entry into an ERP system called SAP for which it is up and running.

    Each roof must have gutters to carry away any rain water and these gutters have different profiles or shapes, but basically have the same properties.

    For each warehouse the user can add the various profiles used in the gutters to a work book by clicking on an image of the relevant gutter profile.

    This then adds a new worksheet to the workbook by copying a base template and then pasting it as a new sheet, changing the title at the top.

    Once the user has completed all the various gutters they click a button and the code goes round each worksheet, compiles a production order, posts it to SAP and bob's your uncle.

    The base worksheet originally contained a series of macros that performed various actions - updating texts in text boxes, calculating and validating various bits and bobs, contacting SAP for the options available in drop down lists that are dependant on the material used etc.

    However, the copy and paste created the worksheet but didn't carry over the macros. In order to allow the cloned spreadsheets to use the same code I was advised to create a class with it's own events, setting the current worksheet to point to the class like so:

    (Somewhere in here I must point out that there are dozens of gutter profiles and the client doesn't want a work book that contains all of the profiles as empty sheets - the workbook should only contain the gutters that are actually required.)

        Call Trap_Worksheet_Events(Worksheets(Sheet_Name))
        On Local Error GoTo 0
        Exit Sub

    The Trap_W... sub is:

    Sub Trap_Worksheet_Events(sh As Object)
        Set Gutter_Sheet.shGutter = sh
    End Sub

    And Gutter_Sheet is defined as:

    Public Gutter_Sheet As New clsGutter

    So, I can now do things like run the same code without duplicating it in every spreadsheet which also means that I don't have to have a copy of every profile in the workbook to start off with, and that means easier coding when it comes to creating the production order.

    However.... :yikes: All the other Worksheet objects have dissappeared!!!




    I am trying to create a new class which inherits the methods and properties of the Worksheet object and then extends them in various ways. The reason being is that I need to clone a spreadsheet along with the macros that run that sheet. Using straight cut and paste doesn't take the macros with it.

    I was under the impression that coding the line:

    Public WithEvents clsGutter as Worksheet

    Would provide me with a new class that would contain all the properties and methods of the worksheet object, plus all the various properties etc that i have added to the class.

    However, when I SET the class equal to the relevant spreadsheet all the events etc are captured but all the worksheet properties dissappear.

    How do I code the declaration so that it inherits the worksheet class ?