+ Reply to Thread
Results 1 to 3 of 3

VBA variable length array help needed

  1. #1
    Registered User
    Join Date
    10-05-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    8

    VBA variable length array help needed

    Hi

    I am a vba self taught hack, and nota very good one at that, and i need some help with variable length arrays. Below is the code I have which works perfectly but does not use arrays.

    I have 4 columns. In Column A is a list of names. There are always atleast 2 or more of the same name and the same names are grouped together one under each other.
    Then in Column B is a list of numbers representing qty of an item sold by that person. Then in column C is the time in number of days that it took that person to sell that qty.
    Nowhere in the VBA code do i refer to column C, but Columd D is Column C multiplied by 5.
    This function allows me enter a persons name and a random number of days that does not neccessarily correspond to any exact number of days related to that persons sales up to that period allowing for interpolation and extrapoltion, spewing out an interpolated or extrapolated number
    Below is the code I have which works perfectly.

    Please Login or Register  to view this content.
    This function above calls on the following Interpolation extrapolation function.

    Please Login or Register  to view this content.
    The above two functions work perfectly, however CalcRange (Column D) is a function of column C multiplied by 5 and what I want to dois create an array of numbers called CalcRange in VBA and use that array when my function calls on the Interpolation function ..QtySold = PLI(TimeRange, CalcRange, Days, nn) and not have to calculate it seperately in excel like i have done in Columd D.

    Below attempt is a disgrace i know...but if anyone can help me it would be much appreciated

    Please Login or Register  to view this content.
    I hope i have explaind my request. If it is not understood it is no doubt my bad explanation.
    Thanks to all those people in adavnce who look to help me.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: VBA variable length array help needed

    You have to dimension your array. You should also get in the habit of declaring your variables as that can cause problems. I'm not sure if the result is correct, but the formula does at least produce one!
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-05-2010
    Location
    London England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: VBA variable length array help needed

    Hi Stephen, you are a super star.
    I know looking at it that it will work....even if the answer is wrong...but i am pretty sure it will be correct. I made up this question of qty and sales and names just to learn how to do this for a more involved problem i had...i did not want to post my more my involved problem as my explanation of the problem would probably have confused me as well as this community as i am not good at explaining.
    I can't thank you enough..am very greatful.

    Cheers
    Brian P

    PS I learnt VBA in bits and pieces..through job neccesity in the 90's and always hacked together a solution, never quite learning about declaring variables....only now that my VBA function and Proc abilities have improved through repitition, do i begin to understand and appreciate declaring variables.......but my bad old habits seem to stick with me....and i seldom declare variables

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1