[smf_addin] Digest Number 3529[1 Attachment]

EXCEL Stock Market Functions Add-in Group

6 Messages

Digest #3529

1a

#REF! error in rows 65537 and larger? by sjagers

1b

Re: #REF! error in rows 65537 and larger? [1 Attachment] by "Randy Harmelink" rharmelink

1c

Re: #REF! error in rows 65537 and larger? by sjagers

1d

Re: #REF! error in rows 65537 and larger? by "Randy Harmelink" rharmelink

1e

Re: #REF! error in rows 65537 and larger? by sjagers

2a

Re: Last and Next Earnings Report Date by rjemery7

Messages

1a

#REF! error in rows 65537 and larger?

Sat Oct 3, 2015 6:16 am (PDT) . Posted by:

sjagers

Randy.... I'm getting a #REF! error in your smfPricesByDates, smfLastPrice, smfPricesBetween, and rchGetYahooHistory functions when the Excel row number is larger than 65536.

Those functions work properly when used in rows 1-65536, but when they're used in row 65537 (and larger) they turn the stock's ticker symbol cell reference (ie... B65537) into #REF! and return an error.

For Example: =smfLastPrice(symbol,date)
=smfLastPrice($B65536,$C65536).... works properly.

But when that function is used in row 65537, or larger, the function appears as =smfLastPrice(#REF!,$C65537)..... and I get a #VALUE! error.

Can you think of any reason why that happens? A screenshot is attached.

P.S. I'm using Windows Excel 2007 (with all updates) on a Windows Vista laptop.

Thanks.
Steve

Attachment(s) from

1 of 1 Photo(s)

function-problem.png

Reply to sender . Reply to group . Reply via Web Post . All Messages (5) . Top ^

1b

Re: #REF! error in rows 65537 and larger? [1 Attachment]

Sat Oct 3, 2015 9:39 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sorry. No clue here. I never do spreadsheets that large. The default
template I use for whenever I open a new workbook doesn't even go beyond
that size.

On Sat, Oct 3, 2015 at 6:16 AM, sjagers@... wrote:

>
> Randy.... I'm getting a #REF! error in your smfPricesByDates,
> smfLastPrice, smfPricesBetween, and rchGetYahooHistory functions when the
> Excel row number is larger than 65536.
>
> Those functions work properly when used in rows 1-65536, but when they're
> used in row 65537 (and larger) they turn the stock's ticker symbol cell
> reference (ie... B65537) into #REF! and return an error.
>
> For Example: =smfLastPrice(symbol,date)
> =smfLastPrice($B65536,$C65536).... works properly.
>
> But when that function is used in row 65537, or larger, the function
> appears as =smfLastPrice(#REF!,$C65537)..... and I get a #VALUE! error.
>
> Can you think of any reason why that happens? A screenshot is attached.
>
> P.S. I'm using Windows Excel 2007 (with all updates) on a Windows Vista
> laptop.
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (5) . Top ^

1c

Re: #REF! error in rows 65537 and larger?

Sat Oct 3, 2015 10:25 am (PDT) . Posted by:

sjagers

That's interesting. What version of Excel are you using? The last row in my default spreadsheet is 1,048,576 and I'm using an old version of Excel: Microsoft Office Professional 2007 (version 12.0.6729.5000)..

Could your VBA code for those functions be limited to 65,536 rows? Thanks.

Reply to sender . Reply to group . Reply via Web Post . All Messages (5) . Top ^

1d

Re: #REF! error in rows 65537 and larger?

Sat Oct 3, 2015 11:32 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I'm using EXCEL 2007, but since I only post XLS files for compatibility, my
sheet.xlt and book.xlt templates are both based on EXCEL 2003 standards.

You're right -- that could be why the VBA code is limited as well -- the
XLA is based on an EXCEL 2003 template.

I've never been one for big worksheets. I'd rather break it down into more
usable chunks. I hate workbooks people post that have 10 or more active
worksheets. In general, the only workbooks I have with more than 2 or 3
worksheets are those where I've "archived" something for future reference.

On Sat, Oct 3, 2015 at 10:25 AM, sjagers@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> That's interesting. What version of Excel are you using? The last row in
> my default spreadsheet is 1,048,576 and I'm using an old version of Excel:
> Microsoft Office Professional 2007 (version 12.0.6729.5000)..
>
> Could your VBA code for those functions be limited to 65,536 rows? Thanks.
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (5) . Top ^

1e

Re: #REF! error in rows 65537 and larger?

Sat Oct 3, 2015 12:47 pm (PDT) . Posted by:

sjagers

Thanks for the reply and thanks for sharing your Excel formulas/functions. They're great and I use them all the time. FYI, if you're interested, my workaround will be to use your function in a smaller worksheet to obtain the prices, and then copy the values to another worksheet for archiving and future reference.

I might even try copying the VBA codes in your .XLA to an .XLA that I'll create in Excel 2007, to see if that works.

Thanks again. Have a great weekend.

Reply to sender . Reply to group . Reply via Web Post . All Messages (5) . Top ^

2a

Re: Last and Next Earnings Report Date

Sat Oct 3, 2015 9:37 am (PDT) . Posted by:

rjemery7

Randy,

I appreciate your response. My use of '=RCHGetQuotes&#39; was psuedo code to express the type of access I sought.

Statements of the type:

=RCHGetElementNumber(ticker,1290)
=RCHGetElementNumber(ticker,1295)

is exactly what I needed. Thank you for the solution..

Reply to sender . Reply to group . Reply via Web Post . All Messages (5) . Top ^

For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Visit Your Group

• New Members 18

• Privacy • Unsubscribe • Terms of Use