15 Messages
Digest #4208
Messages
Sat Nov 4, 2017 2:04 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
When you array-enter a formula, it allows the function to be able to place
values in any of the cells in the range it was entered over. That's normal
with EXCEL.
For example, array-enter this hard-coded array over a 4-row by 1 column
range:
={1;2;3;4}
If you do it over a 4-row by 2-column range, each column will have the same
data.
Or, over a 2x2 range:
={1,2;3,4}
On Sat, Nov 4, 2017 at 1:57 PM, johnhoel1248@
...
wrote:
>
> I have been watching this discussion with interest because I write VBA as
> well. As far as I know, a function cannot change any cell other than the
> one from which it is invoked, and that only by the value it returns. Is
> this code part of a function, or part of a sub invoked as a macro? If a
> function, how can it change a range?
>
values in any of the cells in the range it was entered over. That's normal
with EXCEL.
For example, array-enter this hard-coded array over a 4-row by 1 column
range:
={1;2;3;4}
If you do it over a 4-row by 2-column range, each column will have the same
data.
Or, over a 2x2 range:
={1,2;3,4}
On Sat, Nov 4, 2017 at 1:57 PM, johnhoel1248@
...
wrote:
>
> I have been watching this discussion with interest because I write VBA as
> well. As far as I know, a function cannot change any cell other than the
> one from which it is invoked, and that only by the value it returns. Is
> this code part of a function, or part of a sub invoked as a macro? If a
> function, how can it change a range?
>
Sat Nov 4, 2017 2:31 pm (PDT) . Posted by:
john_hoel
So the statement starting with
Range("A2:I70") = smfGetYahooPortfolioView(...)
was illustrative and not literally coded that way.
I finally read the code for smfGetYahooPortfolioView, which I should have done previously, and now array entry is starting to make sense.
Range("
was illustrative and not literally coded that way.
I finally read the code for smfGetYahooPortfoli
Sat Nov 4, 2017 2:08 pm (PDT) . Posted by:
"Yahoo!" lewglenn
To answer your question, when it stopped it didn't give any vba message; it just highlighted the segment I reproduced in my last email.
When I ran your test macro it gave the attached results (which are obviously not correct). I checked again that the RCH_Stock_Market_Functions.xla file in SMF Add-in has the date Thursday, November 02, 2017, 10:07:18 AM.
So I'm really confused as to what's happening.
On Saturday, November 4, 2017, 1:33:11 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
[Attachment(s) from Randy Harmelink included below]
If it's stopped on the function definition statement, pTickers would be empty. It's saying it can't start the routine because it has a problem. What VBA message are you getting at the time?
Let's try this. I'll attach a workbook with just my test macro in it. Try it and see if your version of the add-in works with it.
On Sat, Nov 4, 2017 at 1:01 PM, Yahoo! lewglenn@... wrote:
When I duplicate what you did it still didn't work. When it executes the command in my subroutine:
Range("A2:H70") = smfGetYahooPortfolioView( SelStr, "0151181920158756", , 1)
it steps into your function modGetYahooPortfolio and it stops with the code segment:
Public Function smfGetYahooPortfolioView(ByVal pTickers As Variant, _
Optional ByVal pItems As Variant = " 010203040506070809101112131415 161718192021222324252627282930 31323334", _
Optional ByVal pView As String = "Obsolete", _
Optional ByVal pHeader As Integer = 0, _
Optional ByVal pDim1 As Integer = 0, _
Optional ByVal pDim2 As Integer = 0)yellowed out.
When I look at pTickers, it says Empty. The same for pItems. I checked that the first argument to the smfGetYahooPortfolioView function, SelStr, is correctly populated but somehow it's not getting in to
modGetYahooPortfolio.
Do you have any suggestion as to what I'm still doing wrong?
On Saturday, November 4, 2017, 12:00:12 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
This works for me:
SelStr = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO, T,TQQQ,MORL,DFT,BAX," _ & "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC, GILD,SRC,MRCC,DES,TQQQ," _ & "AHH,HDV,SPHD,XBI,EEM,UVE,KRE, VOE,GDX,SILJ,DSENX,EFA," _ & "DGRW,REML"
Range("A2:I70") = smfGetYahooPortfolioView( SelStr, "013518192015163535", , 1)
...although my results are different now because I've added the additional fields. So, adding the additional fields, I have this code:
SelStr = "TLT,TLT,EDV,CVX,SON,VNQ,SBIO, T,TQQQ,MORL,DFT,BAX," _ & "BIP,EIGR,XLV,QQQ,XHB,ITB,DBC, GILD,SRC,MRCC,DES,TQQQ," _ & "AHH,HDV,SPHD,XBI,EEM,UVE,KRE, VOE,GDX,SILJ,DSENX,EFA," _ & "DGRW,REML"
Range("A2:H70") = smfGetYahooPortfolioView( SelStr, "0151181920158756", , 1)
...which generates something like:
| Symbol | Prev Close | Open | High | Low | Last Price | Last Traded Date/Time | 50-DMA |
| TLT | $125.30 | $125.47 | $125.70 | $125.14 | $125.64 | 11/3/2017 8:00:00 PM | $124.87 |
| -- | -- | -- | -- | -- | -- | -- | -- |
| EDV | $118.52 | $118.68 | $119.27 | $118.47 | $119.25 | 11/3/2017 8:00:01 PM | $117.60 |
| CVX | $115.33 | $115.48 | $115.75 | $114.73 | $114.99 | 11/3/2017 8:04:39 PM | $117.43 |
| SON | $51.65 | $51.68 | $52.12 | $51.35 | $51.91 | 11/3/2017 8:00:58 PM | $50.90 |
| VNQ | $83.30 | $82.89 | $83.41 | $82.53 | $83.09 | 11/3/2017 8:00:00 PM | $83.43 |
| SBIO | $30.42 | $30.58 | $31.08 | $30.58 | $31.05 | 11/3/2017 7:59:30 PM | $31.02 |
| T | $33.17 | $33.34 | $33.39 | $32.95 | $33.30 | 11/3/2017 8:04:34 PM | $36.76 |
| TQQQ | $129.12 | $130.49 | $132.87 | $129.17 | $132.72 | 11/3/2017 8:00:00 PM | $119.06 |
The reason the second row is empty is you passed TLT twice. As is, the function only fills in the first occurrence of a ticker symbol.
#yiv6816268095 #yiv6816268095 -- #yiv6816268095ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv6816268095 #yiv6816268095ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv6816268095 #yiv6816268095ygrp-mkp #yiv6816268095hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv6816268095 #yiv6816268095ygrp-mkp #yiv6816268095ads {margin-bottom:10px;}#yiv6816268095 #yiv6816268095ygrp-mkp .yiv6816268095ad {padding:0 0;}#yiv6816268095 #yiv6816268095ygrp-mkp .yiv6816268095ad p {margin:0;}#yiv6816268095 #yiv6816268095ygrp-mkp .yiv6816268095ad a {color:#0000ff;text-decoration:none;}#yiv6816268095 #yiv6816268095ygrp-sponsor #yiv6816268095ygrp-lc {font-family:Arial;}#yiv6816268095 #yiv6816268095ygrp-sponsor #yiv6816268095ygrp-lc #yiv6816268095hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6816268095 #yiv6816268095ygrp-sponsor #yiv6816268095ygrp-lc .yiv6816268095ad {margin-bottom:10px;padding:0 0;}#yiv6816268095 #yiv6816268095actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6816268095 #yiv6816268095activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6816268095 #yiv6816268095activity span {font-weight:700;}#yiv6816268095 #yiv6816268095activity span:first-child {text-transform:uppercase;}#yiv6816268095 #yiv6816268095activity span a {color:#5085b6;text-decoration:none;}#yiv6816268095 #yiv6816268095activity span span {color:#ff7900;}#yiv6816268095 #yiv6816268095activity span .yiv6816268095underline {text-decoration:underline;}#yiv6816268095 .yiv6816268095attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv6816268095 .yiv6816268095attach div a {text-decoration:none;}#yiv6816268095 .yiv6816268095attach img {border:none;padding-right:5px;}#yiv6816268095 .yiv6816268095attach label {display:block;margin-bottom:5px;}#yiv6816268095 .yiv6816268095attach label a {text-decoration:none;}#yiv6816268095 blockquote {margin:0 0 0 4px;}#yiv6816268095 .yiv6816268095bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv6816268095 .yiv6816268095bold a {text-decoration:none;}#yiv6816268095 dd.yiv6816268095last p a {font-family:Verdana;font-weight:700;}#yiv6816268095 dd.yiv6816268095last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6816268095 dd.yiv6816268095last p span.yiv6816268095yshortcuts {margin-right:0;}#yiv6816268095 div.yiv6816268095attach-table div div a {text-decoration:none;}#yiv6816268095 div.yiv6816268095attach-table {width:400px;}#yiv6816268095 div.yiv6816268095file-title a, #yiv6816268095 div.yiv6816268095file-title a:active, #yiv6816268095 div.yiv6816268095file-title a:hover, #yiv6816268095 div.yiv6816268095file-title a:visited {text-decoration:none;}#yiv6816268095 div.yiv6816268095photo-title a, #yiv6816268095 div.yiv6816268095photo-title a:active, #yiv6816268095 div.yiv6816268095photo-title a:hover, #yiv6816268095 div.yiv6816268095photo-title a:visited {text-decoration:none;}#yiv6816268095 div#yiv6816268095ygrp-mlmsg #yiv6816268095ygrp-msg p a span.yiv6816268095yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6816268095 .yiv6816268095green {color:#628c2a;}#yiv6816268095 .yiv6816268095MsoNormal {margin:0 0 0 0;}#yiv6816268095 o {font-size:0;}#yiv6816268095 #yiv6816268095photos div {float:left;width:72px;}#yiv6816268095 #yiv6816268095photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv6816268095 #yiv6816268095photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv6816268095 #yiv6816268095reco-category {font-size:77%;}#yiv6816268095 #yiv6816268095reco-desc {font-size:77%;}#yiv6816268095 .yiv6816268095replbq {margin:4px;}#yiv6816268095 #yiv6816268095ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv6816268095 #yiv6816268095ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv6816268095 #yiv6816268095ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv6816268095 #yiv6816268095ygrp-mlmsg select, #yiv6816268095 input, #yiv6816268095 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv6816268095 #yiv6816268095ygrp-mlmsg pre, #yiv6816268095 code {font:115% monospace;}#yiv6816268095 #yiv6816268095ygrp-mlmsg * {line-height:1.22em;}#yiv6816268095 #yiv6816268095ygrp-mlmsg #yiv6816268095logo {padding-bottom:10px;}#yiv6816268095 #yiv6816268095ygrp-msg p a {font-family:Verdana;}#yiv6816268095 #yiv6816268095ygrp-msg p#yiv6816268095attach-count span {color:#1E66AE;font-weight:700;}#yiv6816268095 #yiv6816268095ygrp-reco #yiv6816268095reco-head {color:#ff7900;font-weight:700;}#yiv6816268095 #yiv6816268095ygrp-reco {margin-bottom:20px;padding:0px;}#yiv6816268095 #yiv6816268095ygrp-sponsor #yiv6816268095ov li a {font-size:130%;text-decoration:none;}#yiv6816268095 #yiv6816268095ygrp-sponsor #yiv6816268095ov li {font-size:77%;list-style-type:square;
When I ran your test macro it gave the attached results (which are obviously not correct). I checked again that the RCH_Stock_Market_Functions.xla file in SMF Add-in has the date Thursday, November 02, 2017, 10:07:18 AM.
So I'm really confused as to what's happening.
On Saturday, November 4, 2017, 1:33:11 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
[Attachment(s) from Randy Harmelink included below]
If it's stopped on the function definition statement, pTickers would be empty. It's saying it can't start the routine because it has a problem. What VBA message are you getting at the time?
Let's try this. I'll attach a workbook with just my test macro in it. Try it and see if your version of the add-in works with it.
On Sat, Nov 4, 2017 at 1:01 PM, Yahoo! lewglenn@... wrote:
When I duplicate what you did it still didn't work. When it executes the command in my subroutine:
Range("A2:H70") = smfGetYahooPortfolioView( SelStr, "0151181920158756", , 1)
it steps into your function modGetYahooPortfolio and it stops with the code segment:
Public Function smfGetYahooPortfolioView(ByVal pTickers As Variant, _
Optional ByVal pItems As Variant = " 010203040506070809101112131415 161718192021222324252627282930 31323334", _
Optional ByVal pView As String = "Obsolete", _
Optional ByVal pHeader As Integer = 0, _
Optional ByVal pDim1 As Integer = 0, _
Optional ByVal pDim2 As Integer = 0)yellowed out.
When I look at pTickers, it says Empty. The same for pItems. I checked that the first argument to the smfGetYahooPortfolioView function, SelStr, is correctly populated but somehow it's not getting in to
modGetYahooPortfolio.
Do you have any suggestion as to what I'm still doing wrong?
On Saturday, November 4, 2017, 12:00:12 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
This works for me:
SelStr = "TLT,TLT,
Range("
...although my results are different now because I've added the additional fields. So, adding the additional fields, I have this code:
SelStr = "TLT,TLT,
Range("
...which generates something like:
| Symbol | Prev Close | Open | High | Low | Last Price | Last Traded Date/Time | 50-DMA |
| TLT | $125.30 | $125.47 | $125.70 | $125.14 | $125.64 | 11/3/2017 8:00:00 PM | $124.87 |
| -- | -- | -- | -- | -- | -- | -- | -- |
| EDV | $118.52 | $118.68 | $119.27 | $118.47 | $119.25 | 11/3/2017 8:00:01 PM | $117.60 |
| CVX | $115.33 | $115.48 | $115.75 | $114.73 | $114.99 | 11/3/2017 8:04:39 PM | $117.43 |
| SON | $51.65 | $51.68 | $52.12 | $51.35 | $51.91 | 11/3/2017 8:00:58 PM | $50.90 |
| VNQ | $83.30 | $82.89 | $83.41 | $82.53 | $83.09 | 11/3/2017 8:00:00 PM | $83.43 |
| SBIO | $30.42 | $30.58 | $31.08 | $30.58 | $31.05 | 11/3/2017 7:59:30 PM | $31.02 |
| T | $33.17 | $33.34 | $33.39 | $32.95 | $33.30 | 11/3/2017 8:04:34 PM | $36.76 |
| TQQQ | $129.12 | $130.49 | $132.87 | $129.17 | $132.72 | 11/3/2017 8:00:00 PM | $119.06 |
The reason the second row is empty is you passed TLT twice. As is, the function only fills in the first occurrence of a ticker symbol.
#yiv6816268095 #yiv6816268095 -- #yiv6816268095ygrp-