VBA code leads to #value! in cell but works in immediate box

**bpage** · 12-24-2014, 03:18 PM

I have written a getprice function for retrieving stock tickers from yahoo and bitcoin prices from an online api. However the code for bitcoin results in an #value! in my excel cell. But it performs fine when I run the function from the immediate box in VB. I sense it's some sort of compatibility problem but I've exhausted my troubleshooting knowledge. Any help or pointers would be appreciated!

here is the file
https://drive.google.com/file/d/0B-C...ew?usp=sharing

Book3.xlsm

Book2forMrexcel.xlsm

**Marc L** · 12-24-2014, 03:26 PM

As I was thinking when I read your post, does not work either from Immediate box on my side !

So needs a crystal clear and complete explanation ‼

**bpage** · 12-24-2014, 03:37 PM

Hi Marc. Ok that's an interesting twist.

I want the code to allow me to type
=GetPrice("BTCUSD",[date])
in a cell and have it return the price on that date. It seems to work fine for stock tickers like "MSFT" but the bitcoin side is broken.

When I have in the immediate box
price=GetPrice("BTCUSD",DateValue("10-17-2014"))
it works great! But the excel cell shows
#VALUE!
and that has stumped me

**Marc L** · 12-24-2014, 03:46 PM

Like this, your formula works on my side …

In worksheet, MSFT does not work either !

So it seems to be a bad conversion.

Just control dbclose variable format and what you're affecting to it ‼
Beginner level …

Problem also in CsvToArray function that never open a workbook from Web …

**bpage** · 12-24-2014, 04:04 PM

The line that causes the error is
array1 = wb.Sheets(1).Range("A1").CurrentRegion.Value

This line only gets called if the input is "BTCUSD" because of the way the CSV is handled. Maybe there is a different way to handle the CSV as alternative to making it an array?

**Marc L** · 12-24-2014, 06:15 PM

The problem is you don't know the limits of a personal function …

Better is to use an event like Worksheet_Change …

**bpage** · 12-24-2014, 06:42 PM

Yep, I eliminated the CSVtoarray function by parsing the CSV directly. Now it is functioning fine.
Thank you!

VBA code leads to #value! in cell but works in immediate box

LinkBack

Thread Tools

Rate This Thread

Display

VBA code leads to #value! in cell but works in immediate box

Re: VBA code leads to #value! in cell but works in immediate box

Re: VBA code leads to #value! in cell but works in immediate box

Re: VBA code leads to #value! in cell but works in immediate box

Re: VBA code leads to #value! in cell but works in immediate box

Re: VBA code leads to #value! in cell but works in immediate box

Re: VBA code leads to #value! in cell but works in immediate box

Thread Information

Users Browsing this Thread

Similar Threads

Code works when cell is manually changed only the first time and not with vba

how can i make this code works for multiple links it only works for one link

Embedding Files - Leads to Registering at Site - Leads to Introduction

Is there a way to cell value in a formula that leads to a different file path?

Organize spreadsheet of sales leads by zip code?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions