Function To Write An Array

  • Hello,
    I have the following assignment to do, problem is I'm new to arrays and functions in VBA and nothing is working for me, i get errors no matter what i try, (for example i don't know how to feed the array to the function, and it won't let me write a range to an array)

    [COLOR="RoyalBlue"]5. A) Write a function that takes a 2D array of doubles, a single cell range, an optional cap and an optional floor.
    B)Write the array on to the sheet with the single cell range parameter being the location of the top left of the array on the sheet. When writing the array to the sheet, for each value in the array where it is below the floor put the floor on the sheet, where the value is above the cap put the cap value on the sheet, otherwise display the original value. The input array should not be modified.
    All code should be what you consider production quality.

    here's my ideas for code, it's so bad i'm embarrassed, please help.

  • Re: Function To Write An Array


    This Board (as well as many others) generally don't like to hand hold when what is being asked looks like homework. With this in mind, I won't do the work for you. But I can give pointers.

    What is the source of the 2D array of doubles? Is it an existing excel range or is it coming from somewhere else? From my understanding, you just need to take the array and iterate thru it comparing the values at each step and replacing the value with the cap or the floor if it is outside of the limits. Is this correct?


  • Re: Function To Write An Array

    Functions return values, they don't write to cells.
    To cause the required actions, a Sub (with the listed arguments) should be used, not a Function.

    (Pointing out that the assignment is flawed, will that get you extra points or will it annoy the instructor?)

  • Re: Function To Write An Array

    Hi Mike


    Functions return values, they don't write to cells.

    I think you are talking about UDFs (context worksheet) and not vba functions (context vba). If you call a UDF from a formula in a worksheet you have restrictions that prevent you from changing the environment, like writing to cells, hiding columns, etc. Here, however, that's not the case. The function is called from a Sub, we are in vba context, and so the vba function has no restrictions, it's just vba code.

    This doesn't mean that I don't agree that it makes more sense to use a Sub in this case, since you are not returning a result.

    Kind regards

  • Re: Function To Write An Array

    I was thinking more in the gereral sense. One can write routines that blur the distinction between Functions and Subs in many languages, including VBA.
    Functions return values. The desribed routine does not return a value (what data type would it be?)
    That VBA allows functions to alter the environment does not change the basic distinction between Functions and Subs. For example, consider this function

    Function newSheetName() as String
         newSheetName = ActiveSheet.Name
    End Function

    It meets the description of a function (it returns a string value) even though it can't be called from the spreadsheet.

    The OP's assignment does not return a value, it is a Sub.

    (Yes it could be cast to return a boolean, similar to the Dialogs.Show. But what meaning would one attach to a return value of False vs. True. Similarly, what meaning would one attach to other possible return values for the mis-cast "Function" the OP is required to produce.)

    I interpret the situation as a textbook that has been copy edited, but not content tested by students and teachers, probably the first or second edition of the textbook.

  • Re: Function To Write An Array

    I agree with you, in this case it makes more sense to use a Sub. I was merely pointing out the fact that vba functions running in vba context can alter the environment. Like you said, however, in this case he's not returning any value and so a Sub makes more sense.

    kind regards

  • Re: Function To Write An Array

    The OP must have abandoned this thread. If not, this might help a bit. A keyword in your project was Optional. Notice how I did that. I would use a Variant array rather than a double even though your example used integers.

    The 2nd sub is where the work begins. It should be a simply matter to iterate through the array in it. Search for Array on this forum and you should find many examples. Note the use of Resize in the first Sub rather than writing each cell value.

  • Re: Function To Write An Array

    hi there, thanks for the help everyone, don't worry, I haven't abandoned the thread. i've been away and will attempt the questions again tomorrow.
    And not to worry, it isn't homework as such - i'm not at school or on a course, i'm just trying to improve my vba programming for work.

    kind regards, F

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!