Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SpreadsheetGear & VB.Net -> Workbook.Names

Posted on 2010-11-12
5
Medium Priority
?
2,086 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 34

Accepted Solution

by:
Norie earned 2000 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 34

Assisted Solution

by:Norie
Norie earned 2000 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

Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

704 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