Posts by Fencliff

Re: Introducing Macros In Workplace

About eight months back I was in a similar situation as you, stuck in a non-tech office job with a programming background. That's when I picked up VBA, got me through many months without getting bored.

Selling your "services", you can quite easily build a reputation. Here's my two cents on what you are about to embark on, if you'd care to read. Just keep in mind that while I'll just type these as some kind of ultimate truths, they're just my opinions.

One thing I've noticed is that people don't trust automated processes. You need to make them transparent, explain the user what is happening in the background, and so forth. A very important factor is to make the functionality of your macros user-modifiable, and there are two very good reasons for that. One, your users feel more comfortable using the macros when they know that if there is a change in the input data, or some files change names or locations, it won't break their routine. Another, when you do a number of these projects for people, the support cost starts to reach skies quite fast. (Depending largely on how stupid your users are, but IMHO they quite often exceed your wildest ideas of possible idiocy.)

Documentation is something you will need to do, because when it is finally time to modify something, or even use the macro after two-week leave, quite often the users tend to forget how things work.

Consider seriously separating data and code where possible. If users process certain types of files often, create an add-in to handle these. If they only refresh a single report, create a separate add-in. There are two reasons, at least. This way they aren't bugged by macro security warnings every time they open their file, and if you ever need to make changes in the code, you don't have to reconciliate the new code with data.

Assuming all your users are on a shared company network: write a good, solid error handling module that logs errors in a central location. If your mail server is configured for it, even shoot a silent mail to yourself using CDONTS (Google around for this if it sounds like something you'd like to do). This way you can debug any problems easily, or even see that users are having trouble with this or that, and pre-emptively help them fix the issue, whether it is in your code or sitting in front of the monitor.

People like a little flash. Instead of having them Alt+F8 to run your macros, create a custom toolbar or a menu item. Much more professional. For some reason people I have worked with have also preferred to keep ScreenUpdating turned on, so they can see what is happening, and they think it's cool to see the computer doing their work for them.

When sitting down with a potential "client" and discussing the requirement, there is a fine line between a healthy, iterative process, and a situation where you are working to produce code while they keep throwing new requirements at you. I'm not saying you shouldn't be flexible, but agreeing on some kind of functional specification, whether it's a neatly typed document or jotted minutes from your discussion, is generally a good idea. Alternatively you can go completely Agile and throw betas, revisions, release candidates and what not at them, but while that might get the user what he or she wants more reliably, it is also more work for you.

Re: Checkboxes To Choose Between Worksheet_change Events

Quote from Horpe

I think this is sort of combining multiple Worksheet_Change events, which is over my head :sniff:

You can only have one event handler for one event. Just put the code into the same handler after the rest of your code.

Re: Embed DLL Into Vba Project

In short, there is no good way to do it. It would be possible to embed the DLL as an OLEObject, then move the file into the system32 folder, register the DLL with regsvr32 and boot Excel. I would not advise you to do this, however. For one, it will fail on restricted systems where users are not running as Administrators or Debugger Users, and second, I'd be pretty mightily pissed off if some Excel macro would be touching my system folders

Here is probably the most user-friendly way of handling the situation:

1. Separate all code that uses the scrrun libraries into their own module. The interpreter doesn't try to reach the library before a module that utilizes it is loaded into memory.

2. Before calling any code in that module, try to connect to the library using late binding with CreateObject. If it fails, it will throw a run-time error instead of a compilation error, and that you can capture and tell the user to download it from here:
http://www.microsoft.com/downl…A22B832CAA&displaylang=en

Alternatively, if performance is not crucial, you could use the Collection object instead of Dictionary.

Re: Object Orientated Programming Vba - Multiple Combo Boxes

This sounds like an activity for a course of some sort, so I'm not going to reply with code but let you do your own work. Here's a couple of pointers though:

You could think about setting up a parent and child classes, let's call them CBoxes and CBox respectively. The CBoxes class could define a member collection mcolChildren, and for example methods CreateBox, PopulateBox etc... In turn the CBox class could have whatever properties a ComboBox has.

Now you could use the parent collection as a factory, and create the ComboBoxes from the CBoxes.CreateBox method, that would also add them to the mcolChildren collection.

The CBox class could have an event that fires when the value of the ComboBox associated with that object changes, and you could trap that in the parent class in event handler and run CBoxes.Synchronize to remove that value from all the other CBox children and return any value that was omitted before.

For each group of ComboBoxes you need to keep synchronized, you could then simply create a new CBoxes factory. Or if you want to go for scalability, you could instead define the CBoxes class as a parent collection with an interface to a class CBoxFactory that produces CBox objects.

...

Now if this isn't an assignment, but a real-life situation, I personally wouldn't bother with OOP. Instead you could just set the ComboBox event handlers to examine the pool of populated values and add and remove them from the others as necessary.

Re: Function To Calculate IRR Of An Array

Just post a representative example of your workbook with some data, and maybe somebody can actually fix this bug.

Re: Count A String Occurance Across Multiple Worksheets

Use a PivotTable with multiple consolidation ranges? Seems like the smartest solution to me.

Re: Function To Calculate IRR Of An Array

You don't have to guess where the error is. When the error actually happens, click Debug and see which line is highlighted in yellow. Quoting my previous post:

Quote

Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can't find a result that works after 20 tries, the #NUM! error value is returned.
In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).

If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.

This would result in error 1004.

Re: Run-time Error '1004': Application Defined Or Object Defined Error

What line does the error occur on?

Here's some documentation on the IRR function:

Syntax

IRR(values,guess)

Values is an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.

Values must contain at least one positive value and one negative value to calculate the internal rate of return.

IRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the sequence you want.
If an array or reference argument contains text, logical values, or empty cells, those values are ignored.

Guess is a number that you guess is close to the result of IRR.

Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can't find a result that works after 20 tries, the #NUM! error value is returned.
In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).

If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.

Re: Trim Words To Meet Character Length

User-defined function:

Public Function TrimWords(sValue As String, lMaxLength As Long) As String


    Dim sRet As String
    
    If Len(sValue) > lMaxLength Then
        sRet = Left(sValue, InStrRev(sValue, Chr(32), lMaxLength))
    Else
        sRet = sValue
    End If


    TrimWords = sRet
    
End Function

Place the code in a standard module in your workbook. Example of usage:

=TRIMWORDS($A1, 18)

Re: Calling A Form Procedure

Yes, just reference the userform when calling the procedure, otherwise proceed as normally

FormFoo.SubBar

Re: Filtering A Listbox

Assuming your listbox is single-column. Read up on the ListBox and the AddItem method in the VBA help to modify.

'   Delete listbox contents
    ListBox.Clear


'   Loop through all visible cells in the source range (the rows filtered out are hidden)
    For Each rngCell In rngSource.SpecialCells(xlCellTypeVisible)
   
        'Add cell value to ListBox
        ListBox.AddItem rngCell.Value
    
    Next rngCell

Re: Add-in File Not Recognized

Hmm..

- Do your subs take arguments? This can be tricky sometimes.
- Do you have "duplicate" procedures - i.e. procs in different modules by the same name?
- Are your procs in modules or the sheet or ThisWorkbook classes? Not sure exactly, but seem to remember that the procs need to be in standard modules.

Other than that, your best bet is to upload the file. I'm sure this is something simple that a pair of fresh eyes can solve in seconds flat.

Re: Add-in File Not Recognized

Hard to say what the issue is by the description alone, would have to see it to debug. Any chance you have Option Private Module set?

Re: Filtering A Listbox

You will have to repopulate the listbox manually. You could do this by hooking the userform_initialize event to check whether the visible rows in the source range match your listbox contents and repopulate if not.

Re: Starting A Vba Project

When you create a new workbook in Excel, it automatically contains a VBA project. Each workbook can only have one.

You can rename the project in the Project Explorer window by right-clicking the project (top-level node saying "VBAProject (Workbook name)") you want to rename and click VBA Project Properties... and assign a new name in the Project Name field.

Simple as that.

Re: Display Cell Contents In A Message Box

Edit: Parsnip beat me to the punch. His method is much simpler, and one I would use for 1-dimensional array as well. The code I've posted below also works for 2-dimensional ranges.

--

This is a simple function I've used for some project before, that takes a range and returns it's values as a string, separated by a provided character.

Private Function RangeToString(ByRef rngDisplay As Range, ByVal strSeparator As String) As String


    'The string to separate elements on the message box,
    'if the range size is more than one cell
    
    Dim strMessage      As String
    Dim astrMessage()   As String
    
    Dim avarRange()     As Variant
    Dim varElement      As Variant
    
    Dim i               As Long
    
    'If the range is only one cell, we will return that that
    If rngDisplay.Cells.Count = 1 Then


        strMessage = rngDisplay.Value
    
    'Else the range is multiple cells, so we need to concatenate their values
    Else
    
        'Assign range to a variant array
        avarRange = rngDisplay
        
        'Loop through each element to build a one-dimensional array of the range
        For Each varElement In avarRange
        
            ReDim Preserve astrMessage(i)
            astrMessage(i) = CStr(varElement)
            i = 1 + i
            
        Next varElement
        
        'Build the string to return
        strMessage = Join(astrMessage, strSeparator)
        
    End If
    
    RangeToString = strMessage
    
End Function

You could use it to show a range in a message box like this:

'   Display the selected cells, each value on separate row
    MsgBox RangeToString(Selection, vbNewLine)


'   Display a specified range, each value delimited by a space
    MsgBox RangeToString(Sheet1.Range("A1:C10"), " ")

The array looping method is not particularly effective, but then again you're not going to display THAT many values in a messagebox.

Re: If Condition To Return One Of Three Results

=IF(ISBLANK(AI9),"",IF(AH9>AI9,"Green",IF(AH9=AI9,"Blue",IF(AH9<AI9,"Yellow","")))

The point is to nest multiple IF statements inside the False option of the previous conditional.

Re: Pass Array Byval

Quote from mosu002

Fencliff: Agreed that passing by reference is faster but envisaged a possibility of passing in an input array to consecutive functions that needed to stay unchange (e.g. data inputted/uploaded from user). Re variant: I understand that you only need to use a single variant, but I though even one variant is more memory intensive than an array of simple data types, because enough memory must be set aside for any data type a variant can hold - up to an instance of Excel. Am the first to admit my knowledge on this is fuzzy, so please tell me if I'm wrong. I had thought the method of the explicit copy would be the least memory intensive - but maybe computationally a lot slower.

You're right, you cannot create function that expects to receive an array by value. But, like shg specified, an array passed as variant consumes 12 bytes of extra memory. To put this in perspective, the string "Hey" consumes same 12 bytes. So does an array of three Long integers. Or one and a half Double.

Now this isn't too inefficient, and I would sacrifice 12 bytes any given day to have the peace of mind that a function doesn't change my variables.

Let's take a quick look to what actually happens when you pass arguments ByRef or ByVal. If you already know how this works, feel free to skip to the end of this post.

Say you have written some code and assigned the string literal "Elephant" to a string variable strAnimal. At runtime, your code gets interpreted and the 22 bytes containing the characters "Elephant" are placed into the heap, which is the free allocation part of your memory where your operating system finds a suitable slot to store those 22 bytes for you. In addition, a 4-byte pointer that tells the interpreter where the actual string is located in the heap is placed in the stack, a last-in-first-out queue packed with all the memory access instructions of your function.

Now you pass the variable to a function:

If blnFeedPeanutsTo(strAnimal) = True Then
   'Contrived example
    MsgBox strAnimal & " likes peanuts."
End If

If the function is expecting the string by reference,

Private Function blnFeedPeanutsTo(ByRef strCreatureToFeed) As Boolean

a 4-byte pointer to the memory address containing the string is placed on this function's stack, and therefore the string itself doesn't need to be duplicated in memory. The side effect of this is that changes made to whatever variable passed to the parameter strCreatureToFeed are carried to the string stored in the heap.

However if the function is expecting it's argument by value,

Private Function blnFeedPeanutsTo(ByVal strCreatureToFeed) As Boolean

a copy of the bytes forming the string strAnimal is made and placed in another suitable location in the heap, and the a pointer that refers to the new memory location is added to the stack.

The implications of passing the string "Elephant" by value or by reference are positively negligible, and as a rule of thumb I tend to pass all numeric base types and short strings by value, unless I explicitly want my function to return the modifications ByRef or recurse on the arguments. However, let's take an array of 20 000 strings of average 20 characters per string. In VBA, string's memory print in bytes can be calculated by multiplying the amount of characters by two and adding overhead of 6 bytes, 4 for length information and 2 for terminator, so the size of the array is approximately 900 kilobytes. When you pass this array ByRef, you are simply adding 4 bytes onto the stack. When you pass it ByVal, you are duplicating the 920 000 bytes that exist in the heap. If you are planning on passing an array like this upwards on your call stack by value via multiple consecutive functions, you are eating a lot of memory and the garbage collection will inevitably slow your code down as well coming down the stack.

Hope this helps,
Fencliff

Re: Pass Array Byval

Why do you need to pass your array ByVal? If you are trying to save memory, ByRef will do exactly that. Unless you're passing it to a separate thread (e.g. COM server) and want to avoid marshalling cost, it's much cheaper to pass the array ByRef, especially if you are dealing with a large array.

However, when you pass your typed array (say, array of bytes) to a function that is expecting a ByVal Variant as an argument, VBA doesn't interpret it as an array of Variants, but simply a Variant type variable that contains a byte array. The only additional cost involved is the time it will take to resolve the Variant.

If what you need it to be able to modify the array without passing the changes up the stack, I would simply pass it Byval as Variant. If you want to religiously avoid using a variant, you can make an explicit copy of the array, something like:

Option Explicit


Sub fCaller()


    Dim asArray()   As String
    Dim i           As Long
    
'   Resize the array
    ReDim asArray(0 To 10000)
    
'   Populate the array with some values
    For i = LBound(asArray) To UBound(asArray)
        asArray(i) = CStr(i & " monkeys!")
    Next i


'   Pass the array to a procedure
    fVariantReceiver asArray


'   Output the original array to demonstrate no changes
    For i = LBound(asArray) To UBound(asArray)
        Debug.Print asArray(i)
    Next i
    
End Sub




Sub fReceiver(ByRef asArgument() As String)


    Dim i As Long
    
'   Declare another string array
    Dim asCopy() As String
    
'   Assign argument array to the copy
    asCopy = asArgument


'   Change the values in the copyarray and output
    For i = LBound(asCopy) To UBound(asCopy)
        asCopy(i) = Replace(asCopy(i), "monkeys", "penguins")
        Debug.Print asCopy(i)
    Next i
    
End Sub

Regards,
Fencliff

Re: Run Macro

Hi,

The error is in your use of ActiveCell. The Worksheet_Change function fires when the cell has been changed - and the active cell is the cell that has been selected after editing.

You can remove the check for intersection of ActiveCell and Range("pnr"), the event handler checks that the cell is correct already.