Passing Currency format from one worksheet to another

[FSPH]

Hello there,

I have a list of shares with the market prices in their respective
currencies. For example:
10 Microsoft - 10 USD
10 Nestle - 10 CHF
10 Siemens - 10 EUR

I now want to compute the total value of these shares in their respective
currencies, resulting in
10 Microsoft = 100 USD
10 Nestle = 100 CHF
10 Siemens = 100 EUR

How do I "remember" the market prices in the proper currency? Right now I
only "load" the actual number without refering to the currency:
sngMarketPrice_CURRENCY(lngRow) =
Worksheets(strWORKSHEET_NAME).Cells(lngRow, lngCol)

How can I keep the currency information when loading my array, and then
later on write the info "number + currency" into another worksheet.

Thank you for your help.

 

[Alan]

A few questions so we can help you better:

What is the source of your data? An Excel spreadsheet, a CSV file,
or . . . "?

What is the format of this data? For example, is "10 Microsoft - 10
USD" all in one cell or field, or are they in 4 separate fields?

What is your function "sngMarketPrice_CURRENCY(lngRow)" doing? Can
you show us that code?

Are you trying to compute the values in multiple currencies (e.g., USD
and EUR, etc.)?

In general, you have to get the currency from whatever format it is
provided. From there, you can make multiple columns, one for each
currency, or you can have a column that simply designates the currency
(e.g., "USD"). In the first approach, you simply sum up the column.
In the second approach, you have to check the currency before summing
up the number. I would recommend the first approach if the number of
currencies is small. Much cleaner.

Alan

 

[FSPH]

Hello Alan,

All data are in 1 (one) column in an Excel spreadsheet. That one column
contains numbers that are formatted in different currencies. In my example
these numbers are:

"USD 10.00"
"CHF 10.00"
"EUR 10.00"

The other information like "10 * Microsoft" is in 2 different columns.

"sngMarketPrice_CURRENCY(lngRow)" is not a function, but an array. Here is
my code:
m_udtIssuer(lngMaxIssuer).sngMarketPrice_CUR =
Worksheets(strWorksheet_Name).Cells(lngRow_Issuer, lngColumn_Market_Price)

So, "m_udtIssuer(lngMaxIssuer).sngMarketPrice_CUR" (in whatever currency)
should ideally contain the currency information as well (i.e., USD, CHF, EUR,
etc.).

Storing different currency numbers in different columns would be difficult.

Instead, I am wondering if I can assign my array
(m_udtIssuer(lngMaxIssuer).sngMarketPrice_CUR) numbers with the information
about different currencies as well. The attribute sngMarketPrice_CUR (SINGLE)
should perhaps be a different data type (CURRENCY?).

Thanks for your help.