Solved

SpreadsheetGear & VB.Net -> Workbook.Names

Posted on 2010-11-12
5
1,915 Views
Last Modified: 2012-05-10
Hello,

I am new to SpreadsheeetGear which is a tool that allows excel interactions with VB.Net.
On looking at the sample V.Net source  code at http://www.spreadsheetgear.com/support/samples/srcview.aspx?file=amortizationVB.aspx and the associated Excel spreadsheet it interacts with I note in the source code it has the following:

        ' Open the workbook.
        Dim filename As String = Server.MapPath("files/amortization.xls")
        Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook(filename)
   
        ' Get IRange for cells from defined names.
        Dim pv As SpreadsheetGear.IRange = workbook.Names("PV").RefersToRange
        Dim rate As SpreadsheetGear.IRange = workbook.Names("Rate").RefersToRange
        Dim nper As SpreadsheetGear.IRange = workbook.Names("NPer").RefersToRange

Within the Excel sheet the following is displayed (where "Loan Amount (PV)" and "Annual Interest Rate (Rate)" etc are in column A and "$15,000.00" is in column B etc........

Loan Amount (PV)      $15,000.00
Annual Interest Rate (Rate)      7.25%
Total # of Months (NPer)      24

While "PV", "Rate", and "NPier" are written in column A as "Loan Amount (PV)" etc... in the excel worksheet there is no other reference to such abbreviations.

My question in relation to this is: When it states "Dim pv As SpreadsheetGear.IRange = workbook.Names("PV").RefersToRange" in the VB.Net code, is it actually looking up the excel sheet for the equivalent column/value PV? I presume" PV" is hardly just defined in the worksheet by using "Loan Amount (PV)"?

If anyone can explain how this works & what the IRange values are doing then I'd appreciate all help.

Thanking you in advance
0
Comment
Question by:speedygonzalez
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:ggupta7
ID: 34120672
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 34128553
speedygonzalez

I'm not familiar with SpreadSheetGear - I usually use Excel Interop, depending on what I'm coding of course.

Anyway, in Excel there is a Names collection which consists of all the named ranges in the workbook.

You would create them in Excel either using Insert>Names or the Name Manager.

I would assume that Names in the code you posted refers to that collection and that there are named ranges called PV, Rate etc.

So workbook.Names("PV") refers to  the range and RefersTo is a reference to the range that has the name PV.

So, the code is creating object variables for the ranges the names refer to.

I hope that makes sense - when I read it back it does sound like I might have overcomplicated a bit.:)
0
 

Author Comment

by:speedygonzalez
ID: 34134464
Thanks imnorie, that sounds as if it could be what I'm referring to. I'll leave the question open for now to see if anyone else has any further suggestions but that sounds as if it could be what "PV" etc are referring to!
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 500 total points
ID: 34135801
It's definitely named ranges being referred to in that code, but without seeing the rest of the code it's hard to tell why they are being used.

In Excel VBA you would usually just refer to a range as Range(<rangename>), which would return a range object.

So if you wanted to get the value from the named range PV you would just use something like this.

decPV =Range("PV").Value

I don't know why the code seems to be creating ranges from the named ranges - perhaps it's something to do with how SpreadSheetGear implements Names?
0
 

Author Comment

by:speedygonzalez
ID: 34153214
Thanks Imnorie!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

3 Experts available now in Live!

Get 1:1 Help Now