Solved

SpreadsheetGear & VB.Net -> Workbook.Names

Posted on 2010-11-12
5
1,966 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

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 tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

740 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