PDA

View Full Version : Generate a series of column data with a condition



alsckd333
09-23-2016, 09:36 AM
Hello everyone,

I am in need of your experts' assistance to teach me how to use the VBA program efficiently. Currently, I am trying to create a simple hydrograph with a specific condition: time of peak will be at the 1/4 of total time. My base data will be the USGS Dimensionless Unit Hydrograph, fyi (This is to use the shape of the unit hydrograph).

My question on here is, I am trying to program a code so that I can automatically generate a column of data with a specific condition. I would like to create a series of discharge data by entering in one variable (peak flow value). In doing so, the peak value will automatically position itself at the 1/4 of total time, while the rising limb and falling limb will follow the shape of USGS Unit Hydrograph (+ skewed bell curve). I am assuming this is an interpolation programming with perhaps cubic spline(?), but I am just not knowledgeable enough about programming to even know the degree of programming that i am asking for...

For example, if Qp=2000 cfs and total duration=40 minutes, I will have a time series column with increment of 1 min, and the interpolated discharge values will be generated by multiplying the peak value with the unit hydrograph's x values (Q/Qp), where it will reach 2000 cfs at 10 minutes, and will be zero or close to zero at 40 mins.


Hope this explains what my intention is here! Looking forward to hear back from anyone who can help me out !


I really appreciate the help.


Best regards,
Dan

SamT
09-24-2016, 10:17 AM
I have no idea what a hydrograph is and it doesn't matter anyway.

In order to develop a VBA script, we just need numbers and formulae.

For example:

Over TD rows in a column, where TD is a number of minutes
Assign QP to Cell TD/4, where QP is a variable in F3/Second units

From the TD cell,
Compute the cell values above according to [formula]
and the cell values below according to [formula]




For possible help developing the formulas we need, see:
http://www.nrcs.usda.gov/Internet/FSE_DOCUMENTS/stelprdb1083020.pdf
http://il.water.usgs.gov/pubs/ofr96_474.pdf
http://onlinemanuals.txdot.gov/txdotmanuals/hyd/hydrograph_method.htm
http://onlinecalc.sdsu.edu/


To translate esoteric terms into terms VBA Experts can use, see the lists of math Functions available to Excel

offthelip
09-26-2016, 02:21 AM
From what I understand about your problem it would seem that what you are really trying to do is take some data with X number of points and plot to Y number of point by using interpolation.

this code will work for interpolating a number of points in column A to a larger number of points in column b so it might be a good starting point.

The number of points in column A is in A1 and the required number of points is is B1, the interpolated points are then put in column B

Sub interpolate()
numinpts = Cells(1, 1)
numoutpts = Cells(1, 2)
inarr = Range(Cells(2, 1), Cells(numinpts + 1, 1))
outarr = Range(Cells(2, 2), Cells(numoutpts + 1, 2))
j = 1
ratio = numinpts / numoutpts
outarr(1, 1) = inarr(1, 1)
outarr(numoutpts, 1) = inarr(numinpts, 1)
For i = 2 To numoutpts - 1
curratio = i * ratio

Index = Int(curratio)
fraction = curratio - Index
outarr(i, 1) = inarr(Index, 1) + fraction * (inarr(Index + 1, 1) - inarr(Index, 1))
Next i


Range(Cells(2, 2), Cells(numoutpts + 1, 2)) = outarr


End Sub