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
Solved

SpreadsheetGear & VB.Net -> Workbook.Names

Posted on 2010-11-12
5
1,945 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Combine like values in a graph excel 2016 7 32
Text box keydown event does not work 38 40
Cost allcocation ... 10 22
V-Lookup 11 18
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

856 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