1

I am new to VB programming. Can't figure out what I'm doing wrong here:

Dim somevariable as Integer

somevariable = WorksheetFunction.QUOTIENT(Range(Sheet1!A1), Range(Sheet1!B1))

Thanks

Byron Wall
  • 3,970
  • 2
  • 13
  • 29
user3653446
  • 13
  • 2

2 Answers2

1

Completely new answer as of given details. You are working with VBA so you can call WorksheetFunctions like

Dim someVariable
someVariable = WorksheetFunction.Quotient(Worksheets("Sheet1").Cells(1, "A").Value, Worksheets("Sheet1").Cells(1, "B").Value)
Debug.Print someVariable

There is a quite good list of supported functions available on MSDN WorksheetFunction. Please choose your current Excel version on the linked page.

Furthermore I found another very important site concerning your problem that states that not all WorksheetFunctions are supported as Application. (see support.microsoft.com)

So I searched for workarounds and found a quite simple one on StackOverflow. But on the other hand - you really could make use of the VBA builtIn operator MOD

' created some variables, as I made some attempts on creating a good example
Dim value1
value1 = Worksheets("Sheet1").Cells(1, "A").Value

Dim value2
value2 = Worksheets("Sheet1").Cells(1, "B").Value

someVariable = value1 Mod value2
Debug.Print someVariable

' of course you may use a single line statement too
someVariable = Worksheets("Tabelle1").Cells(1, "A").Value Mod Worksheets("Tabelle1").Cells(1, "B").Value

I think there are some pretty good links to have a deeper look into.

Community
  • 1
  • 1
Pilgerstorfer Franz
  • 8,303
  • 3
  • 41
  • 54
  • OK, between some of the suggestions I got here and a little more trying I got this to work: somevariable = WorksheetFunction.Quotient(Worksheets("Sheet1").Cells(1, "A").Value, Worksheets("Sheet1").Cells(1, "B").Value) but when I tried to do the same thing using Excel's MOD function I got a new error "Run time error 438: Object doesn't support this property or method". It's the exact same code except in one case I am using the Excel "QUOTIENT" function and in the second case I am using the MOD function. Any thoughts? – user3653446 May 20 '14 at 02:48
  • also - apologies for not formatting - I read the instructions. I put 4 spaces in front of my code but it doesn't appear as indented, mono-spaced code. and I ended several lines with 2 spaces to create a newline. that didn't seem to work either – user3653446 May 20 '14 at 02:52
  • I'll have a look into your new question. But first please verify that we are talking about `VB.net` and not `VBA`. Furthermore please add some more information eg. what **framework version** or what **office version** you are using. – Pilgerstorfer Franz May 20 '14 at 04:43
  • Not being a developer (so not having a in-depth understanding of the difference between the two) I would say that I am talking about VBA. I am just trying to do a few things in macros within Excel. I am running MSOffice 2010 and 2013 (depending if I'm poking at this from work or at home) – user3653446 May 20 '14 at 10:55
  • so you are coding in a macro - it's vba. VB.Net would be within VisualStudio and coding against .net Framework. I'll recheck my answer and add some new code for your problems – Pilgerstorfer Franz May 20 '14 at 10:56
  • completely rewrote my answer - now containing vba code! – Pilgerstorfer Franz May 20 '14 at 18:39
0

Try this 

somevariable = WorksheetFunction.QUOTIENT(Worksheets("Sheet1").Range(A1), Worksheets("Sheet1").Range(B1))
Praveen Kodali
  • 51
  • 4