Modifying a RegEx function to cover more situations
Please take a look at the attached workbook ( "GetSeatsFunction_v2.xlsm"
The other workbook ("GetSeats Function Error.xlsm") has a less complicated single function that could be adapted as well.
Thanks,
John
GetSeatsFunction-v2.xlsm
GetSeats-Function-Error.xlsm
The other workbook ("GetSeats Function Error.xlsm") has a less complicated single function that could be adapted as well.
Thanks,
John
GetSeatsFunction-v2.xlsm
GetSeats-Function-Error.xlsm
kaufmed
Can you post the code of the functions? I do not have Excel installed to view the documents.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Andrew, thanks for your post.I tried added 'are' and 'is' to both workbooks and it doesn't help. However There is a table of allowed combinations and if we can modify the code to limit our pattern to those combinations only, then I'd think i'd be in pretty good shape.
Please take a look at the attached revised workbook.
I spent the entire day today trying to figure it out myself, to no avail so any help would be greatly appreciated! :- )
Thanks,
John
GetSeatsFunction-v3.xlsm
Please take a look at the attached revised workbook.
I spent the entire day today trying to figure it out myself, to no avail so any help would be greatly appreciated! :- )
Thanks,
John
GetSeatsFunction-v3.xlsm
ASKER
Hi Stuart, I finally figured it out. I just needed to add the negative lookahead list to "Const cstrPattSeatRange" as well!
I think the reason this took me so long is that while I understand the syntax of patterns pretty well, but i don't really understand how all the ancillary stuff works, and I spent most of the time trying to figure that out.
What I'd like to do is ask a series of questions (which I'm hoping you might be available to answer) on manageable chunks of your function until I actually understand it all to the point where I could write one like it myself.
I know there are a lot of online resources about this stuff, but whether it's laziness or ADD, I have a hard time learning anything except via the apprentice system. :- )
For example:
I hope this isn't too confusing. In any event, thank you again for your solution the previous question, and for leading me to (what I hope is my correct) assumptive solution to this one.
- John
I think the reason this took me so long is that while I understand the syntax of patterns pretty well, but i don't really understand how all the ancillary stuff works, and I spent most of the time trying to figure that out.
What I'd like to do is ask a series of questions (which I'm hoping you might be available to answer) on manageable chunks of your function until I actually understand it all to the point where I could write one like it myself.
I know there are a lot of online resources about this stuff, but whether it's laziness or ADD, I have a hard time learning anything except via the apprentice system. :- )
For example:
intMaxRows = Application.WorksheetFunction.max(rowAssignments)
ReDim aRows(1 To intMaxRows)
For starters, why is "rowAssignments" identified as an Excel.Range, instead of just a Range??
How many cells are in a given instance of that range? Two? the Lo and the Hi of each class? Or something else entirely?
If it does in fact refer to the 2-cell ranges of each class, what part of the function tells me whether it's [CE7:CF7] or [CG7:CH7]? in the function.
With this code:
ReDim aRows(1 To intMaxRows)
What string might aRows(1) be? Or aRows(2)? or aRows(intMaxRows)?
What might the value of intMaxRows be? Is it a defined as an Integer rather than Long because - in the case of [CE7:CF7] for example (assuming that that's an aRow range) - it refers to the number value in the second cell of the range, as opposed to the placement of [CF7]in the 2-cell range of B-Class?
If you can help me with these questions , then I think I can get this stuff down if I post enough similar questions.I hope this isn't too confusing. In any event, thank you again for your solution the previous question, and for leading me to (what I hope is my correct) assumptive solution to this one.
- John
Thanks for the points. I will answer these questions here, but you'd better ask any more as a separate question to give other people a chance to weigh on.
OK here goes:
- Excel.Range is exactly equivalent to Range in Excel VBA. I have a habit of qualifying it with Excel because if you are coding cross product VBA, e.g. interaction between Word and Excel, Range could be ambiguous, becausse Word also has a (quite different) Range object.
- In any range object there can be any number of cells - think of it like a selection you can make in Excel; it could be one contiguous rectangle of cells, but it can be more complex and be multiple ranges, as if you selected a rectangle, thne added another using ctrl-Click
- I can't remember how the GetSeatsNew function is called, but the rowAssignments will be whatever you pass to it.
- the aRows array has one entry per row - aRows(1) contains the seats (all or a subset) that have been specified for Row 1, aRows(2) is the same for Row 2, and aRows(intMaxRows) is the same for the last row.
- intMaxRows is the number of the last used Row. int is fine for this because it has a maximum value of 32,767 - which should be more than enough for number of rows of seats on a plane
OK here goes:
- Excel.Range is exactly equivalent to Range in Excel VBA. I have a habit of qualifying it with Excel because if you are coding cross product VBA, e.g. interaction between Word and Excel, Range could be ambiguous, becausse Word also has a (quite different) Range object.
- In any range object there can be any number of cells - think of it like a selection you can make in Excel; it could be one contiguous rectangle of cells, but it can be more complex and be multiple ranges, as if you selected a rectangle, thne added another using ctrl-Click
- I can't remember how the GetSeatsNew function is called, but the rowAssignments will be whatever you pass to it.
- the aRows array has one entry per row - aRows(1) contains the seats (all or a subset) that have been specified for Row 1, aRows(2) is the same for Row 2, and aRows(intMaxRows) is the same for the last row.
- intMaxRows is the number of the last used Row. int is fine for this because it has a maximum value of 32,767 - which should be more than enough for number of rows of seats on a plane
ASKER
Thanks, Stuart. Yes I will definitely be posting more separate questions. If I understand you correctly, in the case of [AD6] whose formula is "=GetSeatsNew(T6,CC6:CL6,C
And now that I understand "intMaxRows" I can use my named range [RowCount] which returns the number of the last Row used. "ReDim aRows(1 To [RowCount] )"
Thanks for this help. Hope I encounter you in my further questions.
John
And now that I understand "intMaxRows" I can use my named range [RowCount] which returns the number of the last Row used. "ReDim aRows(1 To [RowCount] )"
Thanks for this help. Hope I encounter you in my further questions.
John
Yes that's right