Posts by Rob Xaos

    Re: Rewrite recorded Macro for Pivot Table update


    Firstly, Macro help is not an acceptable thread title at Ozgrid. Thread titles should use Search Friendly Key Words only and give a concise description of the problem. They should allow another member to find this thread when searching for a solution to a similar problem.

    Secondly, all VBA code must be wrapped with [noparse]

    Your code here

    [/noparse] tags. I have added them for you this time.

    Please take note of these comments as further infractions may affect your posting rights.

    Thank you.

    Re: AVAYA CMS Scripting through VBA


    You have posted in what is basically a 4+ year old thread, the only recent posts here should not have been made but were not caught earlier by a Moderator. Ozgrid forum rules are one question (or close follow-ups) per thread, by the original poster only.

    Please start your own thread and link to this one if it helps explain your situation.

    Thank you.

    Re: Read Excel workbook to database

    I am a little short of time at the moment so I don't have time to clean these up to be more generic, but you may find the following two bits of code useful:

    The second piece of code is simply a file browser. The first pulls a CSV file into a temporary table and then updates it onto an existing table.

    You should be able to adapt these to suit your exact situation. You may not need to create a temporary table in your case but simply update the new CSV file onto the existing table

    Re: Macro paste special - Compile Error

    Quote from Ger Plante;530089

    It seems I am invisible.

    How have you been Batman? Havent seen you around these parts in a while.


    Seems we have two Super Heros in this thread. Batman and the Invisible Man :)

    Re: Speed up slow calculation in vba

    Just to chip in here with some further ideas about optimisation.

    There are times when


    It has to run using code

    but in my experience, people often reach for VBA too soon. VBA is excellent when used correctly and does solve problems that cannot be done even by the most complex of formulas. However if used incorrectly it can bring with it huge performance issues.

    I would look again at your calculations and see if there is a way to perform some of the terms of the calculation as formulae. The less that has to be done by VBA the better. These partial terms can be hidden either in hidden columns, rows or even on a separate 'pre-calculation' sheet. Try to take an approach that minimises the amount of processing the VBA is required to do.

    A brief skim of the code to me shows no clear reason why it has to be in VBA. You perform a number of calculations which are used as parameters for the financial function MDURATION the result of which is assigned to the ActiveCell. On the surface this looks like it could all be done as a formula.

    As a side note I would avoid having your own procedure with the same name as the built-in financial formula and I would avoid having a variable within that procedure also with the same name. Although this compiles and runs it makes it difficult to read.

    Re: Receiving "compile error: invalid outside procedure"

    Doc Grimm,

    Please take note of the changes made to your post. Firstly, any VBA code must be wrapped with [noparse]

    Your code here

    [/noparse] tags. Secondly please use Search Friendly Key Words only for titles, including Please Help.. and similar words makes it more difficult to search for appropriate threads.

    Both the pieces of code you have posted are identical. Neither have a correct procedure header (Sub <procedure name> or Private Sub <procedure name>).

    As Cytop mentions the code appears to be attempts at cracking a Worksheet password, you will need to explain in more detail as to how this situation has arisen.

    Re: Network/Collaboration Chart?

    Your image is a bit small to be legible, so it might be better if you provided a link to the chart on the site you mention. A brief glance says that it may be the kind of thing that could be done in Excel but it would require quite a lot of programming.

    In Word it would be horrendous so I will move this thread to the Excel forum where it may get a bit more exposure.

    Re: Excel crashes when opening multiple documents

    This may be very hard for us to diagnose, given the nature of the problem. If there are more class methods then you really need to upload all of them. Don't worry about the adverts they are always on the first and last posts of a page.

    One thing I did notice is that in functions: PreHeating, CW and HW you are declaring Cell as a Range but not setting it to Nothing at the end. Technically Cell is an object so this is probably a small memory leak. If you have anything similar but inside a loop then this may be the problem. If you want to be 100% safe then every declared variable should be set to Nothing before leaving a function/procedure.

    Something else to try is to set the VBE to break on all errors: Tools > Options > General > Error Trapping. That may enable you to catch an error in the class module that is causing a problem but not being highlighted until it has killed Excel.

    Re: Conditional formatting, Highlighting of row A to row F when of active cell


    Please note that you must use [noparse]


    [/noparse] tags when including VBA in your posts. Please see the Tags link in my signature for more detail. I have added the tags for you this time only.

    Re: Excel 2007 - LogIn Logout

    You will need to post your code. It is not possible to provide a solution without knowing what it is you are already doing. Be sure to use [noparse]


    [/noparse] tags when posting VBA.

    Re: RandBetween Number Generator For Statistics

    It is unclear to me why you would want to run such basic tests on the Excel random function. When it was revised several years ago by Microsoft, extensive testing was carried out using the DIEHARD series of tests as well as additional tests from NIST.

    If you want more details about the algorithm used and the testing it underwent then see:

    The kind of tests you are describing in this thread do not provide any real analyis of the quality of the RANDBETWEEN function.

    Re: Read Excel workbook to database

    i think the problem is too open ended to provide a solution. It will depend entirely on how the data is configured in each Workbook/Worksheet.

    Unless you can describe how there are similarities between the data in the different Workbooks then it is unlikely there will be a generic solution.

    Re: Select different ranges using IF formula

    You need to explain what function the formula in S6 is performing on the range. Also please confirm that in:


    I then want the same cell (S6) to use cells 'K2 through K7' 'IF' cell S6 is then 26% to 50% etc ....

    S6 should be M3 since it is not possible to perform a calculation for a cell based on the value of the same cell.

    Re: Countif formula for multiple cells and sheets

    I have already asked you in this thread to use code tags correctly. You have not taken note and have once again posted code with incorrect tags.

    You must edit your post to correct this otherwise you post will be locked as will the other post you have just started.